Suppose a have a structure of two tables
|_Key name_|_Parent_key_id_| | key1 | NULL | | key2 | key1 |
|_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
Store string, number, id in a single VARCHAR column and make nullable column
Reference table namein
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
.as decimal separator.
Make 3 nullable columns instead of
Id reference- if one has value, others should be
NULL. Again I need
Reference table namecolumn 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.