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

[kehoea.livejournal.com]

I've no idea what database software you use, but this should apply to most of them:

One table for the boxes; one entry in that table per box. One table for the compartments; one entry in that table per compartment. One table for the objects; one entry in that table per object.

One table for linking compartments to boxes; one entry in that table per compartment that appears in any box, that entry storing both the compartment ID and the box ID. One table for linking objects to compartments; one entry in that table per object that appears in any compartment, that entry storing both the object ID and the compartment ID.

For the constraints, you would need to set up container types and object types: one table for container types, one entry in that table for every type that exists (that subset of containers that behaves identically with regard to size of contained objects and number of them); a container_type_id field in your containers specifying what container type a given container is. One table describing the known object types; one entry in that table for each set of objects that behaves differently when it comes to putting them into containers. One table linking object types to container types, either defaulting to allow and specifiying deny when necessary, or vice-versa.

For allowing compartments as objects, you would need to unify the object and compartment tables, and, in your programming logic, make sure you're not putting a compartment inside an object that isn't a compartment and so on.

Database design was one of the most successfully taught classes (without stellar professors) I've ever seen, btw—it is an eminently learnable subject. Sit down some afternoon and read Philip Greenspun through, it'll make your life easier and clearer.

[ragnvaeig.livejournal.com]

I'm not sure I could explain how to do it in such abstract terms, but I know I could make it work with relative ease in a relational database.

[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.