Postgresql – Key/value table: what is a good way to organize it if value could be foreign key

Architecturebest practicesforeign keypostgresql

Suppose a have a structure of two tables

Table Key names:

|_Key name_|_Parent_key_id_|
| key1     |  NULL         |
| key2     |  key1         |

Table Key values:

|_Key__|_Value__|_Version__|
| key1 | value1 | version1 |
| key2 | value3 | version1 |
| key1 | value2 | version2 |
| key2 | value3 | version2 |

So key values are unique within the Version and keys follow nested structure.

The goal is to organize tables the way that key values could be a string, number (integer or double) or reference (id) to a row from another table.

My thoughts are

  1. Store string, number, id in a single VARCHAR column and make nullable column Reference table name in Key names – so if the column is not NULL, then value is an id.
    I'm not feel very good of it because then I face a problem of no type check, and for example floating point number could be written with , or . as decimal separator.

  2. Make 3 nullable columns instead of Value: String value, Number value, Id reference – if one has value, others should be NULL. Again I need Reference table name column to specify which table I reference.

I wonder if some best practices are there. Couldn't find them by myself.

Thank you in advance. We use PostgreSQL.

Best Answer

If this is not explicitly intended for some very flexible system, you better normalise this into (at least) 3NF tables with columns for each fact type (not data type). In a key/value architecture like this it is very hard to implement constraints (as you write yourself in thought 1)

If you are sure this key/value architecture is what you want (ask yourself twice) then: Use a separate table for each datatype. A key/value table for string values, a key/value table for integers, a key/reference table for references. At least type checking is possible and storage is efficient. Still no other constraints. Takes lots of SQL to get all the values for one key, but clean tables.

Related Question