Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> comp.databases.theory -> Re: So what's null then if it's not nothing?

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

From: Hugo Kornelis <hugo_at_pe_NO_rFact.in_SPAM_fo>
Date: Wed, 23 Nov 2005 01:30:52 +0100
Message-ID: <>

On 22 Nov 2005 03:40:39 -0800, Julian M wrote:

>Very interesting that Pick can efectively "remove a fieldd from a row"
>and keep the rest of the row.

Hi Julian,

Mind you, I don't *KNOW* it can, since I know nothing about Pick. But the description of Pick's data structure that DonR wrote (message-ID <>) reminds me very strongly of XML. And in XML, it's easy to simply remove a "field" from a "row": in "<Person><Name>Hugo Kornelis</Name></Person>", there are no tags for birthdate, which clearly indicates that the value for my birthdate is absent in this piece of data.

> 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)

Yeah - but you'll often want to combine data from various columns; if this design has scattered these columns over various tables, you'll have to join them together. Inner joins would lose you your relevant data, so you'll have to use outer joins -- and that means that the nulls you worked so hard to get rid of will be looking you straight in the face again.

>2- You create a parent/root entity/table and make a subtype for each
>optional field

And that would lead to an exponential explosion of tables. With one optional attribute, you get two subtypes. Two optional attributes means four subtypes. With 10 optional attributes, you'd need no less than 1,024 tables. And for a simple aggregation, you'd have to UNION half of them together.

It might not be an argument for a theory group, but the performance of such a monster would be awful.

>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)

Also agreed - if optional attributes are a result of a conceptual subtype, the subtype should be modelled. But if an attribute has to be optional because some information about the entity might in some cases not be present, then there's not enough reason to use subtypes - an optional column suits that need.

The distinction is quite easy to make. Just check what would determine in which subtable an entity should go. If data being present or absent for an optional attribute is the only thing, use one table; if the proper table can be determined by looking at another atttribute, use subtables.

Best, Hugo


(Remove _NO_ and _SPAM_ to get my e-mail address)
Received on Tue Nov 22 2005 - 18:30:52 CST

Original text of this message