4

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?

4

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.

  • 1
    Does this still apply if a user can be a consumer and a merchant at the same time? – JC Lee Oct 12 '15 at 21:11
  • 1
    Yes, if the unique information placed in the proper subtype, then all should be clear. – RLF Oct 12 '15 at 21:14
  • 2
    If you go with the three table solution, you may also be interested in a technique called shared primary key. In this technique, the Id field in Consumers and Merchants is a copy of the Id field in Users, and therefore a foreign key reference to it. This means that a foreign key in some fourth table can point to the users table and also the consumers table, the merchants table, or both. – Walter Mitty Oct 14 '15 at 11:06
  • If you want to do some more reading, look up what Martin Fowler refers to as Class Table Inheritance. – Walter Mitty Oct 14 '15 at 11:06
  • 1
    @WalterMitty - I agree that it is useful to use the SuperType ID for the SubType IDs. – RLF Oct 14 '15 at 15:52
0

I would go ahead with the EAV implementation but would further decorate the attributes with an additional table (AttributeGroup), which could be quite handy for reporting and segregation purpose, something like this:

Table name: AttributeGroup

Id        Name
==        ==========
1         Consumer
2         Merchant
3         Something else

Then, you can have one more One-to-many relational table with Attributes. Just explaining the concept, but if you need me to provide an example, then please let me know.

0

This is old, here is my more modern advice here, are you going to query on the extra fields,

  • if so, consider normalization.
  • if not, save yourself the time and use a JSON. It's an integral part of SQL:2016.

Currently, MySQL provides a Binary JSON type. MariaDB has acknowledged one of the many errors in their way, and they're moving to implement a binary JSON field. This largely but not entirely marks the death of EAVs, the only time I would consider an EAV currently is if the attributes themselves were the subject of a query, and they were only key-value pairs.

And, that's not to say that JSON won't trump EAVs more in the future. Under many workloads PostgreSQLs GiST can provide competitive querying or even beat EAV performance. It's only a matter of decades before MySQL provides the same functionality.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.