Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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  |aa@aa.com|aAaaA     |
    |1       |userBBB   |User Bbb  |bb@bb.com|bBbbB     |
    |1       |userCCC   |User Ccc  |cc@cc.com|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           |aa@aa.com       |
    |1      | 3           |aAaaA           |
    |2      | 1           |User Bbb        |
    |2      | 2           |bb@bb.com       |
    |2      | 3           |bBbbB           |
    |3      | 1           |User Ccc        |
    |3      | 2           |cc@cc.com       |
    |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.

share|improve this question
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. –  dezso Apr 23 '13 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. –  a_horse_with_no_name Apr 23 '13 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 '13 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 '13 at 9:33

2 Answers 2

up vote 15 down vote accepted

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.

share|improve this answer
  • 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".

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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