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

datapanix_at_gmail.com wrote:
>

Here, widgets is a well-defined entity:

> widgets:
>
> id widget
> ----------------------
> 1 sprocket
> 2 cog
> 3 hammer
> 4 thingamajig

Here, qualifiers is a weakly-defined conflation of attributes:

> qualifiers:
>
> id qualifier
> ----------------------
> 1 red
> 2 blue
> 3 green
> 4 smooth
> 5 crunchy
> 6 sharp

You have, in essence, taken columns and made them into rows. Redness is not the same kind of thing as smoothness.

Instead, why not have widgets have a color attribute, that is one of red, blue, green, and a texture attribute, that is one of smooth, crunchy, sharp. (Note that this also solves the (red, green) problem.)

> 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.

If widgets have a color and a texture, you can simply use the id of the widget that has the attributes widget=cog, color=red, texture=crunchy.

> 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.

There is no substitute for actually doing the work of modelling. If you want the benefits, you have to pay the cost.

Marshall Received on Sun Oct 30 2005 - 07:51:16 CET