need some DB design coaching

[Bart Pietercil]

Hi Tiemo,

very limited time now but you should normalise your design

record x has MANY (unlimited) options so options should be an extra table with 3 fields: option_name & option_value + objectpointer to table where record x is to be found
With this design you can have option_name = 'color' , option_value = 'red', objptr = record x

hth (have to go now)

If not clear, ask I'll look at your mail (if necessary) later

Bart Pietercil
CTO Cognosis NV

On 03 Mar 2011, at 12:24, Tiemo Hollmann TB wrote:

> Hello,
> with my few SQL experience I am not sure, if my approach is correct.
> I am having a table, where I want to add 8 options (columns). With these options I want to classify each record and want to let the user search for  the wanted record with theses options.
> Lets say I have the option “shape”, “size” and “color”. Usually each record can be classified distinct, e.g. with shape= round, size= big, color=red
> Now I have records where the classification color could be red OR blue and the same record should be found, if the user searches either for red OR for blue. My approach is because an option can only have one value in the DB, I create two options (columns): color1 and color2, so that I can assign to color1: red and to color2: blue. Now I could set up my sql (simplified):
> SELECT * FROM table WHERE
> fldShape = ShapeValue AND
> fldSize = SizeValue AND
> fldColor1 = ColorValue
> OR
> fldShape = ShapeValue AND
> fldSize = SizeValue AND
> fldColor2 = ColorValue
> because I have actually 8 Options and more than one can have alternative classifying, I get pretty big WHERE clauses because of the OR parts. Is this the only and state of the art approach, or are there more sophisticated ways to solve that?
> Thanks for any recommendations
> Tiemo
>  
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macserve.net/pipermail/valentina/attachments/20110303/55b63f84/attachment.htm>