2

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
samsina
  • 973
  • 2
  • 11
  • 16
  • Well, this table doesn't need to normalized, and very minimal read and write. I HAVE to store in DB for very specific reason. Also another thing is that; since this table is acting like a properties file, I have to change my schema on regular tables anytime I want to add a property to my application that is being read from DB (not scalable...) – samsina May 13 '09 at 06:41

2 Answers2

11

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

Community
  • 1
  • 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Yep, in the long run, doing this will make you cry. For the first 5 minutes though it seems fantastic!! – Matthew Watson May 13 '09 at 04:45
  • Amen, Bill! +1 for the focus on long-run maintainability. Lots of tables really hardly ever is an issue - "magic" tables like this will be - for sure! – marc_s May 13 '09 at 05:46
3

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.

cletus
  • 616,129
  • 168
  • 910
  • 942