[thelist] recursive database design

[Dusty McDonald]

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

I have a ~110 table natural history database running in Oracle (public interface available at http://hispida.museum.uaf.edu:8080/home.cfm). 

I haven't been able to come up with a satisfactory model to track objects (primarily specimen parts - ie, a skull or a skin). I have a table Container that has a recursive join to itself, and works rather well - unlimited layers, and I've written some pretty nice interfaces for it. The table looks something like

container_id
parent_container_id
stuff_about_the_container

This works great for tracking containers (which may be anything - our ultimate parent container is presently "The Universe", typical middle layers are something like range-->case-->tray--->box). The problem comes from trying to put collection items (parts) in a container. My current solution is table coll_obj_cont_hist, 

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.

Anyone have a better solution? I'm open to just about anything, but I would like to preserve the recursive model if I can.

Thanks --Dusty