Re: Modelling objects with variable number of properties in an RDBMS

It sounds like what you really doing should be doing is using the widgets table as a list of properties -- specifically types -- for another table, which would list individual "models" of widgets and have a "type" column with ids from your "widgets" table

models:

id    model                            type
----------------------------------------
1     superhammer               3
2     bluehammer                  3
3     crunchycog                    2
4     electricsprocket            1

then in a second table relate model ids to qualifiers

model_id qualifier_id

1                      3
1                      7
2                     2
2                      6

<datapanix_at_gmail.com> wrote in message news:1130610162.744817.239070_at_g14g2000cwa.googlegroups.com...
> i've apparently hit a brick wall in my understanding of table
> relationships. Say I have the following:
>
> widgets:
>
> id widget
> ----------------------
> 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 Sun Oct 30 2005 - 01:34:23 CEST