Re: Storing multiple items in one MySQL field?

On 13/01/12 03:47, tamouse mailing lists wrote:
> On Thu, Jan 12, 2012 at 1:12 AM, B. Aerts<[email protected]>  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?
>

Nope - please check top-of-thread, the 3rd table was off-limits.
A latter without former makes a hard compare.

But depending on this application you might have a valid point: any hints ?

Bert

>>
>>
>> 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