Approaching the storage of objects in relational environments

I've been using MySQL for years now and love it's flexibility, scaleability, and general useability. However, I think I might be beginning to bump into a wall in cleanly and efficiently representing some data structures within merely a relational system. My hope is that someone on the list has had to deal with similar data and might be able to offer some insight into how to effectively and efficiently represent it within a relational, as compared to an object oriented DB structure so I don't have to start looking around for an alternative DB option.

I need to draft a mechanism for storing _user defined_ Objects (users define characteristics, components of that object, events that occur to those objects etc.). So, a very simplified example might be that someone is defining what cut up pieces of sandwiches are and how they came about from their original Raw Materials (RM).

RM1 --

      \
RM2 ----> ObjectTop

      /             \
RM3 --               \
                      \                       --> ObjectWidget1
                       \                     /


                        ----> ObjectSandwich -->  ObjectWidget2


RM1 --                 /                     \
      \               /                       --> ObjectWidget3
RM4 ----> ObjectBottom
      /

Where, (not programmatically or by an arbitrary table) a user would define say:

ObjectTop as requiring RMS1 - 3, define it has having a name as <something> being of size <something> etc. etc. and so on in a similar fashion for ObjectBottom. The user might create an object ObjectSandwich which is composed of 1 ObjectBottom and 1 ObjectTop and have a characteristic of a name of <something> along with nth other characteristics. Finally, s/he might create multiple ObjectWidgets which descend from ObjectSandwich (think chopping up the sandwich into multiple pieces).

This would be a two step process obviously. The user would DEFINE a generic ObjectSandwich for example (think, creating a class in code), and then would create specific instances of that generic object (think, create instances of your class and constructing your specific characteristics of that instance).

The database would thus have no idea it was being told to store data about a sandwich, or, more specifically, pieces of that sandwich UNTIL the user DEFINES those "objects" for it.

Each level would have to inherit the characteristics, events (you might heat the sandwich while it's still a sandwich; you might not), composition etc. of it's parents (all the way up the chain). So, you should be able to ask ObjectWidget what kind of and which specifically ObjectTop he had. However, I don't have the option of having a "Bottoms" table, and a "Sandwiches" table or a "Widgets" table since I won't know as a programmer what objects might be created by users. There would just have to be tables describing the compositions of any specified objects, the values of those specifications for each particular instance of that object (think creating instances of classes in an OO language).

To add further complexity to the project I need to be able to reference external data into specific objects. For example, I might want to take pictures of an ObjectTop and associate that picture with that specific ObjectTop. I may want add multiple notes (not just a 1:1 relationship) about specific ObjectSandwiches and so on.

This seems fairly intuitive within an OO model, but, it's more difficult to visualize how a strictly relational model would effectively and efficiently handle it (particularly traversing up inheritances defined by users without overrunning your database).

Any ideas? Received on Fri Mar 15 2002 - 20:23:15 CET