[thelist] recursive database design

Dusty McDonald fndlm at uaf.edu
Tue Dec 23 15:19:49 CST 2003

*********** REPLY SEPARATOR  ***********

On 12/23/2003 at 3:37 PM Joshua Olson wrote:

>The only simplification would be if objects can only be in exactly one

True, at least they can only be in only one container at one time. Of course, that container is in a container which is in a container which...

 and you don't want to maintain a history.

I do, primarily as a safety net - if container A isn't where the DB thinks it is, then it's last location is a good place to start looking.

I don't think this is relavant, but I also have a table fluid_container_history that tracks wet container contents - ie, ethanol concentration and last checked date. Eventually, this will likely expand to include things like thermal history - ie, a datalogger on an ultracold freezer recording the temperature every n seconds.

  This model allows for
>objects to be in multiple containers. 

True, although I'd never thought of that before. I think I consider that a bad thing!

 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

1) I do want the history, and 2) many of my objects are not trackable (ie, "cataloged items" are the virtual whole specimen).

I gather you think the current model is appropriate - ie, there is nothing wrong with pretending that objects are containers? That's always seemed a bit hinky to me, but I can't figure out why - hence this question. 

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

Removal_date may be inferred from install_date, assuming we'll only move things around by scanning them into new containers. I do track whodunit in another table.

>Just considering the hierarchical containers for a moment, there are a
>couple other implementations of the database that you may find helpful. 
>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:

Our collection of containers does change (grow) quite a bit, and our collection of trackable objects grows even faster. However, most inserts are a few dozen to a few hundred at a time - I'm not overly concerned about database efficiency (yet!). I'm more concerned about SELECTs; I run lots of them to find part locations.

I greatly appreciate your response. I'm a biologist stuck in a programmer job, and sometimes I get in way over my head! If I have dug myself into a hole with this model, I need to start digging my way out NOW! If that isn't the case, I can certainly use the reassurance.


>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
>you should have a scalable and maintainable solution.
>Good luck.
>Joshua Olson
>Web Application Engineer
>WAE Tech Inc.
>* * Please support the community that supports you.  * *
>For unsubscribe and other options, including the Tip Harvester 
>and archives of thelist go to: http://lists.evolt.org 
>Workers of the Web, evolt !

More information about the thelist mailing list