Essentially, I want the user to be able to define a hierarchical model, but then I need to allow the user to store data within their defined model. Does this make sense? So the users will be able to create new "unit types" to be organised in a hierarchical way and decide how units of these types are allowed to be organised. A simple example: in my hypothetical interface a user creates three unit types, trunk, branch and leaf. The user then defines the relationships between them. A leaf can exist at any point in the hierarchy, a branch must have a trunk as a parent. The user can then creates instances of these unit types (as units) and can organise them according the rules defined in their model... is there a good way of doing this in the database?
This is an extremely broad question, but this may point you in the right direction. Note that you're only going to be able to store the relationship rules in the database. Enforcing them will be up to your client code. Try this on for size..
unit:
unit id,
name,
unit relationship:
unit id,
foreign unit id
You can then use your unit relationship table in the following way..
unit id relates to the unit it's describing.
foreign unit id should be nullable.
A unit with no relationship records can only exist at the root of the heirarchy. A unit with a null foreign unit id can have any other unit as its parent. Otherwise, a unit must have another unit as its parent, and it's type must be one of those defined in its relationship records.
As for storing the instances themselves, that should be straightforward..
instance:
instance id,
unit id,
parent instance_id
I'm sure there would be other fields you'd need (name, for instance), but I assume you get the drift.