Alternatives to Entity-Attribute-Value (EAV)?

by

There is a difference between EAV done faithfully or badly; 5NF done by skilled people or by those who are clueless.

Sixth Normal Form is the Irreducible Normal Form (no further Normalisation is possible). It eliminates many of the problems that are common, such as The Null Problem, and provides the ultimate method identifying missing values. It is the academically and technically robust NF. There are no products to support it, and it is not commonly used. To be implemented properly and consistently, it requires a catalogue for metadata to be implemented. Of course, the SQL required to navigate it becomes even more cumbersome (SQL already being cumbersome re joins), but this is easily overcome by automating the production of SQL from the metadata.

EAV is a partial set or a subset of 6NF. The problem is, usually it is done for a purpose (to allow columns to be added without having to make DDL changes), and by people who are not aware of the 6NF, and who do not implement metadata. The point is, 6NF and EAV as principles and concepts offer substantial benefits, and performance increases; but commonly it is not implemented properly, and the benefits are not realised. Quite a few EAV implementations are disasters, not because EAV is bad, but because the implementation is poor.

Eg. Some people think that the SQL required to construct the 3NF rows from the 6NF/EAV database is complex: no, it is cumbersome but not complex. More important, an ordinary SQL VIEW can be provided, so that all users and report tools see only the straight 3NF VIEW, and the 6NF/EAV issues are transparent to them. Last, the SQL required can be automated, so the labour cost that many people endure is quite unnecessary.

So the answer really is, Sixth Normal Form, being the father of EAV, and a purer form, is the replacement for it. The Caveat is, ensure it is done properly. I have one large 6NF db, and it suffers none of the problems people post about, it performs beautifully, the customer is very happy (no further work is a sign of complete functional satisfaction).

I have already posted a very detailed answer to another question which applies to your question as well, which you may be interested in.

Other EAV Question

More Related Contents:

  1. What’s the difference between identifying and non-identifying relationships?
  2. What are the design criteria for primary keys?
  3. What is the most efficient way to store tags in a database?
  4. Should OLAP databases be denormalized for read performance? [closed]
  5. Best beginner resources for understanding the EAV database model? [closed]
  6. Database development mistakes made by application developers [closed]
  7. What’s wrong with foreign keys?
  8. Surrogate vs. natural/business keys [closed]
  9. What are database normal forms and can you give examples? [closed]
  10. NoSql vs Relational database
  11. single fixed table with multiple columns vs flexible abstract tables
  12. Multiple schemas versus enormous tables [closed]
  13. First name, middle name, last name. Why not Full Name?
  14. Techniques for database inheritance?
  15. Modeling Product Variants
  16. Microservices with shared database? using multiple ORM’s? [closed]
  17. Database EAV Pros/Cons and Alternatives
  18. In terms of databases, is “Normalize for correctness, denormalize for performance” a right mantra?
  19. How should international geographical addresses be stored in a relational database?
  20. Still Confused About Identifying vs. Non-Identifying Relationships
  21. Which is a good design for a database table that can be owned by two different resources, and therefore needs two different foreign keys? [closed]
  22. Ideas on database design for capturing audit trails [closed]
  23. NULL permitted in Primary Key – why and in which DBMS?
  24. How does the HyperLogLog algorithm work?
  25. Versioning Database Persisted Objects, How would you? [closed]
  26. What are the differences between B trees and B+ trees?
  27. Best design for a changelog / auditing database table? [closed]
  28. Derived account balance vs stored account balance for a simple bank account?
  29. In a StackOverflow clone, what relationship should a Comments table have to Questions and Answers?
  30. uncertainty in developing a database model

Leave a Comment