<html><head><base href="x-msg://69/"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; ">Hi Tiemo,<div><br></div><div>very limited time now but you should normalise your design</div><div><br></div><div>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</div><div>With this design you can have option_name = 'color' , option_value = 'red', objptr = record x</div><div><br></div><div>hth (have to go now)</div><div><br></div><div>If not clear, ask I'll look at your mail (if necessary) later</div><div><br></div><div><div>
<span class="Apple-style-span" style="border-collapse: separate; color: rgb(0, 0, 0); font-family: Helvetica; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-align: auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; font-size: medium; ">Bart Pietercil<br>CTO Cognosis NV</span>
</div>
<br><div><div>On 03 Mar 2011, at 12:24, Tiemo Hollmann TB wrote:</div><br class="Apple-interchange-newline"><blockquote type="cite"><span class="Apple-style-span" style="border-collapse: separate; font-family: Helvetica; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; font-size: medium; "><div lang="DE" link="blue" vlink="purple"><div class="WordSection1" style="page: WordSection1; "><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 11pt; font-family: Calibri, sans-serif; "><span lang="EN-US">Hello,<o:p></o:p></span></div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 11pt; font-family: Calibri, sans-serif; "><span lang="EN-US">with my few SQL experience I am not sure, if my approach is correct.<o:p></o:p></span></div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 11pt; font-family: Calibri, sans-serif; "><span lang="EN-US">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.<o:p></o:p></span></div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 11pt; font-family: Calibri, sans-serif; "><span lang="EN-US">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<o:p></o:p></span></div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 11pt; font-family: Calibri, sans-serif; "><span lang="EN-US">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):<o:p></o:p></span></div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 11pt; font-family: Calibri, sans-serif; "><span lang="EN-US">SELECT * FROM table WHERE<o:p></o:p></span></div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 11pt; font-family: Calibri, sans-serif; "><span lang="EN-US">fldShape = ShapeValue AND<o:p></o:p></span></div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 11pt; font-family: Calibri, sans-serif; "><span lang="EN-US">fldSize = SizeValue AND<o:p></o:p></span></div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 11pt; font-family: Calibri, sans-serif; "><span lang="EN-US">fldColor1 = ColorValue<o:p></o:p></span></div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 11pt; font-family: Calibri, sans-serif; "><span lang="EN-US">OR<o:p></o:p></span></div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 11pt; font-family: Calibri, sans-serif; "><span lang="EN-US">fldShape = ShapeValue AND<o:p></o:p></span></div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 11pt; font-family: Calibri, sans-serif; "><span lang="EN-US">fldSize = SizeValue AND<o:p></o:p></span></div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 11pt; font-family: Calibri, sans-serif; "><span lang="EN-US">fldColor2 = ColorValue<o:p></o:p></span></div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 11pt; font-family: Calibri, sans-serif; "><span lang="EN-US">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?<o:p></o:p></span></div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 11pt; font-family: Calibri, sans-serif; "><span lang="EN-US">Thanks for any recommendations<o:p></o:p></span></div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 11pt; font-family: Calibri, sans-serif; "><span lang="EN-US">Tiemo<o:p></o:p></span></div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 11pt; font-family: Calibri, sans-serif; "><span lang="EN-US"><o:p> </o:p></span></div></div>_______________________________________________<br>Valentina mailing list<br><a href="mailto:Valentina@lists.macserve.net" style="color: blue; text-decoration: underline; ">Valentina@lists.macserve.net</a><br><a href="http://lists.macserve.net/mailman/listinfo/valentina" style="color: blue; text-decoration: underline; ">http://lists.macserve.net/mailman/listinfo/valentina</a><br></div></span></blockquote></div><br></div></body></html>