Key/Value Pairs in a Database Table

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.

SQL Key Value Pair Query

You first need to group the Key value pairs. This can be achieved using a concat operatoor like ||, you need to think about nulls as well. NUll concatenated with NULL is still NULL in most DBs.

SELECT ProductID, Key || ':' || Value as KeyValue FROM AttributeTable

Then you would need to group those using an aggregating function like STRING_AGG (Assuming SQL Server above 2017). Other databases have different aggregate functions Mysql f ex uses GROUP_CONCAT

https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017
https://www.geeksforgeeks.org/mysql-group_concat-function/

SELECT ProductID, STRING_AGG( Key || ':' || Value, '|') as Key Value FROM AttributeTable GROUP BY ProductId

I can expand on the answer if you can provide more information.

Key-value pairs in SQL table

There is even better solution if you are using Postgresql, which is JSONField. You can directly store the key value pair in the JSONField. Try like this:

# model
class Statistics(models.Model):
    records = JSONField()

# usage
statistics = {
    'last_successful_transaction_date': datetime(2010, 2, 3),
    'total_transaction_amount': Decimal('1234.56'),
}

Statistics.objects.create(records=statistics)

Statistics.objects.filter(records__last_successful_transaction_date= datetime(2010, 2, 3))  # Running query

s = Statistics.objects.filter(records__last_successful_transaction_date= datetime(2010, 2, 3)).first()
new_statistics = {
    'last_successful_transaction_date': datetime(2012, 2, 3),
    'total_transaction_amount': Decimal('1234.50'),
}
records = s.records
records.update(new_statistics)
s.records = records
s.save()

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?

---

Related Topics