Data attribute table vs many small tables
I am working for a client that cur开发者_运维知识库rently 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?
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.
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/
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?
精彩评论