[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