products with different size and colour options

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Has anyone set up a database for a clothing shop. How did you get round the
fact that one product can have different sizes and colours and show which
ones were in stock/sold etc. Did you create a new record for every size and
colour or did you have a number of description fields. At the moment I have
the latter but I'm not very happy with it for various reasons. Any other
ideas about this?
 
Typically, a Label produces a range of Styles in a Season.
Each Style comes in a range of Sizes and Colours.

When you enter the Style, you have a subform where you select the sizes that
apply to that Style, and another subform where you select the Colours that
apply to the style.

Then in the form where orders are placed, the user selects the Style, and
your programmatically filter the Size combo and the Colour combo to those
that apply to the selected Style.

I suggest you use a Text-type key for the Colour table and the Size table.
Your structure will therefore include these tables:

Style: one record for each syle
StyleID primary key
LabelID foreign key to Label.LabelID
SeasonID foreign key to Season.SeasonID
...

StyleSize table
StyleID foreign key to Style.StyleID
SizeID foreign key to Size.SizeID

StyleColour table
StyleID foreign key to Style.StyleID
ColourID foreign key to Colour.ColourID

Size table:
SizeID primary key. Text (so you can enter S, M, L, etc.)
SortOrder Number (so you can sort S before M, etc.)

Colour table:
ColourID primary key. Text (the name of the colour.)
 
So....

I understand your reply that you create a table for various styles and colours and link that back to the product table....but on the Product Table itself do you list each seperately...eg I have gift bags (same style) but one is green, pink and a christmas red. Do I list all three on a product table which will essentially give each a different Product ID number??? In that case then why would I need to do a colour table if I list all three products seperately? I played around with a colour table and linked it back to the product table which gave me a drop down list for the colours, but I cant figure out what the point of that is since I would still need to do three entries on the product table to get all the colours and quantity of each in stock...

I also tried using an example of a Product Detail ID table which essentially auto numbered the Product Detail ID and listed the Product ID number from the ONE entry for the Product (Gift Bags) in the Products Table and the colours that it came in. So I ended up with a table which listed a 1, 2 3 Product Detail ID and used the same Product ID number (from the products table) for all three entries and 3 different colours. But still after linking it back it just gives a drop down menu to pick from.

I am totally stumped... I guess my question is how do I list the same products with different colours in the Product Table....seperately??? which means they will all have a different Product ID # or what???? Because ultimately I want to see how many of each colour I have in stock..... and then if I do that, why would I need a seperate colour table....and if I use a colour table then how do I set up the Product table? Hope that makes sense.....go easy on me, I am just learning this stuff ;)

grrrrr.... LOL

Robyn
 
Back
Top