Key Value Pairs in Relational Database

Key value pairs in relational database

Before you continue on your approach, I would humbly suggest you step back and consider if you really want to store this data in a "Key-Value Pair"table. I don't know your application but my experience has shown that every time I have done what you are doing, later on I wish I had created a color table, a fabric table and a size table.

Think about referential integrity constraints, if you take the key-value pair approach, the database can't tell you when you are trying to store a color id in a size field

Think about the performance benefits of joining on a table with 10 values versus a generic value that may have thousands of values across multiple domains. How useful is an index on Key Value really going to be?

Usually the reasoning behind doing what you are doing is because the domains need to be "user definable". If that is the case then even I am not going to push you towards creating tables on the fly (although that is a feasible approach).

However, if your reasoning is because you think it will be easier to manage than multiple tables, or because you are envisioning a maintenance user interface that is generic for all domains, then stop and think really hard before you continue.

Storing key-value pairs in a database column

Obviously it depends on the particular case, but this sort of 1NF violation is generally a bad approach. One significant problem is that you can't ever query on the metadata. (E.g., "SELECT WHERE key2 = 'value3'") Another is that you can't ever update a single key/value without parsing, adjusting, un-parsing, and rewriting the whole large set. To address the claims individually:

1. Has this claim actually been tested against your data? If you only ever need one key/value from the record, you currently have to pay the database overhead to read the whole set, the network overhead to transport it to the client, and the cpu overhead to parse out the one piece you need. Doing that job inherently is precisely what the database was designed for, so you're essentially disabling the component that excels at that sort of work and poorly emulating it with unnecessary client-side programming.

2. How do they figure that? Storing all key/value pairs in a single field will degrade as the number of pairs increases.

3. Almost certainly irrelevant. Disk space is cheaper than bad design.

P.S. What happens if you have a value with two newlines in it?

Database for key value pairs

There is really no simple answer to this with so little information.
Take a google at "No SQL" or "Document Databases" - should find you what you need.

If you are after WP7 there are some good suggestions here http://nosql-database.org/

Key and value lookup in relational database

You simply want to join twice:

select t2a.D, t2b.D
from table1 t2 join
     table2 t2a
     on t1.A = t2a.C join
     table2 t2b
     on t1.B = t2b.C;

Note the use of two different aliases for table2. This is how these are distinguished in the query.

---

Related Topics