Database design: flexibility vs. simplicity

Question

I am trying to weigh up the relative pros and cons of a simple database structure such as this:

1.

CREATE TABLE x (
    my_id INT PRIMARY KEY,
    ...,
    text_attribute_blah TEXT,
    text_attribute_blah_blah TEXT
);

vs:

2.

CREATE TABLE x (
    my_id INT PRIMARY KEY,
    ...
)

CREATE TABLE attributes (
    my_id INT,  /* foreign key to x.my_id */
    text_attribute_type INT,
    text_attribute TEXT
)

Where attribute_type could be blah or blah_blah.

Option 1 offers simplicity - the table is easier to read/write; Option 2 offers flexibility (if we want to add another attribute such as blah_blah_blah, we don't need to make schema changes and so probably fewer code changes.)

Is there a right/wrong answer to this conundrum? Is one of these options considered better practice than the others? Can you point me at further reading that might help be determine the way forward?

Answer 1

I'd almost always choose #1 - I just prefer to have attributes as columns in my tables - makes querying, indexing for performance and the general handling much easier and more transparent.

the #2 option is called EAV - Entity Attribute Value - and it has some major drawbacks - see

• Five simple database design errors you should avoid - the EAV approach is #3 on this list
• Joe Celko: Avoiding the EAV of destruction

Answer 2

It's interesting that you don't mention either performance or data integrity as concerns. For what it's worth, model #1 is the best approach for those considerations.

Flexibility is vastly over-rated with regards to data models. Most table structures are well-known at the start of development and remain stable throughout the lifetime of a database. If you have an application where the model is genuinely fluid and unknowable then probably you should not be using an RDBMS at all. Choose one of the NoSQL products instead.

So that's another vote for #1.