Construction Advice Please

[Robin Chapple]

I am planning a garment database that will be used for searching for
availability of any garment in a specific colour. Each garment type is
in a table with a garment ID.

As an example polo shirts are one of several garments that are
involved.

Polo shirts are sourced from several manufacturers. They are available
with up to 23 colours as the body colour and the same 23 colours as
sleeve colour and in a few cases a third colour as a trim. That is a
huge number of combinations. Some garments have only three or four
options.

Colours are known by a single character. ie N = Navy

We need to be able to search for availability of garments with navy as
the main colour and to have a list displayed.

What is the best way to design a colour table? Do I have a table with
the garment ID and a separate record for each colour or do I have all
the available colours listed in the colour field or is there a better
way?

Thanks,

Robin Chapple

 

[John W. Vinson]

What is the best way to design a colour table? Do I have a table with
the garment ID and a separate record for each colour or do I have all
the available colours listed in the colour field or is there a better
way?

I'd suggest having three tables to resolve the many to many
relationship:

Garments
GarmentID
<all fields but color, incl. manufacturerID, etc.>

Colors
ColorCode <just a simple lookup table with all the one-letter color
codes>

GarmentColors
GarmentID
BaseColor
SleeveColor
TrimColor

If a given shirt is available with six basecolors, in any combination
with six sleevecolors, this table would have 36 records for that
garment; but you wouldn't be repeating garment information, just three
bytes of color codes and a numeric ID.