DB Systems/Archite

So one of the things I do is design data-systems. In particular, I have been working on a set of custom content-management tools that both I and Digitopolis can resell. Because I wanted to make this system scalable and universal, I’ve tried to abstract all the systems in it. To this end, every object in the system has a unique ID. And object could a table, a row in a table, or a field (column). I keep track of relationships in a special index. However, this didn’t suffice for some cases, and so I had to create a new way of noting relationships, which I have called normalization tables (as they are all used in data normalization). This has proven useful in one particular case from which I am hoping to derive a universal law. The case is thus:

A concert can have both performers and composers listed. This handled with a link to the person object from the concert object. A person can be both a performer and a composer. This is handled with a link to the person_type object (a person has an attribute of person_type). When linking to the person object from the concert object, I need to know what ‘type’ of person I am linking to. This is currently handled by having both a norm_composer and a norm_performer index table. This seems both inelegant and inefficient to me. Ideally, I should be able to handle all relationships through my relationships index. So what I need is a way to describe the relationship in the index, in a universal manner. If I knew that how an object related was always based on a particular attribute of one of the objects, then I could conceivably add a column that described the attribute that the relationship was based on (like person_type = performer). However, I can’t guarantee that it would always be based on the attributes of an object.

I guess what I am wanting is a way to describe the link between objects, and in a more complicated fashion than one-to-one, one-to-many, etc. Perhaps to be able to treat the link between objects as an object also, that would have a particular interface, rules, methods, etc…

If anyone has ANY ideas on how to accomplish such a thing while working in the constraints of a relational database system, please feel free to suggest them. Because I’m stumped.

I’m also working on a bit-validation security model, but I think I’ve got a handle on that one. However, if anyone has any links to great resources on those sorts of security models, please send them on to me, as I could always use more resources.