2

Here's a typical way I would store data (obviously not storing the password in plain text)

    USER TABLE

    | UserID | UserName | FullName | EMail   | Password |
    |--------|----------|----------|---------|----------|
    |1       |userAAA   |User Aaa  |[email protected]|aAaaA     |
    |1       |userBBB   |User Bbb  |[email protected]|bBbbB     |
    |1       |userCCC   |User Ccc  |[email protected]|cCccC     |
    |--------|----------|----------|---------|----------|

Is there anything wrong with storing it in the following manner?

    USER TABLE                 ATTRIBUTES TABLE

    | UserID | UserName |      |AttributeID | Attribute |
    |--------|----------|      |------------|-----------|
    |1       |userAAA   |      |1           |Full Name  |
    |1       |userBBB   |      |2           |E-Mail     |
    |1       |userCCC   |      |3           |Password   |
    |--------|----------|      |------------|-----------|

    ATTRIBUTE_VALUES TABLE

    |UserID | AttributeID | AttributeValue |
    |-------|-------------|----------------|
    |1      | 1           |User Aaa        |
    |1      | 2           |[email protected]       |
    |1      | 3           |aAaaA           |
    |2      | 1           |User Bbb        |
    |2      | 2           |[email protected]       |
    |2      | 3           |bBbbB           |
    |3      | 1           |User Ccc        |
    |3      | 2           |[email protected]       |
    |3      | 3           |cCccC           |
    |-------|-------------|----------------|

The huge benefit I see here is the ability to easily add additional attributes to the user. But I really wanted to get another opinion on this.

4
  • 1
    Think about it: how do you reconstruct a users data with this design (called EAV), compared to a traditional users table? This design has its use cases though. Apr 23, 2013 at 21:24
  • 1
    If you are using Postgres, have a look at hstore. It is essentially a key/value store (or to go with the hype: a NoSQL) which will make this kind of dynamic attribute much easier to deal with.
    – user1822
    Apr 23, 2013 at 22:01
  • 1
    From my point of view, if you are forced to use EAV, it means you failed at getting good requirements and specifications for your project in the first place. So maybe you should look back at your project requirements and work on them.
    – guigui42
    Apr 24, 2013 at 2:53
  • 1
    this is the first question in any job interview for application dba. the advantage is as you said - you can easily add attributes. the draw down is that you have to do a lot of joins for querying that data or for filtering by attributes. think of that - how often you add attributes ? how often you query the data ? ....
    – haki
    Apr 24, 2013 at 9:33

2 Answers 2

15

This is known as the Entity-Attribute-Value design. See Best Practices for Semantic Data Modeling for Performance and Scalability for a lengthy discussion of pros and cons.

The main problem is querying becomes both difficult to express at design time and unperformant at runtime.

Better approaches are to have a true schema, properly indexed, for known attributes, and allow EAV for future, unknown, custom, non-search critical attributes.

Schema-free databases, like MongoDB, are also alternatives to EAV.

0
  • No more Type specific fields (no more Dates, Integers etc ...)
  • No more Constraints or Foreign keys

In other words, no more Data Integrity !

So unless you don't care about Data Integrity, it's a big "no-no".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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