Database design: for EAV or not for EAV?

Question

Let's say I have an entity that will have many attributes, some of which I know now, while others will be defined by the user. What is the best way to simulate this?

1) Do I have a main table and relate it to the secondary table of name-value pairs? All attributes are in the second EAV table.

• OR -

2) I put the most common attributes (not all users will need them, so I expect a lot of NULL entries) in the main table and have a secondary EAV table for user attributes?

• OR -

3) Any other approach that I did not think about?

Answer 1

As a rule, many empty cells are cheap and should not be normalized. The only converse to # 2 is if you have a very large number of rows (millions — where performance problems may occur), a very large number of columns (more than 20 — where it is just annoying to see the data), or there are many unique restrictions on the EAV table.

With that said, now is 2011, and today it makes sense to use a programming structure with a database abstraction level so as not to directly create a relationship with the database. Something like Django Relational Mapper object allows you to focus on the models themselves and let you take the best care of yourself (in 95% of cases). This tutorial will help you get started. Django is only used to model a web development database. For non-web environments, it is better to use other environments.

Answer 2

You can use solution two for efficiency reasons, especially if you often need to select these quantities. These values ​​may be the "cache" of the EAV table, if you wish. You introduce duplication, but speed up the search.

EAV is a good solution to this problem if you do not need to perform joins at the db level. An alternative is to move from a relational model to a model based on RDF.

Answer 3

I worked a lot with the EAV template, and it served the purpose well. I find empty columns, or dynamic columns (e.g. col1, col2, etc.) It is much harder to deal with the controls after the fact, but it may be easier to query because you don't need so many joins.

One thing that I highly recommend is to take a look at options like Mongo DB. It automatically processes complex dynamic data structures.