Schema for dynamic number of attributes

database-design

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

Best Answer

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.

Related Solutions

Modelling Hierarchical attributes

You could formalize the approach that you're already using and make it more efficient to both maintain and query. Consider an ERD like so:

ERD

Note that all of your hierarchy is flattened into a single set of columns. There is an involuted relationship so that each child entity in the hierarchy can point to it's immediate parent. I've also included left and right visitation numbers which would really help you with your price lookups. Lastly there is a Level column to store the rank within the hierarchy.

If you're not familiar with visitation numbers please have a look at my answer to this question, where I go into quite a bit of detail.

So why is this better than what you're doing now?

You only track each item in the hierarchy once. Instead of having 4001 show up in the channel column many, many times, you just have one record with 4001.
The structure is flexible and allows for insertion of new levels at a later time. It also allows for uneven trees, say for example ZONE 3001 is subdivided into three SUBZONEs and that cities are under the subzones there (but go direct to zone outside of 3001).
All of your prices are in exactly one place (which doesn't need any reading logic that goes outside of normal SQL queries. Instead of having to have logic that joins a price to different Entity tables depending on what range the EntityID is in, you have one consistent price retrieval query.
You don't need coded meaning in your identifiers so you won't run into trouble when you get too many entities at any one level, thus breaking your coding scheme.
The price retrieval query is dead simple:


-- Look up the left and right numbers for the store in question:
declare @StoreLeft int
declare @StoreRight int
select @StoreLeft = H.left, @StoreRight = H.right
from HIERARCHY H where H.ID = @DesiredStoreId

-- Look up the lowest level (most granular) price for this store:
select TOP 1
  P.price_amt
, P.currency
from PRICE P
  inner join HIERARCHY H
    on H.Left <= @StoreLeft and H.right >= @StoreRight
  inner join ARTICLE A
    on P.article_id = A.id
where A.id = @TheArticleInQuestion
order by H.Level ASC   -- ASC or DESC depends on how you count levels.

Many-to-many relationship when one entity is without attributes

If the cluster really has absolutely no attributes then no, you don't need a separate table for these entities. The following would do:

User            UserCluster
====            ===============
UserID (PK) --> UserID (FK, PK)
UserProp1       ClusterID (PK)
UserProp2
...
UserPropN

But it is unusual that an entity has absolutely zero properties: they usually have at least have a human friendly name for display purposes. Of course you could make the cluster's ID the same as the name, but as names can change it is usually not recommended that you do this (where possible the primary key should be a value that is immutable during the existence of an entity).

Related Question