4

Our previous DBA got fed up with the development teams frequent requests to change the database schema to add and delete columns. He then advised to the developers that he would create simple tables with the following definition.

+---------------+---------+
| Record Number | VarChar |
+---------------+---------+
| Column Name   | VarChar |
+---------------+---------+
| Column Value  | VarChar |
+---------------+---------+

So if the developers wanted a table which normally look like the following

+-------------+---------------+-----------------+
| Employee ID | Employee Name | Employee Salary |
+-------------+---------------+-----------------+
| 0001        | John Doe      | 100000.00       |
+-------------+---------------+-----------------+
| 0002        | Jane Doe      | 110000.00       |
+-------------+---------------+-----------------+
| 0003        | Jack Doe      | 120000.00       |
+-------------+---------------+-----------------+

They could add rows in the following fashion

+---------------+-----------------+--------------+
| Record Number | Column Name     | Column Value |
+---------------+-----------------+--------------+
| 1             | Employee ID     | 0001         |
+---------------+-----------------+--------------+
| 1             | Employee Name   | John Doe     |
+---------------+-----------------+--------------+
| 1             | Employee Salary | 100000.00    |
+---------------+-----------------+--------------+
| 2             | Employee ID     | 0002         |
+---------------+-----------------+--------------+
| 2             | Employee Name   | Jane Doe     |
+---------------+-----------------+--------------+
| 2             | Employee Salary | 110000.00    |
+---------------+-----------------+--------------+
| 3             | Employee ID     | 0003         |
+---------------+-----------------+--------------+
| 3             | Employee Name   | Jack Doe     |
+---------------+-----------------+--------------+
| 3             | Employee Salary | 120000.00    |
+---------------+-----------------+--------------+

This obviously does not meet the smell test, and makes me want to analyze what Database normalization would such a setup break.

Does this break 1NF? 2NF? 3NF? BCNF? Explanations would be nice.

  • 8
    this is known as an EAV model and it basically breaks every normal form – Lamak Jul 6 '18 at 16:23
  • 2
    EAV can do anything, badly. – Jasen Jul 8 '18 at 9:39
6

This is a terrible pattern, but it doesn't actually break any normalization rules. The reason is that it's actually a change in what you are modeling. Instead of your database modeling, say, Employees, it models Entities, Attributes, and Values.

  • 2
    I wouldn't necessarily call it "a terrible pattern", since this has it uses (sqlblog.org/2009/11/19/what-is-so-bad-about-eav-anyway) – Lamak Jul 6 '18 at 17:15
  • @David Browne: I agree it's like lazily saying that entities have various attributes with values. With out taking the trouble to investigate what those attributes actually are and what data type and constraints can those value have. – Rahul Khimasia Jul 7 '18 at 13:56
  • 1
    In terms of RDBMS the table represents a type. EAV is an approach to use RDBMS for data that has no strict (or any) type. EAV is a meta-type that violate the main principle of the relational algebra: each entity is represented by single tuple. Instead EAV represents the entity as the set of rows that make impossible to use relational algebra. EAV-related code can mimic the relational algebra but that is just kind of hack. RDBMS should be replaced by more suitable tool if EAV is emerged. Sure if such kind of tool exists. – Kondybas Jul 7 '18 at 18:02
  • 2
    eav is really inconveient to work with, if your database supports free-form structured types like XML or JSON use one of them and put one entitiy in each row, – Jasen Jul 8 '18 at 9:49
2

I agree with David Browne's answer and Lamak's comment. They helped me a lot to do my analysis.

As Lamak mentioned in his comment, such a model is known as an EAV model. Although the EAV model may not break any normalization rules, it should mostly be used to model entities where the number of attributes (properties, parameters) that can be used to describe them is potentially vast, but the number that will actually apply to a given entity is relatively modest. EAV models are an efficient way to store data, but they are inefficient and difficult to query.

In situations where the domain's entities have well defined attributes, a Relational model is far more superior and desirable. When such a model is implemented in a RDBMS, users can leverage powerful RDBMS features like efficient storage techniques and powerful querying abilities.

0

For me it breaks the first normal form.

Why? "Column Value" does not represent a unique domain of values (in this case: ID, name and salary are different data types: int, character and decimal).

So, an example would be something as simple as calculating the SUM() of the salaries, you have to do a complex query for this.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.