Schema for dynamic number of attributes

Question

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.

Answer 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.