At the risk of repeating myself, no, in my opinion its not automatically OK to present this as a
jolly spiffing idea
I consider that it is OK to do this once all the other possibilities have been examined.
In your example I guess you could just have used a steel grade of 'Unknown' and added it to the lookup
table - i.e. a key/value pair, no more NULLs.
This has the added advantage of being able to distinguish WHY you don't know the value, for
example what happens if the steel grade is incorrectly assessed ?
Then the NULL can be replaced with a more useful set of descriptions:
'Not yet known - awaiting cooling'
'Not yet known - incorrectly graded' etc
Yes there are many examples in real life, unfortunately they seem to be frequently modelled incorrectly.
Here is a list of cons and examples I have come up with just in the last 15 minutes:
a) dealing with how other users handle result sets that can contain a FK NULL, e.g. developers, as a rule I
try to make things as uncomplicated as possible so if they have a list of descriptors that they pick up from
a related lookup table and they find a NULL value what do they display in a drop list pick list - PANIC STATIONS!
F->Female
M->Male
NULL->???
or
F->Female
M->Male
U->Unknown
So, I give them a 'None' or 'Unknown' and they feel that the world is still a place they understand.
b) middle-ware design tools that expect a column to hold a value that always exists in a related table - this
can be a bit of a pain
c) concurrency issues - OK I'm this is more to do with the database design itself rather than any issue with
NULLs in FKs so its a bit of a stretch, still if you imagine two NULLable FKs and replace them with relations
you won't have any locking or concurrency issues that you would get if you had two users trying to update
different NULL FK columns on the same row
d) tricky reporting - just as long as you accept the fact that in the case of 'lookups' you have to always
remember to left join a nullable FK column if you want to see all the members of a set irrespective of what
their related values are, e.g. show all doctors and their sex doctors in one table, sex descriptors in another:
Using a right join you get:
Fred -> Male
Joe -> Male
Eileeen -> Female
Chris -> NULL (doesn't show because no related row in sex table)
This becomes jolly good fun when you have 5 or 6 NULLable FKs, like to see the performance hit on my
database with a query containing 6 left joins anyone ?
Pros include:
a) ease of reporting - don't have to worry about including lots of tables
b) ease of initial modelling
I'm sure if you think about it you can come up with some that apply in your case.
OK this thread is now probably WAY out of what the forum really allows so this is the last post from
me on the subject.
See you in the other forums my friends, I'm off to bed.