Containers redux

[lexiclevenger]

From Nate Shoobs on the Speciforum:

We desperately need a way to link preparations many-to-one AND many-to-many to Containers which can be linked to the storage tree.

Use Case:

We have a storage type where there are multiple catalogued lots in a single larger container. For example, lots in a single cabinet drawer, or lots in an oversize box on a shelf.
We also have a storage type where multiple containers house the specimens of a single preparation of a catalogued lot. For example, a lot of 100 shells that occupies multiple boxes because there are so many.
Both of these types have an added complication, which is that some preparation types are “paired” logically and are always stored together in the same container. So in those cases, one or more containers contain many preparations of a catalogued lot, but not all of the preparations from that lot. ex. Dry shell pairs and Dry shell singles are separate preparation types in our DB because of Specify’s baked-in limitations in tracking individual organisms. Those preparations always have the same storage info and will always be in the same container.
We need to be able to track the number and location of these containers separately from preparations or COs.
We also need to be able to print labels for these containers from within the database.

One could technically make every single container a node in the storage tree, but this would be extremely difficult and impractical to manage.

We cannot use COG for this, as the relationship of the container is not modeled correctly (at least, I don’t think it can be), because there is no “master” or “primary” object, and the fundamental relationship is between one or more preparations and one or more containers, which are not catalogued objects and should be entirely independent of the CO table.
Furthermore we need to reserve COGs for a different kind of relationship.

Is there any way to accomplish this in Specify 7 at present? If not, is there a potential for this kind of relationship in the near future with the update focusing on Schema changes?

This is functionality we really desperately need. Not having it blocks a ton of our storage data from being imported into Specify.

Currently, the closest functionality we have is the Storage tree, which does not fulfill the need for a many-to-many relationship.

[nfshoobs]

@lexiclevenger This issue is related to the other ones here and here, under the broad topic of "storage tree improvements that will require schema changes".