*********** 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 !