Re: Storing multiple items in one MySQL field?

On Jan 12, 2012, at 1:12 AM, B. Aerts 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.
>
>
> 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
>

WOW, thanks Bert!
I actually have come to the conclusion from my search and from
the responses here, that I will definitely need a colors table.
It will normalize? my structure I believe it was said.
Plus I can add as many colors as I want at any time and just assign IDs.

but I must say, your second option was very intriguing to me.
I am afraid I would break it though at this stage in my coding  
experience.. :P

Thank you all for your help!
Much respect!

Karl DeSaulniers
Design Drumm
http://designdrumm.com


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