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
Store string, number, id in a single VARCHAR column and make nullable column
Reference table name
inKey names
- so if the column is notNULL
, 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.Make 3 nullable columns instead of
Value
:String value
,Number value
,Id reference
- if one has value, others should beNULL
. Again I needReference 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.