--- Ian Sillitoe <ian.sillitoe@xxxxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
