Database structure for website comment system

Question

I am currently working on a website that needs a comment system. Since this site is brand new and the database structure has not yet been established, I would like to offer some recommendations on how to best handle the comment system, such as:

• Comments should be placed on anything. Include items in future tables.
• Comments should be quick (and easy?) Requests.

I know that this is just not so much, so here is the idea: every university has colleges, every college has buildings, and each building has rooms. Each user should be able to comment on any of these four elements (and future ones that we can add later), but I would like to avoid creating a comment table for each element.

The solution I came up with seems to work, but I'm open to other ideas as well. My solution is to use the UUID as a primary key for each position (university, college, building, room), and as a reference identifier in the comments table - UUID. Although I don’t think that I can create a system of foreign keys to bind everything, I believe that nothing will break, because only available products can have comments, so the item may either not have comments, or if it is deleted, then comments simply never will be refunded.

University: UniversityID - CHAR(36) //UUID() & primary key ... Comments: CommentID - CHAR(36) //UUID() & primary key CommentItemID - CHAR(36) //UUID of item & indexed CommentUserID - INTEGER CommentBody - TEXT 

And then the following queries will appear:

 SELECT * FROM University, Comments WHERE UniversityID = CommentItemID; 

So what do you all think? Will this system scale with large amounts of data, or is there a better one (perhaps a better option or template)?

I thank you in advance.

Edit 1: I changed the definition of the comment to include the primary key and indexed column to solve the problems raised so far. Thus, the system may also have comments on comments (not sure how this will confuse this in practical code, but it has a certain mathematical completeness that I like). I wanted the system to be as similar as possible, although until I accepted the answer.

In both answers so far, Sebastian Goode and Brian M. have proposed a double primary key of two integers, which are something like ItemID and TableID. My only hesitation in this method is that I would either have to have a new table listing the table identifiers and their required row table names, or enter global variables in my code that references them. If there is no other method that I am missing, this seems like extra code that can be avoided for me.

What do you all think?

Answer 1

I would just use a more traditional approach to relations with a foreign key between comments and what they are attached to.

 UNIVERSITY UniversityID // assuming primary key COMMENTS CommentID // assuming primary key TypeID // Foreign Key Type // Name of the table where the foreign key is found (ie, University) 

This is just a little for me. Some about using the foreign key of another table as the primary key for your comments did not feel good.

Answer 2

If you are using a UUID, it is difficult to figure out which table it came from. If you just want to look at the comments from the entity, as in your request, everything will be fine. If you want to look at a comment and find out what it is, you will need to look at all the possible tables (universities, buildings, etc.) to find out.

One of the features that allows you to use simple consecutive integers for the keys of your base objects (which is often desirable for readability, index fragmentation, etc.) is to make the key of your comment table two columns. One of them is the name of the table to which the comment refers. The second is the key to this table. This is similar to the approach suggested by Brian M., but note that you cannot actually define foreign keys from the comment table to all possible parents. Your queries will work in both directions if necessary, and you do not need to worry about UUIDs, as the combination of table name + ID will be unique in the database.

Answer 3

Well, since no one seems to want to answer, I think I just stick to my method. However, I will continue to be open to accepting other proposals.