[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
>container

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
>tier.

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
>if
>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. 
>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:

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.

Thanks!

--Dusty
>
>http://www.evolt.org/article/Four/17/4047/index.html
>
>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.
>http://www.waetech.com/service_areas/GA/
>706.210.0168
>
>-- 
>* * Please support the community that supports you.  * *
>http://evolt.org/help_support_evolt/
>
>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