Product Table

[janlosangeles]

I want to create product table. I have many products with many colors. For example:

Product COLOR
Hat Blue
Hat Pink
Hat Red
Shirt Blue
Shirt Red
Shirt Pink
Pants Black
Pants Red

1) How should I create my table or tables so I do not enter any duplicates?
2) If I have to create many tables, in which table do I place my quantity and the selling price, quantity received and quantity sold?
3) Also I want to be able to enter blue in my search box and my search comes up with Hat and Shirt.

Thank you for your help.

 

[PHV]

Tons of threads in this forum about similar topic.
Search for many-to-many junction (or bridge) table.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[willir]

janlosangeles

Not sure if you found an answer for this per PHV's suggestion.

A "product" is composed of one or more categories. In your case, you have clothing type and colour. But you can also add size, gender, style, etc.

A simple many-to-many table for your example would be...

tblProduct
ProductCode - primary key
ProductName

Eg.
SHRT - Shirt
PANT - Pants

tblColour
ColourCode - primary key
ColourName

Eg.
RED - Red
BLK - Black
BLE - Blue

tblProdColour
ProductCode - foreign key to tblProduct
ColourCode - foreign key to tbleColour

For a small joiner table like this, the primary key would be ProductCode + ColourCode.

Eg.[tt]
ProductCode ColourCode
SHRT RED
SHRT BLE
PANT BLK
[/tt]

But it gets more complicated...

Add size and style

tblSize
SizeCode - primary key
SizeName

Eg.
SM - Small
MD - Medium
LG - Large

tblStyle
StyleCode - primary key
StyleName

Eg.
CHIC - Chic
WNTR - Winter
FALL - Fall
SMMR - Summer
CSUL - Casual

Putting it together. Your "joiner" or junction table would be expanded to include these new categories...

tblProdProfile
ProductCode - foreign key to tblProduct
ColourCode - foreign key to tbleColour
SizeCode - foreign key to tblSzie
StyleCode - foreign key to tblStyle

Eg.[tt]
ProductCode ColourCode SizeCode StyleCode
SHRT RED SM CSUL
SHRT RED MD CSUL
[/tt]
The primary key for larger is more debateable. Instead of making each foreign key part of the composit primary key, another approach would be to use a serial number or autonumber...

tblProdProfile
ProductProfileID - primary key, autonumber
ProductCode - foreign key to tblProduct
ColourCode - foreign key to tbleColour
SizeCode - foreign key to tblSzie
StyleCode - foreign key to tblStyle

Richard

 

[janlosangeles]

Thank you Richard,
Fortunatelly, my tables is not complicated.
I only have a different product and colors. I do not have to worry about size or style.

Thank you