Difference between two table structure

by

Antipattern?

In common case, second table is anti-pattern in context of database design. And, even more, it has specific name: Entity-Attribute-Value (EAV). There are some cases, when using this design is justified, but that are rare cases – and even there it can be avoided.

Why EAV is bad

Data integrity support

Despite the fact, that such structure seems to be more “flexible” or “advanced”, this design has weakness.

  • Impossible to make mandatory attributes. You can not make some attribute mandatory, since attribute is now stored as a row – and the only sign that attribute is not set – is that the corresponding row absent in table. SQL will not allow you to build such constraint natively – thus, you’ll have to check that in application – and, yes, query your table each time
  • Mixing of data types. You will not be able to use SQL standard data types. Because your value column must be a “super-type” for all stored values in it. That means – you’ll have in general to store all data as raw strings. Then you’ll see how painful is to work with dates as with strings, casting data types each time, checking data integrity, e t.c.
  • Impossible to enforce referential intregrity. In normal situation, you can use foreign key to restrict your values by those, which are defined in parent table. But not in this case – that’s because referential integrity is applied to each row in table, but not for row values. So – you’ll loose this advantage – and it’s one of fundamental in relation DB
  • Impossible to set attributes names. That means – you can’t restrict attribute name on DB level properly. For example, you’ll write "customer_name" as attribute name in first case – and another developer will forget that and use "name_of_customer". And.. it’s ok, DB will pass that and you’ll end with hours spent on debugging this case.

Row reconstruction

In addition, row reconstruction will be awful in common case. If you have, for example, 5 attributes – that will be 5 self-table JOIN-s. Too bad for such simple – at first glance – case. So I don’t want even imagine how you’ll maintain 20 attributes.

Can it be justified?

My point is – no. In RDBMS there will always be a way to avoid this. It’s horrible. And if EAV is intended to be used, then best choice may be non-relational databases.

More Related Contents:

  1. Strings as Primary Keys in MYSQL Database [closed]
  2. Which DB technology is sued to solve the following search issue? [closed]
  3. How to select the nth row in a SQL database table?
  4. What does character set and collation mean exactly?
  5. What’s faster, SELECT DISTINCT or GROUP BY in MySQL?
  6. Storing JSON in database vs. having a new column for each key
  7. How do I restore a dump file from mysqldump?
  8. MySQL – Conditional Foreign Key Constraints
  9. single fixed table with multiple columns vs flexible abstract tables
  10. Entity-Attribute-Value Table Design
  11. In SQL, is it OK for two tables to refer to each other?
  12. Remove Primary Key in MySQL
  13. Modeling Product Variants
  14. Check for x consecutive days – given timestamps in database
  15. MySQL Insert Into from one Database in another
  16. Does mysql have the equivalent of Oracle’s “analytic functions”?
  17. MySQL: LAST_INSERT_ID() returns 0
  18. Remove special characters from a database field
  19. Using reserved words in column names
  20. Proper database model for a user feedback system (an interesting case)
  21. MySQL query, MAX() + GROUP BY
  22. MySQL – how to front pad zip code with “0”?
  23. MySQL – Replace Character in Columns
  24. FIND_IN_SET with multiple value [duplicate]
  25. What is the difference between SQL and MySQL? [closed]
  26. Selecting distinct 2 columns combination in mysql
  27. How to store a one to many relation in MySQL database?
  28. MySQL – Complexity of: SELECT COUNT(*) FROM MyTable;
  29. MySQL: Computed Column
  30. Inventory management with stock options

Leave a Comment