Trying to avoid "Polymorphic Associations" and uphold foreign key referential integrity

Question

I'm building a site similar to Yelp (Recommendation Engine, on a smaller scale though), so there will be three main entities in the system: User, Place (includes businesses), and Event.

Now what I'm wondering about is how to store information such as photos, comments, and 'compliments' (similar to Facebook's "Like") for each of these type of entity, and also for each object they can be applied to (e.g. comment on a recommendation, photo, etc). Right now the way I was doing it was a single table for each i.e.

Photo (id, type, owner_id, is_main, etc...)
where type represents: 1=user, 2=place, 3=event

Comment (id, object_type, object_id, user_id, content, etc, etc...)
where object_type can be a few different objects like photos, recommendations, etc

Compliment (object_id, object_type, compliment_type, user_id)
where object_type can be a few different objects like photos, recommendations, etc

Activity (id, source, source_type, source_id, etc..) //for "activity feed"
where source_type is a user, place, or event

Notification (id, recipient, sender, activity_type, object_type, object_id, etc...)
where object_type & object_id will be used to provide a direct link to the object of the notification e.g. a user's photo that was complimented

But after reading a few posts on SO, I realized I can't maintain referential integrity with a foreign key since that's requires a 1:1 relationship and my source_id/object_id fields can relate to an ID in more than one table. So I decided to go with the method of keeping the main entity, but then break it into subsets i.e.

User_Photo (photo_id, user_id) | Place_Photo(photo_id, place_id) | etc...

Photo_Comment (comment_id, photo_id) | Recommendation_Comment(comment_id, rec_id) | etc...

Compliment (id, ...) //would need to add a surrogate key to Compliment table now

Photo_Compliment(compliment_id, photo_id) | Comment_Compliment(compliment_id, comment_id) | etc...

User_Activity(activity_id, user_id) | Place_Activity(activity_id, place_id) | etc...

I was thinking I could just create views joining each sub-table to the main table to get the results I want. Plus I'm thinking it would fit into my object models in Code Igniter as well.

The only table I think I could leave is the notifications table, since there are many object types (forum post, photo, recommendation, etc, etc), and this table will only hold notifications for a week anyway so any ref integrity issues shouldn't be much of a problem (I think).

So am I going about this in a sensible way? Any performance, reliability, or other issues that I may have overlooked?

The only "problem" I can see is that I would end up with a lot of tables (as it is right now I have about 72, so I guess i would end up with a little under 90 tables after I add the extras), and that's not an issue as far as I can tell.

Really grateful for any kind of feedback. Thanks in advance.

EDIT: Just to be clear, I'm not concerned if i end up with another 10 or so tables. From what I know, the number of tables isn't too much of an issue (once they're being used)... unless you had say 200 or so :/

Answer 1

Some propositions for this UoD (universe of discourse)

• User named Bob logged in.
• User named Bob uploaded photo number 56.
• There is a place named London.
• Photo number 56 is of place named London.
• User named Joe created comment "very nice" on photo number 56.

To introduce object IDs

• User (UserID) logged in.
• User (UserID) uploaded Photo (PhotoID).
• There is Place (PlaceID).
• Photo (PhotoID) is of Place (PlaceID).
• User (UserID) created Comment (CommentID) on Photo (PhotoID).

Just Fact Types

• User logged in.
• User uploaded Photo.
• Place exists.
• Photo is of Place.
• User created Comment on Photo.

Now to extract predicates

Predicate               Predicate Arity
---------------------------------------------
... logged in            1 (Unary predicate)
... uploaded ...         2 (Binary)
... exists               1 (Unary) 
... is of ...            2 (Binary)
... created ... on ...   3 (Ternary)

It looks like each proposition is this UoD may be stated with max ternary predicate, so I would suggest something like

Predicate role (Role_1_ID, Role_2_ID, Role_3_ID) is a part that an object plays in a predicate. Substitute the ... in a predicate from left to right with each Role_ID. Note that only Role_1_ID is mandatory (at least unary predicate), the other two may be NULL.

In this simple model, it is possible to propose anything. Hence, you would need to implement constraints on the application layer. For example, you have to make sure that it is possible to create Comment on Place, but not create Place on Place. Not all predicates represents action, for example ... logged in is an action while ... is of ... is not. So, your activity feed would list all Propositions with Predicate.IsAction = True.

Answer 2

If you rearrange things slightly, you can simplify your comments and compliments. Essentially you want to have a single store of comments and another one of compliments. Your problem is that this won't let you use declarative referential integrity (foreign key constraints).

The way to solve this is to make sure that the objects that can attract comments and compliments are all logical sub-types of one supertype. From a logical perspective, it means you have an "THING_OF_INTEREST" entity (I'm not making a naming convention recommendation here!) and each of the various specific things which attract comments and compliments will be a sub-type of THING_OF_INTEREST. Therefore your comments table will have a "thing_of_interest_id" FK column and similarly for your compliments table. You will still have the sub-type tables, but they will have a 1:1 FK with THING_OF_INTEREST. In other words, THING_OF_INTEREST does the job of giving you a single primary key domain, whereas all of the sub-type tables contain the type-specific attributes. In this way, you can still use declarative referential integrity to enforce your comment and compliment relationships without having to have separate tables for different types of comments and compliments.

From a physical implementation perspective, the most important thing is that your various things of interest all share a common primary key domain. That's what lets your comment table have a single FK value that can be easily joined with whatever that thing of interest happens to be.

Depending on how you go after your comments and recommendations, you probably will (but may not) need to physically implement THING_OF_INTEREST - which will have at least two attributes, the primary key (usually an int) plus a partitioning attribute that tells you which sub-type of thing it is.

Answer 3

If you need referential integrity (RI) there is no better way to do it than to use many-to-many junction tables. True, you end up having a lot of tables in the system, but that's the cost you need to pay. It also has some other benefits going this route, for instance you get some sort of partitioning for free: you get the data partitioned by their relation type, each in its own table. This offers RI but it is not 100% safe either, for instance there's nothing to guarantee you that a comment belongs to a photo and to that photo alone, you'd need to enforce this kind of constraints manually should you need them.

On the other hand, going with a generic solution like you already did gets you faster off the ground and it's way easier to extend in the future but there'll be no RI unless you'll code it manually (which is very complex and a lot harder to deal with than the alternative M:M for every relation type).

Just to mention another alternative, similar to your existing implementation, you could use a custom M:M junction table to handle all your relations regardless of their type: object1_type, object1_id, object2_type, object2_id. Simple but no other benefit beside very easy to implement and extend. I'd only recommend it if you don't need RI and you got yourself a lot of tables, all interlinked.