Best Practices For Creating a Notifications Database Table

Question

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.

Answer 1

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.

Answer 2

You can either have a column for each type of thing you can be notified about. Or you can store the "Type" of the referencing object