title: Trying to avoid "Polymorphic Associations" and uphold foreign key referential integrity
body: <p>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.</p>
  
  <p>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.</p>
  
  <blockquote>
    <p>Photo (id, <strong>type</strong>, <strong>owner_id</strong>, is_main, etc...)<br>
    <em>where type represents: 1=user, 2=place, 3=event</em>  </p>
    
    <p>Comment (id, <strong>object_type, object_id</strong>, user_id, content, etc, etc...)<br>
    <em>where object_type can be a few different objects like photos, recommendations, etc</em></p>
    
    <p>Compliment (<strong>object_id</strong>, <strong>object_type</strong>, compliment_type, user_id)<br>
    <em>where object_type can be a few different objects like photos, recommendations, etc</em></p>
    
    <p>Activity (id, source, <strong>source_type</strong>, <strong>source_id</strong>, etc..) <code>//for "activity feed"</code><br>
    <em>where source_type is a user, place, or event</em></p>
    
    <p>Notification (id, recipient, sender, activity_type, <strong>object_type</strong>, <strong>object_id</strong>, etc...)<br>
    <em>where object_type &amp; 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</em></p>
  </blockquote>
  
  <p>But <a href="https://stackoverflow.com/questions/441001/possible-to-do-a-mysql-foreign-key-to-one-of-two-possible-tables">after</a> <a href="https://stackoverflow.com/questions/4050784/defining-multiple-foreign-keys-in-one-table-to-many-tables/4051523#4051523">reading</a> a few <a href="https://stackoverflow.com/questions/3383300/foreign-key-to-one-of-many-tables">posts</a> 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.</p>
  
  <blockquote>
    <p>User_Photo (photo_id, user_id) | Place_Photo(photo_id, place_id) | etc...</p>
    
    <p>Photo_Comment (comment_id, photo_id) | Recommendation_Comment(comment_id, rec_id) | etc...</p>
    
    <p>Compliment (id, ...) <code>//would need to add a surrogate key to Compliment table now</code></p>
    
    <p>Photo_Compliment(compliment_id, photo_id) | Comment_Compliment(compliment_id, comment_id) | etc...</p>
    
    <p>User_Activity(activity_id, user_id) | Place_Activity(activity_id, place_id) | etc...</p>
  </blockquote>
  
  <p>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.</p>
  
  <p>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).</p>
  
  <p>So am I going about this in a sensible way?  Any performance, reliability, or other issues that I may have overlooked? </p>
  
  <p>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.</p>
  
  <p>Really grateful for any kind of feedback. Thanks in advance.</p>
  
  <p><strong>EDIT</strong>: 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 :/</p>