Owner ID type database fields

Question

Suppose you have these tables: RestaurantChains, Restaurants, MenuItems - with the obvious relations between them. Now, you have tables Comments and Ratings, which store the customer comments/ratings about chains, restaurants and menu items. What would be the best way to link these tables? The obvious solutions could be:

• Use columns OwnerType and OwnerID in the tables Comments and Ratings, but now I can't add foreign keys to link comments/ratings with the objects they are ment for
• Create separate tables of Comments and Ratings for each table, e.g. MenuItemRatings, MenuItemComments etc. This solution has the advantage that all the correct foreign keys are present and has the obvious disadavantage of having lots and lots of tables with basically the same structure.

So, which solution works better? Or is there even a better solution that I don't know about?

Answer 1

Have a single Comments/Rating table for all the objects and dont use automatically generated foreign keys. The key in the ratings table eg RatingID can be placed in a field in Restaurant, Chain, Menuitems table and they can all point to the same table, they are still foreign keys.

If you need to know in reverse what object the review relates to you would need to have a field specifying the type of review it was, but that should be all.

Answer 2

Use a single table for comments and use GUID's as primary keys for your entites.

Then you can select comments without even knowing beforehand where they belong to:

SELECT CommentText
FROM Comments c, Restaurants r
WHERE c.Source = r.Id

SELECT CommentText
FROM Comments c, Chains ch
WHERE c.Source = ch.Id

etc.

You can't use foreign keys for comments, of course, but it's not that comments cannot live without foreign keys.

You may clean orphaned comments in triggers but there's nothing bad if some of them are left.

You amy also create a global Entity table (with a single GUID column), make your Chains, Restaurants, MenuItems and Comments refer to that table with a FOREING KEY ON DELETE CASCADE, and when DELETE'ing, say, a restaurant, delete it from that table instead. It will delete both a restaurant and all comments on it, and you still have your integrity.

Answer 3

If you want to take advantage of foreign key constraint and normalize the attributes of comments (and ratings) across base tables, you may need to create relationship tables between base tables and comments (and ratings).

e.g. for Restaurants and Comments:

Restaurants
  id (PK)
  (attributes of restaurants...)

RestaurantComments
  id (PK)
  restaurantid (FK to Restaurants)
  commentid (FK to Comments)

Comments
  id (PK)
  (attributes of comments...)

Answer 4

Since comments about a menu item are different from comments about a restaurant (even if they happen to share the same structure) I would put them in separate tables and have the appropriate FKs to enforce some data integrity in your database.

I don't know why there is an aversion to having more tables in your database. Unless you're going from 50 tables to 50,000 tables you're not going to see a performance problem due to large catalog tables (and having more, smaller tables in this case should actually give you better performance). I would also tend to think that it would be a lot clearer to understand when dealing with tables called "Menu_Item_Comments" and "Restaurant_Comments" than it would to deal with a table called "Comments" and not knowing what exactly is really in it just by the name of it.