[Helma-user] allowing NULL VALUES in foreign keys

[nex]

Julian Tree wrote:
> if I am building my system from scratch, why don't I just  
> start off with a system that doesn't allow null values in any  
> columns?
because it's bad design. currently your database says that every node 
has a parent node, which is basically a lie. as long as you don't have a 
benchmark that proves such a kludge to actually achieve something 
worthwhile, i would advise against it.

anyway, if you're going to do it this way, you need a node that is the 
parent of all nodes that don't have a parent -- i.e. also of itself. 
(confused yet?) keep in mind that you don't _have_ to do this on the 
database level, you can also do it in your application logic, i.e. 
actually create this dummy node that is the default parent. that way 
there's no 'lie' and no magic number in the database any longer, i.e. 
there's one less thing to worry about ^_^

> Now I know there is the theoretical  
> different between NULL (unknown value) and (blank a known but blank  
> value).  Maybe someone can give me an example
NULL usually does not mean 'unknown'. your own use case is an example 
for this: the parent isn't unknown, it's known to not exist. now imagine 
the type of the foreign key was a number, so you can't have a blank 
value. you can either have a number, which looks like a valid foreign 
key that actually references something (and if it doesn't really 
reference something, you either made a mistake or are using some kind of 
hack, like magic values), OR you can have NULL. "NOT NULL", in this 
context, means that this foreign key must indeed always reference 
something. with a child-parent relation that's obviously not the case, 
unless every child really has a parent.

and as nighthawk said, because you're solving that frequently used 
pattern in a standardised way, this enables the database to provide you 
with nifty additional features.

N.B. this rant is just to let you see why many people think having NULL 
values is an important feature. if it doesn't convince you personally, 
that's OK with me :-)

~nex