On Thu, Jan 12, 2012 at 1:12 AM, B. Aerts <ba_ae...@yahoo.com> wrote:
> Karl,
>
> I'm somewhat limited to reading posts, so I might have missed something, but
> can you explain why you wanted to avoid the 3rd table solution ?
>
> Because depending on that question, I can offer two other solutions, but
> they have their own limitations.
>
> Solution 1: the "2,5nd" table.
> you create an extra table and write a procedure that makes a unique ID for
> every combination of attributes (and this procedure needs to be run every
> time you update/add attributes).
>
> when you want to query items for a combination of attributes, you first
> select the attribute combination ID, then query your item list for all items
> with this specific ID.
>
> It isn't exactly a 3rd table solution as it doesn't involve individual
> cross-references between items and a set of attributes, and thus saves you
> an INNER JOIN.

Have you done any performance testing on this to see how your scheme
compares to using a 3rd table?

>
>
> Solution 2: an alphanumeric bit-wise selection.
> Instead of saving your combinations as binary number, you could extend it
> to, for example, a string of 4 characters per attribute: 4 characters times
> 26 characters makes 456976 possible combinations per attribute :
>
> define MENS   = ________AAAA ;
> define WOMENS = ________AAAB ;
>
> define GREEN  = ____AAAA____ ;
> define RED    = ____AAAB____ ;
>
> you write one PHP function that converts an array of combinations into an
> ISO wildcard filter (or does the MySQL syntax provide regular expressions
> ?), and apply this in a SELECT FROM ... WHERE ... LIKE statement.
>
> $findTheseAttribs = new array ( MENS, GREEN );
>
> $SQLlike=createSQLwildcard($findTheseAttribs); // returns ____AAAAAAAA
>
> $mySql = "SELECT * FROM t_myTable WHERE itemAttribute LIKE " . $SQLlike;
>
> The advantage is that you can assign different filter widths per attribute:
> MENS/WOMENS only need 1 character, size only needs 2 ( XXXS, XXS, XS, S, ...
> pretty limited), while colour can have op to 10 characters to encode. This
> leaves reasonable room to expand/scale.
>
> The drawback is that, when you add new attributes, your filter string
> expands, and the LIKE-statement might fail on the difference in string
> lengths.
>
> Bert
>
>
>
> On 12/01/12 02:18, Karl DeSaulniers wrote:
>>
>> Yeah, I was being somewhat facetious about the colors of a shirt. :)
>> I agree on the items and attributes drill-down before implementation
>> though.
>> There will be more than T-Shirts. Watches, book-covers, etc, etc.
>>
>> So I need to find a general logic to cover the items and attributes of
>> each item?
>> And this will make it scalable?
>>
>> Best,
>>
>> On Jan 11, 2012, at 4:39 PM, tamouse mailing lists wrote:
>>
>>>> I am thinking of limiting the colors to 10 for now (after all there
>>>> are only
>>>> so many ways to die a shirt. =)
>>>
>>>
>>> Oh, please. There are lots more than 10 dyes in the world. Take a look
>>> at a women's clothing catalog sometime or other...
>>>
>>> Just look at this one t-shirt item alone:
>>> http://store.americanapparel.net/2102.html
>>>
>>> Individual item characteristics are going to be a lot different than
>>> categories. You'll need expandable attributes for all kinds of things.
>>> Colour is the obvious one here. Also: Size: not everything comes in S,
>>> M, L, or is measured in that way. If this is for an apparel store that
>>> sells a variety of different items, you'll need to solve this
>>> generally across a whole lot of different types of clothing.
>>>
>>> I'd really suggest you do a deep analysis of the different types of
>>> items that are going to be sold, the attributes of each one, and
>>> figure out how to best represent that breadth and depth.
>>
>>
>> Karl DeSaulniers
>> Design Drumm
>> http://designdrumm.com
>>
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to