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.
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.
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.