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

Hi, only just arrived at the group and still have an immense amount to learn theory wise, but I've already had very useful responses to my questions and hopefully I can return some of that. The first thing I've realised is to take a step back and consider what I'm modeling. In your case I think you want to describe individual instances of a widget say, and so you need some value to reference it. If there is no suitable label available to you, then utilise a surrogate key to represent the item. Next focus on the initial predicate - well all the items are going to be a certain type of widget so that seems a good place to start.

"There is a widget with widget_id (w) that is of widget_type (t)" seems an appropriate predicate, so that can make up the first relation. From then on the rest of your analysis seems fine, and so I think the result would be something like:

widgets:

1         | sprocket
2         | sprocket
3         | cog
4         | cog
5         | hammer
6         | thingamajig


------------------------

qualifiers:

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


------------------------

widgets_qualifiers:

1      | 1
2      | 3
2      | 5
3      | 2
3      | 6


------------------------

Then by referencing widget_id 2 say, and making a coupla joins you could return the information that it is a "green crunchy sprocket", but widget_id 1 is a "red sprocket". Hope this is the sort of thing that you were aiming for.

All best, Jason. Received on Sun Oct 30 2005 - 04:04:11 CET