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