Re: So what's null then if it's not nothing?

From: Julian M <>
Date: 22 Nov 2005 03:40:39 -0800
Message-ID: <>

Hi Hugo

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

Implementation results:
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

Original text of this message