title: How do I design a flexible MySQL notification schema that references multiple tables?
body: <p>I decided to work on a notification system with inspiration from another StackOverflow post that suggested I break up the notification from the body of the notification. As such, I have the following schema:</p>
  
  <pre><code>notifications
  id | user_id | notification_object_id | is_read
  
  notification_objects
  id | message | type
  </code></pre>
  
  <p>I have it set up this way so I can create a notification object and send this out to multiple users in the case the notification object applies to multiple people. The type is there to condense messages into one notification by type, sort of the way Facebook says, "John Doe, John Smith, and 27 other users commented..." rather than giving you 29 separate notifications.</p>
  
  <p>(The naming schema of the table and column are for CakePHP 3's 'contains')</p>
  
  <p>What I need help on is a way to tie in the different users or objects that would be playing a role in the requests. A user might have commented, so I'll probably need the user_id of the user who messaged and need to store that somewhere.</p>
  
  <p>There are other cases besides users; one might want to know the id of the comment such that he can click the notification and go straight to comment/81. I have many use cases where I want to store the different types of ids that are foreign keys to other tables (users, comments, articles, etc.), some of which might need two foreign keys (say you want to mention "<a href="http://#" rel="nofollow">Chris</a> has commented on your <a href="http://#" rel="nofollow">article</a> 'How to beg StackOverflow for help'") and have the user_id for Chris and the article id for the article both linked in the notification.</p>
  
  <p>Each different type of notification could have different values and tables it needs to pull information from. What's a good place to start on creating this schema?</p>