T-SQL: Complete Database in one table

Question

at a relational data model every logical relation is a single table with values and so on. I know someone who is this too much work and he wants to create only one table which contains all. This could be look like:

ID | Name       | N    | M    | Value
------------------------------------------------
1  | Customer   | NULL | NULL | NULL
2  | LastName   | NULL | NULL | NULL
3  | FirstName  | NULL | NULL | NULL
4  | NULL       | 1    | 2    | Thomson
5  | NULL       | 1    | 3    | Steve
6  | House      | NULL | NULL | NULL
7  | Color      | NULL | NULL | Blue
8  | Color      | NULL | NULL | Red
9  | Blue House | 6    | 7    | NULL
10 | Red House  | 6    | 8    | NULL

Make it sense to design a database in this kind or should it be "classic"?

Thanks for any response.

Answer 1

Congratulations, someone has reinvented EAV (Entity Attribute Value). Please study up on the subject with them.

The short form is: one or more EAV tables can be useful in certain specific cases, usually when accompanied by other tables, but you lose most of the benefit of a relational database when you move to them.

I would also ask: If someone wants to put everything in a single EAV, for what purpose does someone still want to use SQL Server? What benefits does SQL Server provide at that point over other approaches, particularly transactional databases (one example is a simple structure where you have "key columns" then "data columns" and you always look up data via the key).

Wikipedia has a reasonable entry.

A couple interesting notes from Wikipedia (the second of which applies in particular to the "only one Value column in the whole database" idea you listed someone as having proposed):

• an index on the value column of an EAV table is essentially useless
• constant data type inter-conversions are required if one wants to do anything with the values

Tony Andrews covers EAV as well as OTLT with an example of an EAV query that gets a bit cumbersome.

Aaron Bertrand argues for the EAV approach in select cases.

Aaron Bertrand has perhaps the best bullet list:

Cons from Aaron:

• it is difficult to control the attribute names, and to make them consistent;
• it is difficult to enforce data type integrity and referential integrity;
• it is difficult (and slow) to pivot and/or self-join the values to make a single row;
• it is difficult to make certain attributes mandatory;
• and, we've been trained to view this as the "wrong" way to solve the problem.

Pros from Aaron:

• But what do you do when you have customers that demand real-time, on-demand addition of attributes that they want to store?

Answer 2

This is bad design practice and should be avoided. Switch to NoSQL DB if this is really needed.

Answer 3

Relational databases are designed to ensure data structure complexity and flexibility. Having all records in a single table can IMHO mean two things - a bad design, or you don't need a database at all. Why not just use an Excel table then?

The example you provided shows that you want to store people and their houses, colors, etc in the same table. Having your apples and oranges in the same basket (i.e. table) is not a solution

A good starting point is to check MS AdventureWorks2012 database . It represents a good example of splitting various data into related tables, simple and self-explanatory

Answer 4

to deiced if schema design is good or bad what you should ask yourself is

"what different types of query i will use against schema to access this data".

good or bad is very subjective and based on our own experience we can do educated suggestions but most accurate thing will be what is your business requirement.

start from looking at most frequent queries and ask

how they will perform.

how much this schema scale.

what will be db perform with 100M+ rows.

what is the schema maintenance cost.

What needs to be done to make sure the referential data is is in-place all time.

Bring this kind of questions up in discussion and see what the response is. If I am confidant about my schema I must have done my homework.

Unless there is a scheduled plan to redesign the db in 1/2/3 years you should think about how the design will accommodate any future requirements with minimal changes in system and also w/o compromising the performance.