Generic DB Table

Question

Is there any name for the following DB table design:

Basically we have generic columns representing key/value pair.

id | k1 | v1 | k2 | v2 | k3 | v3 | ....

1 | name | sam | last_name| smith | NULL | NULL | ...

In my application, I have many tables that have only one row and I would like to merge them into a generic table that has X number of columns with each rows representing singular table rows.

Thanks in advance.

Answer 1

Entity-Attribute-Value. Also called a "Name-Value Table" or an "Open Schema."

This is an SQL Antipattern. It fails many rules of relational database design, and it's incredibly hard to maintain. I recommend against it.

See more of my thoughts about EAV in my answer to the question "Product table, many kinds of product, each product has many parameters."

Answer 2

Bad idea. Basically you should use your relational database as a relational database.

Key/value pairs ordinarily aren't the advised storage method but at least single rows with a single key and a single value are "correct".

If you put multiple pairs in one row, it raises a number of issues:

1. How do you find a particular key?
2. Can that key be in k1, k2 or k3? Or is it always in a specific column?
3. How do you enforce that?
4. How do you make it performant?

Model your entities as entities with known attributes. That's what databases are for.

Answer 3

I'm not sure about the use of a database.

Why don't you just use a properties or an XML file or something else to store those key/value data?