[thelist] recursive database design

Joshua Olson joshua at waetech.com
Tue Dec 23 14:37:00 CST 2003

----- Original Message ----- 
From: "Dusty McDonald" <fndlm at uaf.edu>
Sent: Tuesday, December 23, 2003 3:27 PM

> I hope this is an appropriate forum for my question. If not, I aplogize
> in advance.

This forum is perfect for this sort of question.

> container_id (f-key to Container)
> collection_object_id (f-key to coll_object, the table that (more-or-
> less) describes an object)
> installed_date

> which lets me pretend like a collection object is a container. This is a
> bit clunky - every time I make a part, I also have to make a coll_obj_cont
> and a container for that part; I'm creating and storing useless data.


>From an OLTP angle, this structure is quite intelligent in it's
implementation.  It's flexible, extensible, and provides a natural audit

The only simplification would be if objects can only be in exactly one
container and you don't want to maintain a history.  This model allows for
objects to be in multiple containers.  If an object can only be in one, or
you don't want to keep a running history, then move the fields from the
coll_obj_cont_hist table into the object table itself and skip this middle

Also, you may look at adding removal_date and installed_by and removed_by if
you want to track changes with even more accuracy.

Just considering the hierarchical containers for a moment, there are a
couple other implementations of the database that you may find helpful.  The
database structure you've selected is quite efficient at adding new values
to the table.  If the collection of containers doesn't change very much,
then there are some representations will greatly simplify the SELECTion of
the data while adding only a slight bit of overhead to the INSERTion of new
elements.  Contact me offlist if you need more info.  Kirby Fling covered
some of these in an article at evolt:


All things considered, this structure is a good one and I'd recommend
sticking with it.  Create some decent handler classes in the middle tier and
you should have a scalable and maintainable solution.

Good luck.

Joshua Olson
Web Application Engineer
WAE Tech Inc.

More information about the thelist mailing list