products with different size and colour options

[shazzer]

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?

 

[Allen Browne]

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.)