Data attribute table vs many small tables

Question

I am working for a client that currently has a DataAttributes table with columns Name, Type, Value, and ParentID. This table is used to store virtually everything such as US states, anatomical items, combo box selections, and system settings. I would seriously like to get rid of this table and break each section into its own table.

Are there any pros and cons in doing so?

Answer 1

This is a database anti-pattern called an EAV table. It creates performance issues and is an extremely bad idea most of the time.

http://www.simple-talk.com/opinion/opinion-pieces/bad-carma/

Answer 2

Pros - one user interface can be used to maintain anything in the whole system

Cons - inefficient, potentially risky (e.g. corruption or accidental deletion could have systemwide ramifications)

On the other hand, "if it ain't broke don't fix it" is a very good rule to follow. Unless you are having performance problems, or this will be an impediment to future development you have to do, what's the payoff for redesigning it now?

Answer 3

This approach throws away many features of a DBMS. You can't use database design to enforce data integrity. Some examples:

• Referential integrity (foreign keys)
• Domain integrity (check constraints)
• Nullability (NULL/NOT NULL)
• Data type
• Uniqueness (unique constraint)
• ...

I'm not sure there are any pros that really offset the cons.