Database Design Question

[gothwalk]

Originally published at Now Is A Long Time Too. You can comment here or there.

I’m poking at a project concept here. It might grow into a massive, world-ruling empire, or it might disappear when I find it too complex. However, it needs a database. Without giving away any specifics, it deals with boxes and objects. There is a single main box, which has some method of distinguishing it from other main boxes - probably an ID number, that being nice and simple. It has a number of compartments within it, which may vary. Some of these compartments can hold more than object, others can only hold one. Some compartments have limits as to what types of objects they can contain. Some objects are containers themselves, and can hold other objects, generally more than one. Some objects are too large to fit in particular containers.

Optional extras for simplicity: Containers cannot hold containers, so the deepest you can go is main-compartment-container-object.  The size limit will only be applied to whether a given object can fit in a given container, not how many other objects are in there.
Can someone who is a little more knowledgable than me in the ways of database design show me how to set up the tables for this?

Comments

[kshandr.livejournal.com]

I'd do this with one table, called "object". The object table has a column for Unique ID, Object Type, Capacity, Parent (and possibly Mass and Volume. See below.)

Within this table, each "object" (because we'll support types of object other than boxes and compartments - you allude to this in your spec) has an object type: Box or "Other" (you can increase the "other"s later.)

An object within a box is implemented as the "Parent" of a box being that object. Compartments within a box that can store more than one object are arranged with the parent of the compartment being a box, then any objects within the compartment are set to have the parent of the compartment. You will need to use program logic to ensure that neither a box nor a compartment can be filled above their "capacity."

Limits on what sort of objects a box/compartment can contain should be set up in a seperate column on "object" and program logic used to ensure those criteria are adhered to. You can also implement your "size" in this way - Perhaps an object has a "mass", and containers/boxes have a "volume" which you can then interrogate from your code.

Optional extras: Using the above you can have an infinite amount of objects within your parent/child tree. If you want to. Using the mass/volume setup you can easily control what goes within each container.

For programming ease, you're going to want some recursive routines which will interrogate this data structure - It'll make your life easier. So I'd have a "box contents" procedure which the IDs of everything in the box, or "-1" if the item queried is an object. Using that procedure you can derive a "box mass" procedure which will return the total mass of everything in the box.

Obviously I've no idea what you're at, but it sounds very familiar to something I've worked quite extensively on. Let me know if you need owt else - kshandr (splat) gmail (dot) com.