Can I have a column in my values table (value) referenced as a foreign key to knownValues table, and let it be NULL whenever needed, like in the example:
Table: values
product type value freevalue
0 1 NULL 100
1 2 NULL 25
3 3 1 NULL
Table: types
id name prefix
0 length cm
1 weight kg
2 fruit NULL
Table: knownValues
id Type name
0 2 banana
Note: The types in the table values
& knownValues
are of course referenced into the types
table.
NULLs in foreign keys are perfectly acceptable. Dealing with NULLs in foreign keys is tricky but that does not mean that you change such columns to NOT NULL and insert dummy ("N/A", "Unknown", "No Value" etc) records in your reference tables.
Using NULLs in foreign keys often requires you to use LEFT/RIGHT JOIN instead of INNER JOIN.