EAV Model vs Fixed Fields for Additional User Data in MySQL

database-designeavMySQLsubtypes

Say I have to store data about two different types of person roles or occupations, i.e., Consumers and Merchants, and both types of roles share a single base table called User.

In this respect, Merchants need to have a few additional fields, e.g., CompanyName, CompanyDescription.

What is the most efficient way to design a database for these specifications?

  1. Have extra columns on the User table.
  2. Have an extra table called MerchantData which has a UserId FOREIGN KEY.
  3. Have a UserMeta EAV table which allows any amount of additional values.

I am currently leaning towards option 2, as the additional fields are fixed. Are there any down sides that I am not seeing?

Best Answer

Since Consumers and Merchants share some common data as Users they should be in a single base table, as you have already mentioned.

An EAV implementation can be successful under some well planned and controlled use. But I see nothing in your question that would imply that an EAV solution is the right answer.

In fact, as you move further in your development you might find that not only do Merchants have some additional attributes, but so do Consumers. If so, you might want a simple Supertype-Subtype model.

That would give you 3 tables that are related as shown below:

               USERS (contains common information)
                 |
                 |
     ____________|_____________
     |                        |
     | (details specific to   | 
     |       a subtype)       |
Consumers                Merchants

If this works for you, much of the time you would only access the Users table, but when you needed additional information for a Consumer or a Merchant you would like to those details as well.

This is a pretty simple approach and avoids unneeded duplication of data.

For a perspective on EAV implementations, you might look at Aaron Bertrand's post, which explains the advantages and the limitations.

Note that in this case it was rapidly changing requirements which made the EAV decision a good choice.

Related Question