Unknown foreign key [UFK]

We prefer to minimize NULL values for foreign keys, because :

  • Allowing NULL values makes queries more complex. We don’t want SQL like this where user_id in ( select user_id from X where user_id is not null)
  • We don’t want null values in report comboboxes. E.g. user:  A, B, NULL
  • In SSIS we dislike the following code: not User_Id is null && User_id = 32
  • We prefer INNER JOIN over LEFT JOIN because it’s faster. When a foreign key can be null we have to use left join because else we would be filtering.

Solution

We replace NULL values by an Unknown value. E.g. when a user is unknown we set the user_id to -1 which points to a user with the name unknown. This way we can always inner join and show unknown values in report.

Notes

NULL values are useful for facts, e.g. download_speed. When download speed is NULL it means that there is no download speed defined (e.g. for sales propositions that do not contain internet). This is different than having a download speed of 0.