Re: (FAQ?) JOIN condition - 'WHERE NULL = NULL'

--- Ian Sillitoe <ian(dot)sillitoe(at)googlemail(dot)com> wrote:

> I completely take your points - so maybe I should be
> asking for advice on
> database design instead.
>
> We are annotating nodes on a hierarchical structure
> where NULL implied an

I don't mean to be rude, but yuck. Why provide a
record for data that isn't there?

I recently put together a database (at present at a
very early prototype stage) to handle biological data.
Considering ONLY the taxonomic portion of it, I opted
for a general hierarchical model. Maybe not the most
efficient, yet, but no waste, yet.

In what is an over simplification, I created a taxon
table, with columns for a unique ID number, taxonomic
level (species, genus, &c. with all the glorious
subcategories taxonomists of varius tripes are wont to
create/define). The taxonomic levels are predefined
(taken from my references that deal with such
matters), in a lookup table. Then, I have columns to
hold parent taxon ID number.

Of course, there is, in a middle layer, constraints
that prevents recording a species as a parent of a
genus, and other silliness (no linking a species
epithet directly to a class or order). But you get
the idea.

An object oriented programming metaphore might be that
of a singly linked list. And of course, I have
deliberately obfuscated the complexity arising from
having to handle synonyms both usefully and
gracefully, but the core idea is simple, and there are
no nulls, except for taxa representing a whole
kingdom. Last I checked, there were no taxa more
general than the kingdom, and there's only a handful
of kingdoms. If you don't have data on subclass or
superfamily or subspecies, you just don't put it in.
Therefore no nulls!

I have no idea if this model would work for you, but
maybe it will help.

Cheers,

Ted