AW: need some DB design coaching

[Bart Pietercil]

Hi Tiemo,

Of course you can work without normalisation, but this implicates that your application will be less portable and you will loose speed.
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.
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.

hth,


Bart Pietercil
CTO Cognosis NV

On 03 Mar 2011, at 13:31, Tiemo Hollmann TB wrote:

> 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
>  
> _______________________________________________
> 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/b2e8f024/attachment.htm>