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
