Best Practices For Creating a Notifications Database Table

database, foreign-keys, mysql, ruby-on-rails, sql

Solution

If you are using MySQL add another column to specify the foreign object table.

`id | status (unread/read) | message | reference_object_id | reference_object_table`

Now you can reference another object in any table.

Problem

I'm stumped on how to best do this, and was hoping the community could shed some light on it (maybe you have experience, or have an innovative way of solving this!). I have an app where there are users, events, and groups. I'm trying to make a notifications table which will store notifications for users that they've been invited to events, groups, or connected with friends. No, I'm not building Facebook hah. The notification should have a message that says something like: "You've been invited to the Syracuse Runners Club" and you click it to jump to the Runners Club group object. So, my best guess so far is having a table structured as follows: Notifications ``` id | status (unread/read) | message | reference_object_id | ``` –where the reference object_id points to the id of the foreign object. However, the problem is that I won't know which table the foreign object_id belongs. My ultimate goal is to have this in JSON form so that I can have something along the lines of: ``` { message:"You're invited blah blah", status:"unread", reference_object: { id:1, name:"Runners Club" } } ``` I'm using MySQL and Ruby on Rails.

Original source