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:
condition (above, below)
forecast (rainy, sunny)
- User comment
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 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.
- 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.
- 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.
- 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.