Modelling objects with variable number of properties in an RDBMS

1      sprocket
2      cog
3      hammer
4      thingamajig

qualifiers:

id qualifier

1      red
2      blue
3      green
4      smooth
5      crunchy
6      sharp

widgets_qualifiers:

widget_id qualifier_id

1         1
2         3
2         5
3         2
3         6

Basically I have a many-to-many relationship between widgets and qualifiers, so that I can assign a number of different properties to an entry in the widget table, i.e. a smooth, blue hammer. (Let's ingore for the moment that with this setup, it's possible to have a widget be both red and blue which makes no sense)

I understand that so far. What I want now is the ability to assign a unique id to a widget posessing specific qualifiers. IOW, a way to reference a "green, smooth cog" as well as a "red, crunchy cog" uniquely. So in another table I can reference this unique identifier in a one-to-many relationship. (For example in an "open orders" table where we're waiting for one specific widget for the order to be complete. Not the most realistic example, but good enough for explanation purpose) This is where I get stuck.

Because of the possibility of variability of the number of qualifiers of any particular widget, I don't see how I can normalize this design. I think I'm on the right track with the many-to-many relationship, but like I said I've reached the upper limit of my conceptualization abilities on this.

any suggestions on how i can uniquely identify a widget with varying properties? or is this not even possible?

thanks! Received on Sat Oct 29 2005 - 20:22:42 CEST