Re: Modelling objects with variable number of properties in an RDBMS
From: paul c <toledobythesea_at_oohay.ac>
Date: Sun, 30 Oct 2005 01:56:04 GMT
Message-ID: <UKV8f.340968$1i.68459_at_pd7tw2no>
>>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!
>>
Date: Sun, 30 Oct 2005 01:56:04 GMT
Message-ID: <UKV8f.340968$1i.68459_at_pd7tw2no>
I don't get what models and model_id stand for. isn't datapanix, whatever his name, asking how to model all subsets of the properties, ie. 2 raised to the power which is equal to the number of properties? (except, maybe, for the empty subset)
paul c.
Bruce Rusk wrote:
> 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 > etc. > > > <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 - 02:56:04 CET