0

I am implementing an application where users can save alerts for different services. Each service can have multiple types of alerts and each alert has X number of attributes to form a predicate. So here's an example:

Services:
- Weather
- Reddit

Weather Alerts:
- Temperature

id
user_id
type
city
condition (above, below)
temperature 

- Forecast

id
user_id
type
city
forecast (rainy, sunny)

Reddit Alerts:
- Frontpage

id
user_id
type
number_of_votes

- User comment

id
user_id
type
reddit_user
number_of_votes

I think this gives a good idea of the model. All the alerts are similar but have a different number of attributes. Now my question is: How should I store these alerts in my database? Here are the options I'm considering so far:

  1. 1 table with all the possible fields. The first three (id, user_id, and type) would always have some data but most of the others would be empty as the number of alerts type grow. I don't think that's a good option.
  2. 1 table per type of alert. This seems like an easy solution but might be tricky for real life scenarios. I'll have to do plenty of joins if I want to get all the alerts for a user since they will be in different tables. The id, user_id and type fields are also always needed so this feels wrong.
  3. 1 to many relationship where I have an alert table and an alert has many attributes or conditions. That second table is basically just alert_id, attr_name, value. When I want to use an alert I just fetch all the attributes for that alert_id.
  4. EAV model.

What are your suggestions? Am I missing something here or making all of this too complicated? Database modelling was never one of my strong areas so I'm a bit struggling to make a decision.

If it matters this is for a ruby on rails application.

2
  • 1
    Which DBMS are you using? In Postgres you can very efficiently use the hstore (key/value) data type for this kind of problems
    – user1822
    May 22, 2014 at 8:40
  • Most likely Postgres in production. I'll take a look at hstore. Thanks.
    – Chris911
    May 23, 2014 at 1:58

1 Answer 1

1

Option 3 sounds like your best option as it gives you all the flexibility you need, fits into a RDBMS perfectly and follows normalisation.

Option 1: requires schema changes when new alerts are added to the system.

Option 2: More joins = slower queries.

Option 4: This does give you the flexibility you need but if you were going to do it this way then maybe consider using a document store like MongoDB.

1
  • Pretty much confirms what I was thinking. Thanks for your input.
    – Chris911
    May 23, 2014 at 1:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.