Re: Storing multiple items in one MySQL field?

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