[Helma-user] allowing NULL VALUES in foreign keys

[Julian Tree]

>> 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 ^_^


Thanks for the the dummy node suggestion. I think that is what I am  
looking for.   I am aware the by assigned a forgein key value of zero  
I am actually telling the database that it belongs to a parent with  
id of 0, but I am ok with that.  I rather everything has a value.   
Thanks for the


select count(field1), count(field2), count(field3) from table;
and it will count all non-null fields in each column. So if you have
10 rows in your table, the result set will not look like 10 | 10 | 10
but rather  9 | 5 | 8 or something like that. I don't do this on a
daily basis either, but I'm pretty sure there is more examples.

That is interesting. I guess I never ran into situation where I need  
to do this type of operation. Or I have being using

SELECT COUNT(CASE WHEN field1 > 0 THEN 1 ELSE 0 END), but I can see  
how having null can be useful in the instant. Thanks for pointing out. 
  
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://helma.org/pipermail/helma-user/attachments/20060821/3c1e9a04/attachment-0001.html