Re: Storing multiple items in one MySQL field?

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