What normalization rules does this table break

Question

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.

Answer 1

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.

Answer 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.

Answer 3

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.