Product Table 1

[janlosangeles]

I want to create a product table. I have many products with different colors. For example I have Product X with Red, Blue, and Green colors. I can also have Product Y in Red, Blue, and Green Colors. But I can not have more than one Product X in blue color. How should I create my table?
What is going to be my primery key?


Thank you


Jan

 

[fkegley]

I would probably uae an AutoNumber field as my primary key. The only reason I wouldn't would be if gaps in the numbering were unacceptable. You will have more gaps with AutoNumber than with other ways.

Once you do that, then you can have multiple records with the same ProductID, so I would have one record in my table for each different ProductID and color combination. I would use a unique index on ProductID and color if I couldn't have the same ProductID and color combination more than once.

Frank kegley

 

[markphsd]

Does each product only have one color, or can it be multicolored. For example, a hat can be red and blue at the same time, or the hat can be red or blue.

If you get into the exact details of your product, rather then being so abstract, it might be helpful.

If you are unsure of how to make aprimary key you should check out the tables and relationships forum.

Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.

 

[imterpsfan3]

I would create a product table, a color table and a productcolor table. You have a many-to-many relationship of products to colors and colors to products.

tblProduct
ProductID (Primary key autonumber)
Product

tblColor
ColorID (Primary key autonumber)
Color

tblProductColors
ProductColorID (primary key autonumber)
ProductID (foreign key to tblProduct)
ColorID (foreign key to tblColor)

Then create a unique index on ProductID and ColorID in the table design of tblProductColors. Go to View Indexex.

 

[janlosangeles]

To make my question more clear:
Each color can have many color. For example, I can have a Hat in blue color, in red color, and in green color. I have a shirt in blue color, and in red color. I can have a pants in purple and shirts in black.

I want to make sure not to have a duplicate combination. For examples, I do not want to enter Hat-Blue Combination more than once.

I already have a primary key set as my autonumber.

Thanks for the help.

 

[PHV]

Create a unique composite index on (ProductID, ColorID) in your tblProductColors junction table.
In fact you don't need the ProductColorID autonumber field as you may choose the 2 above fields as PK.

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

 

[imterpsfan3]

According to database theory, or how I have understood it, creating composite primary keys is considered bad database design.

I can see how this is true because when you join tables, then you're having to create joins on two fields or more. I tend to go the single primary key route and then apply a composite index. Everyone has their own style.

But then again, who made the rules anyway?

See the link below:

http://forums.asp.net/1067985/ShowPost.aspx

 

[MajP]

Is there a way to uniquely identify a product. To me hat, shirt is a product type not an identifier of the product. You could have brown cowboy hat, brown mens dress hat, brown ladies hat etc. I think of J. Peterman on Seinfeld. He always had an unique identifier. For example "The Urban Sombrero" which came in several colors. Or the "Malaysian Jungle River Hat." If I order from J. Crew the products all have a unique identifier which if you go into the warehouse I be the boxes are labled with that identifier (example 2x12RX4). So I think it would be something like this:

tblProduct
productIdentifier (ex 2x12RX4, or The Urban Sombrero)
productType (Hat, Shoe, Etc)
fKeySupplier (a key to your supplier Table)
currWhslePrice (assuming all sizes and all colors have same price
currSalePrice (Same as above)

tblAvailableProductChoices
fkeyProduct (key to the product)
strSize (If necessary size of product)
strColor (color)
intQuantity (number of that product of that color, in that size on hand)

Look at the Northwind database if you are going to track orders, it is an ok example.