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<ba_aerts@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?

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