<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>Of course you can work without normalisation, but this implicates that your application will be less portable and you will loose speed.</div><div>This aside nothing prevents you from define colors as a column (as you did) and store different colors inside the column through some string manipulation.</div><div>ie inside column colors you could store values like #red#green#blue. BUT you will not be able to retrieve the values by indexed searches as you need to work with the like %% operators. Depending on the number of records this can be a blessing (when having few records loadtime index + search time > sequential scan of table) or no problem (speed is acceptable probably few thousand records) or unworkable (unacceptable speed for retrieving). This of course is your responsibility as an application designer. But using locate and split commands (or equivalents) it is certainly doable without normalising. The main advantage of normalising here seems to be that your speed degradation over time (more records) will be much more acceptable.</div><div><br></div><div>hth,</div><div><br></div><div><br><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 13:31, 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" style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; "><div class="WordSection1" style="page: WordSection1; "><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125); ">Hi Bart,<o:p></o:p></span></div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125); ">yes I understand. My hope was, that there is a workaround without normalizing.<o:p></o:p></span></div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125); ">Thank you<o:p></o:p></span></div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125); ">Tiemo<o:p></o:p></span></div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125); "><o:p> </o:p></span></div><div style="border-top-style: none; border-right-style: none; border-bottom-style: none; border-width: initial; border-color: initial; border-left-style: solid; border-left-color: blue; border-left-width: 1.5pt; padding-top: 0cm; padding-right: 0cm; padding-bottom: 0cm; padding-left: 4pt; "><div><div style="border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; border-top-style: solid; border-top-color: rgb(181, 196, 223); border-top-width: 1pt; padding-top: 3pt; padding-right: 0cm; padding-bottom: 0cm; padding-left: 0cm; "><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 12pt; font-family: 'Times New Roman', serif; "><b><span style="font-size: 10pt; font-family: Tahoma, sans-serif; ">Von:</span></b><span style="font-size: 10pt; font-family: Tahoma, sans-serif; "><span class="Apple-converted-space"> </span><a href="mailto:valentina-bounces@lists.macserve.net" style="color: blue; text-decoration: underline; ">valentina-bounces@lists.macserve.net</a><span class="Apple-converted-space"> </span>[mailto:valentina-bounces@lists.macserve.net]<span class="Apple-converted-space"> </span><b>Im Auftrag von<span class="Apple-converted-space"> </span></b>Bart Pietercil<br><b>Gesendet:</b><span class="Apple-converted-space"> </span>Donnerstag, 3. März 2011 12:37<br><b>An:</b><span class="Apple-converted-space"> </span>Valentina Developers<br><b>Betreff:</b><span class="Apple-converted-space"> </span>Re: need some DB design coaching<o:p></o:p></span></div></div></div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 12pt; font-family: 'Times New Roman', serif; "><o:p> </o:p></div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 12pt; font-family: 'Times New Roman', serif; ">Hi Tiemo,<o:p></o:p></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 12pt; font-family: 'Times New Roman', serif; "><o:p> </o:p></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 12pt; font-family: 'Times New Roman', serif; ">very limited time now but you should normalise your design<o:p></o:p></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 12pt; font-family: 'Times New Roman', serif; "><o:p> </o:p></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 12pt; font-family: 'Times New Roman', serif; ">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<o:p></o:p></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 12pt; font-family: 'Times New Roman', serif; ">With this design you can have option_name = 'color' , option_value = 'red', objptr = record x<o:p></o:p></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 12pt; font-family: 'Times New Roman', serif; "><o:p> </o:p></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 12pt; font-family: 'Times New Roman', serif; ">hth (have to go now)<o:p></o:p></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 12pt; font-family: 'Times New Roman', serif; "><o:p> </o:p></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 12pt; font-family: 'Times New Roman', serif; ">If not clear, ask I'll look at your mail (if necessary) later<o:p></o:p></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 12pt; font-family: 'Times New Roman', serif; "><o:p> </o:p></div></div><div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 12pt; font-family: 'Times New Roman', serif; "><span class="apple-style-span"><span style="font-size: 13.5pt; font-family: Helvetica, sans-serif; color: black; ">Bart Pietercil</span></span><span style="font-size: 13.5pt; font-family: Helvetica, sans-serif; color: black; "><br><span class="apple-style-span">CTO Cognosis NV</span></span><o:p></o:p></div></div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 12pt; font-family: 'Times New Roman', serif; "><o:p> </o:p></div><div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 12pt; font-family: 'Times New Roman', serif; ">On 03 Mar 2011, at 12:24, Tiemo Hollmann TB wrote:<o:p></o:p></div></div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 12pt; font-family: 'Times New Roman', serif; "><br><br><o:p></o:p></div><div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; ">Hello,</span><span style="font-size: 11pt; font-family: Calibri, sans-serif; "><o:p></o:p></span></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; ">with my few SQL experience I am not sure, if my approach is correct.</span><span style="font-size: 11pt; font-family: Calibri, sans-serif; "><o:p></o:p></span></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; ">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.</span><span style="font-size: 11pt; font-family: Calibri, sans-serif; "><o:p></o:p></span></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; ">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</span><span style="font-size: 11pt; font-family: Calibri, sans-serif; "><o:p></o:p></span></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; ">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):</span><span style="font-size: 11pt; font-family: Calibri, sans-serif; "><o:p></o:p></span></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; ">SELECT * FROM table WHERE</span><span style="font-size: 11pt; font-family: Calibri, sans-serif; "><o:p></o:p></span></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; ">fldShape = ShapeValue AND</span><span style="font-size: 11pt; font-family: Calibri, sans-serif; "><o:p></o:p></span></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; ">fldSize = SizeValue AND</span><span style="font-size: 11pt; font-family: Calibri, sans-serif; "><o:p></o:p></span></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; ">fldColor1 = ColorValue</span><span style="font-size: 11pt; font-family: Calibri, sans-serif; "><o:p></o:p></span></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; ">OR</span><span style="font-size: 11pt; font-family: Calibri, sans-serif; "><o:p></o:p></span></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; ">fldShape = ShapeValue AND</span><span style="font-size: 11pt; font-family: Calibri, sans-serif; "><o:p></o:p></span></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; ">fldSize = SizeValue AND</span><span style="font-size: 11pt; font-family: Calibri, sans-serif; "><o:p></o:p></span></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; ">fldColor2 = ColorValue</span><span style="font-size: 11pt; font-family: Calibri, sans-serif; "><o:p></o:p></span></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; ">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?</span><span style="font-size: 11pt; font-family: Calibri, sans-serif; "><o:p></o:p></span></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; ">Thanks for any recommendations</span><span style="font-size: 11pt; font-family: Calibri, sans-serif; "><o:p></o:p></span></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; ">Tiemo</span><span style="font-size: 11pt; font-family: Calibri, sans-serif; "><o:p></o:p></span></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; "> </span><span style="font-size: 11pt; font-family: Calibri, sans-serif; "><o:p></o:p></span></div></div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 12pt; font-family: 'Times New Roman', serif; "><span style="font-size: 13.5pt; font-family: Helvetica, sans-serif; ">_______________________________________________<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><o:p></o:p></span></div></div></div><div style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0.0001pt; margin-left: 0cm; font-size: 12pt; font-family: 'Times New Roman', serif; "><o:p> </o:p></div></div></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>