need some DB design coaching

[Tiemo Hollmann TB]

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

 

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