Database Structure for a website commenting system

Question

I'm working on a website currently that needs a commenting system. As this website is brand new, and the database structure has yet to be set in stone, I would like some suggestions on how to best handle a commenting system such as this:

• Comments must be able to be placed on anything. Including items in future tables.
• Comments must be quickly (and easily?) queryable.

I know that this alone is not much to go on, so here is the idea: Each university has Colleges, each College has Buildings, and each Building has Rooms. Every user should be able to comment on any of these four items (and future ones we may add later), but I'd like to avoid making a comments table for each item.

The solution I have come up with this far seems to work, but I'm open to other ideas as well. My solution is to use UUIDs as the primary key for each item (university, college, building, room) table, then have the reference id in the comments table be that UUID. While I don't think I can make a system of foreign keys to link everything, I believe that nothing will break as only items available can possibly have comments, therefore an item can either have no comments, or if it is deleted, then the comments simply will never be returned.

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 queries will appear like:

SELECT * FROM University, Comments WHERE UniversityID = CommentItemID;

So what do you all think? Will this system scale will with large amounts of data, or is there a better (maybe Best Practice or Pattern) way?

I thank you in advance.

Edit 1: I have altered the Comment definition to include a primary key and indexed column to address the issues raised thus far. This way the system can also have comments of comments (not sure how confusing this would be in practical code, but it has a certain mathematical completeness to it that I like). I wanted to keep the system as similar as possible though until I have accepted an answer.

Both answers so far by Sebastian Good and Bryan M. have suggested a dual primary key of two integers being something like ItemID and TableID. My only hesitation with this method is that I would either have to have a new table listing the TableIDs and their correstponding string table names, or introduce global variables into my code referencing them. Unless there is another method I am missing, this seems like extra code that can be avoided to me.

What do you all think?

Answer 1

Well, since no one appears to want to answer, I guess I'll just stick with my method. However, I'll still be open to taking other suggestions.

Answer 2

I would just take a more traditional approach to the foreign key relationship between the comments and whatever they're bound 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 just feels a bit cleaner to me. Some about using a foreign key of another table as the primary key for your comments didn't feel right.

Answer 3

If you use a UUID, it's hard to know what table it came from. If you only ever want to look from the entity down to the comments, as in your query, it'll work alright. If you want to look at a comment and find out what it was on, you'll have to look at all possible tables (universities, buildings, etc.) to find out.

One possibility which enables you to use simple sequential integers for keys of your base entities (which is often desirable for readability, index fragmentation, etc.) is to make the key of your comments table contain two columns. One is the name of the table the comment applies to. The second is the key of that table. This is similar to the approach Bryan M. suggests, though note that you won't be able to actually define foreign keys from the comments table to all possible parents. Your queries will work both ways round if necessary, and you don't need to worry about UUIDs, as the combination of table name + ID will be unique across the database.