Storing data as rows as opposed to columns

database-design

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.

Best Answer

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.

Related Question