Table structure for Shop 1

[Idee]

Hi,

For adding products in database which are to be sold online, I want table structure for products which should be able to handle products which have 2 dimensional options like the size and color. Like for a product, size L available in colors red, blue and green and size S in colors Red and blue only. I want to add this product as one record in databse and its options can be in other table. I am not sure how it will be possible to handle this:
My table can be:

Product Table

ProductId
ProductName
ProductDesc
Price

Option Table
OptionId
OptionType


Link Table
ProductId
OptionId

But the problem is that these tables will only be able to handle either color or size seperately and not both together.

Your suggestions will be very valuable for me

Thanks

 

[willir]

Inderdip

When creating tables, I find it helps to ask what is unique for this object.

In your case the properties of a product are size and colour, so I suspect int order to get what you are looking for is...

tblProduct

ProductID
ProductName
ProductDesc
Price
ProdSize
ProdColour
OnHand
OnOrder

When checking to see if a product is available, if it does not exist in the product table, it does not exist. And vice versa.

So how the heck can you force a person to use a spcific size and colour?
The person entering / creating products will have to know this, but "you" can help by using restricting size and colour by...
- table level (lookup tab, switch to list or combo display, row source)
- form level (combo or list box, row source)
- supporting table or tables

I prefer to store my values in one table and access the with a combo box or list box

tblVari

CtlText
CtlName

Example

CtlText CtlName
ProdSize Small
ProdSize Medium
ProdSize Large
ProdCol Red
ProdCol Blue

This will not prevent some one from creating a new product - Large Yellow Sweaters when you will never have this product. Again, this control will rely on the expertise of the person in control.


An issue you will have to address is inventory. There are books and book written on this subject. Basically, if you are storing your products at different locations, you need to create a "tblStock" to track inventory. If you are storing the product at only one location, then it may be okay to include inventory on the product table.



Ready for some chaos in the mix?

There is another way to look at Products.

A product have many colours.
A colour may have many products.

This leads to a many-to-many relationship for products and size and, products and colour.

Tables could be as follows...

tblProduct

ProductID
ProductName
ProductDesc
Price

tblSize
ProdSize

tblColour
ProdColour

tblProdSize (Product and size)
ProductID
ProductSize

tblProdCol (Product and Colour)
ProductID
ProdColour

From a relational perspective, this works, so far...

But now we have a problem -- How do we track inventory?
OnHand
OnOrder

Inventory can not be put on the Product table because you can not differentiate from the number of yellow and red sweaters.

If you put it on the joining table for Product and Colour, because you will not know the quantity for what sizes are available. Likewise for the product and size joing table, you will not be able to determine what colours that or on hand.

There is probably a solution for this, but it gets messy.


So this takes us back to the original design where the a "product" is a product + colour + size. (I am sorry for the confusion, but I felt it is necessary to include inventory, and how to address inventory -- otherwise, you could end up with a problem later on.)


There are draw backs when using design -- product is product + size + colour. For example, you may want to know how many Avril Levigne T-Shirts you have in stock for all sizes and all colours.

This will have to rely on how you code your products. There are two routes to go on this -- keep it simple or use a smart code that includes a suffix to indicate colour and size.

A simple product code

AVIRLSHIRT
MADONPANTS

or

SHIRT
PANTS

An example of a smart code,

AVRILLSHIRT-SM-RD -- T-shirt, small, red
AVRILLSHIRT-LG-YL -- T-shirt, large, yellow
MADONAPANT1-MD-BL -- Pants, medium, black

The thing about smart codes is that they should be a fixed size because later on, you need to develop a select clause to gather your information.

In other words, you will need to think on how you want the product information to be displayed.

One more comment on the product code.
Some would say you can use this as your primary key. Although the product should unique for a product, I personally would not use it as the primary key for one reason -- sooner or later, you may want to change your product code (say move from a simple to complex / smart product code). If you are using the product as the primary key, this would become a complex and time consuming process (you have to change all the invoices, shipments, and many-to-many tables). If you use an internal primary key, ProductID, changing the product becomes much easier - perhaps change the field size on a form or report.


To summerize, you may be looking at...

tblProduct

ProductID (autonumber or other, primary key)
ProductCode (text, 10 to 25 characters, indexed, no nulls, unique)
ProductName (text, 50+)
ProductDesc (text, 255)
Price (numeric, currancy)
ProdSize (text, 2 to 15)
ProdColour (text, 2 to 15)
OnHand (numeric, long)
OnOrder (numeric, long)


Hope I have given you some food for thought.

Richard

 

[Idee]

Thanks a lot Richard for explaining the concept in so many different ways.

Now what I have understood from your final conclusion is that each product with differnet color and size has to be entered as a seperate record in database. I really liked your idea of not making productcode as an index key. That really makes lot of sense.

I really appreciate your help.

Thanks once again

 

[cloudseven]

Hey Richard, I am also doing an e-commerce site right now and after reading this thread, it's helped me a lot with designing my inventory. Great concepts!!!

 

[willir]

cloudseven
I have not been doing this for too long, like less than a month, but I am glad I am helping. But I have to say, it is far easier to help when working face-to-face than over a forum. But this is still fun.
Richard