Re: So what's null then if it's not nothing?
Date: 22 Nov 2005 03:40:39 -0800
Good point on the fact that it is a special value. Perhaps
using/calling it NULL was not such a good idea. The problem being that
null has so many other meanings and uses that people tend
(unconsciously) to expect NULL in SQL to work the same way as whatever
they are used to... Unfortunately in SQL it works differently. I have the same problem with null in other languages as I tend to expect it to work like SQL NULL
Very interesting that Pick can efectively "remove a fieldd from a row"
and keep the rest of the row. The question is how would we do this in a
SQL RDBMS two ways I can think of (I am sure there are others)
1 - Put the optional field in another table, with a 1:1 realationship
(this is usually mentioned in normalisation books/courses)
2 - Create a subtype of the original table without the field
1- This solves the problem if you put the FK on optional table and point back to the original table (however if you put the FK on the original table and point to the optional table you will need an optional/nullable FK)
2- You create a parent/root entity/table and make a subtype for each optional field
Both of these have the problem that you end up with the original table
and an extra table for each optional/nullable field. It is usually
easier to allow the null values
Modelling Note: Often you can come up with a sensible parent/root object with exclusive subtypes, this will eliminate nulls (as the nulls in the combined table were previously indicating that no value will ever be known)
This indicates that the relational model is not the ideal way to
implement business objects (however it is a mature useful tool). If we
look closely at set theory and try to model in terms of sets membership
(indicating meaning/sense) and tuples linking objects (members and sets
are both objects) - with tuples also indicating sense/meaning. We begin to see that the relational model does not work directly in this way, hence some of the odd structures we need at times.
Nulls often indicate the need to model time (eg value of order delivery date before order is sent) but that is another topic.
Julian 8^) Received on Tue Nov 22 2005 - 12:40:39 CET