On Jan 12, 2012, at 1:12 AM, B. Aerts 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.
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
WOW, thanks Bert!
I actually have come to the conclusion from my search and from
the responses here, that I will definitely need a colors table.
It will normalize? my structure I believe it was said.
Plus I can add as many colors as I want at any time and just assign IDs.
but I must say, your second option was very intriguing to me.
I am afraid I would break it though at this stage in my coding
experience.. :P
Thank you all for your help!
Much respect!
Karl DeSaulniers
Design Drumm
http://designdrumm.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
