AW: need some DB design coaching

[Tiemo Hollmann TB]

Hi Bart,

yes I understand. My hope was, that there is a workaround without
normalizing.

Thank you

Tiemo

 

Von: valentina-bounces at lists.macserve.net
[mailto:valentina-bounces at lists.macserve.net] Im Auftrag von Bart Pietercil
Gesendet: Donnerstag, 3. März 2011 12:37
An: Valentina Developers
Betreff: Re: need some DB design coaching

 

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/4d286bc6/attachment-0001.htm>