Re: Storing multiple items in one MySQL field?

"B. Aerts" <ba_aerts@xxxxxxxxx> · Fri, 13 Jan 2012 13:17:09 +0100

On 13/01/12 03:47, tamouse mailing lists wrote:

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