Challenging "products' availability according to attributes" design

(I have placed a TXT attachment in case this message doesn't display correctly)

Dear Sirs:

In our current project there is a situation related to the attributes available for our products: There are certain conditions they should meet.

Please let me explain using the following example.


Suppose that our factory produces the following:

====================
PRODUCTS TABLE
====================
ID DESCRIPTION
---------------------
001 Basic Can
002 Stamped Can
002 Box
003 Stamped Shirt
004 Stamped Glove


The above can be produced with different "attributes". This is a typical list of different attributes that can apply to some or all of the above products:

===========================================
ATTRIBUTES TABLE
===========================================
ID DESCRIPTION ATTRIBUTES TYPES ID
-------------------------------------------
001 Blue 001
002 Red 001
003 Kelme 002
004 Bianchi 002
005 Fox 002
006 Axo 002
007 Size S 004
008 Size M 004
009 Size L 004
010 Large 005
011 Small 005
012 Lycra 006
013 Nylon 006
014 Mickey Mouse 003
015 Rocket Queen 003

The third column of the table above relates the attributes of similar "nature" as follows:

=====================================================================
ATTIBUTES TYPES TABLE
=====================================================================
ID DESCRIPTION NOTES
---------------------------------------------------------------------
001 Color Available for any kind of items
002 Clothes Stamp We would place it just for clothes
003 Articles Stamp Those stamps are different from the clothes
004 Clothes Size Sizes just for clothes
005 Articles Size Sizes just for articles
006 Shirt Material Shirt's Fabric


Now here comes the complex conditions for the table(s) that relate(s) the products and its attributes. We would name it AVAILABILITY TABLE.

FIRST CONDITION
===============
We should make available the products according to its particular attributes. We would like to use a table which states the final price of the product according to its attributes. Note that a product can have one or a mix of attributes of different nature.

For example, a BASIC CAN can be available in Two sizes: SMALL or LARGE, and in different colors: BLUE OR RED (For a total of 4 combinations).

Also, the STAMPED CAN can be available in the same of above, but with different stamps: MICKEY MOUSE or ROCKET QUEEN (For a total of 8 combinations).

Not to mention that the STAMPED SHIRTS can be available in different SIZES, COLORS, STAMPS and MATERIALS (Here we can extend the attributes as much as we would like (sounds ridiculous, but that 's the "sad real life". Let's say we can include an extra ATTRIBUTE: Short or Long Sleeve, or so on).

SECOND CONDITION
================
We should explicitly set the availability of the combinations of the attributes. For example, a BIANCHI STAMP is only available for the SMALL SIZE, while the FOX STAMP can be available for all of the SIZES.


DESIRED RESULT
==============

Here I wrote a table that shows what we expect to get.

==================================================================================================== =================
AVAILABILITY TABLE
==================================================================================================== =================
|----- ATTRIBUTES IDs (Note: There will be as much columns as ATTIBUTES TYPES) -------------|
---------------------------------------------------------------------------------------------------------------------
ID PRODUCT COLOR CLOTHES STAMP ARTICLES STAMP CLOTHES SIZE ARTICLES SIZE SHIRT MATERIAL PRICE
---------------------------------------------------------------------------------------------------------------------
001 BASIC CAN BLUE .NULL. .NULL. .NULL. LARGE .NULL. 90
002 BASIC CAN RED .NULL. .NULL. .NULL. LARGE .NULL. 90
003 BASIC CAN BLUE .NULL. .NULL. .NULL. SMALL .NULL. 90
004 BASIC CAN RED .NULL. .NULL. .NULL. SMALL .NULL. 90
005 STAMPED CAN BLUE .NULL. MICKEY MOUSE .NULL. LARGE .NULL. 120
006 STAMPED CAN RED .NULL. MICKEY MOUSE .NULL. LARGE .NULL. 120
007 STAMPED CAN BLUE .NULL. MICKEY MOUSE .NULL. SMALL .NULL. 120
008 STAMPED CAN RED .NULL. MICKEY MOUSE .NULL. SMALL .NULL. 120
009 STAMPED CAN BLUE .NULL. ROCKET QUEEN .NULL. LARGE .NULL. 150
010 STAMPED CAN RED .NULL. ROCKET QUEEN .NULL. LARGE .NULL. 150
011 STAMPED CAN BLUE .NULL. ROCKET QUEEN .NULL. SMALL .NULL. 150
012 STAMPED CAN RED .NULL. ROCKET QUEEN .NULL. SMALL .NULL. 150

From the above, please note that the Rocket Queen STAMP has a different price. Just a marketing condition.


What's wrong with the above?
=============================

Naturally, we can't grow the fields of a table according to the content of another. Obviously we can write a table with a limited maximum of allowed fields for attributes, but that is not the expected. The ideal solution is to have as much as desirable.

I would appreciate any suggestions on this matter. I won't mind changing any of the structure in order to achieve the mentioned conditions.

Very best regards

I would suggest you build a set of tables for the attributes and link them to a number
ie
1 blue
2 red
etc

for the input, provide a radio button or check box that returns a number corresponding to the attribute choosen, store the complete string as as your description

13452

each number is then picked from the string, queried against the table and a final description in english is build from your query results. if you need more than 10 numbers (0-9) then just define two digits for one property or use an alphabet character

This approach relates each attribute to a number, allows growth in your attributes, and minimal space required to store the information. All of the long stuff is produced by the output queries.

cheers
RK