database design (MS SQL)
i have a problem on my database design for my online clothing store. my tables are listed accordingly below. in my orderdetails table i wanted that the same product but different size and/or color be a DIFFERENT ITEM. so i made my colorid and sizeid also primary keys. is there a better way of designing this table and achieve the same purpose?
every product may be available in a set of colors and in turn for this set of colors, each will have a set of available sizes to it -- i wanted that each available color for the said product will have a small image of that product in that color. hence, i have colorid and productid as primary keys for my colors table.
my idea for the colorsize table is to get all sizes linked to a colorid.
i just wanted to get some ideas and some insights from those who know better to achieve a "good" database design for my ol store.
any suggestions and/or comments would be greatly appreciated. thanks.
tbl orderdetails
-orderid [pk]
-productid [pk]
-colorid [pk]
-sizeid [pk]
-qunatity
unitcost
tbl colors
-colorid [pk]
-productid [pk]
-colorname
-colorimage
-fabric
tbl sizes
-sizeid [pk]
-sizename
tbl colorsize
-colorid [pk]
-sizeid [pk]
tbl products
-productid [pk]
-categoryid
-subcategoryid
-productimage
-productimagethumb
-modelnumber
-modelname
-unitcost
-description
tbl shoppingcart
-recordid [pk]
-cartid
-productid
-colorid
-sizeid
-quantity
-datecreated
every product may be available in a set of colors and in turn for this set of colors, each will have a set of available sizes to it -- i wanted that each available color for the said product will have a small image of that product in that color. hence, i have colorid and productid as primary keys for my colors table.
my idea for the colorsize table is to get all sizes linked to a colorid.
i just wanted to get some ideas and some insights from those who know better to achieve a "good" database design for my ol store.
any suggestions and/or comments would be greatly appreciated. thanks.
tbl orderdetails
-orderid [pk]
-productid [pk]
-colorid [pk]
-sizeid [pk]
-qunatity
unitcost
tbl colors
-colorid [pk]
-productid [pk]
-colorname
-colorimage
-fabric
tbl sizes
-sizeid [pk]
-sizename
tbl colorsize
-colorid [pk]
-sizeid [pk]
tbl products
-productid [pk]
-categoryid
-subcategoryid
-productimage
-productimagethumb
-modelnumber
-modelname
-unitcost
-description
tbl shoppingcart
-recordid [pk]
-cartid
-productid
-colorid
-sizeid
-quantity
-datecreated
ASKER
no, actually this online store is not yet international.
at the moment it can accept orders only in our country only so no need for European/Japanese size equivalents
so you mean, i don't need to create tables for colors and sizes?
about the orderdetails table, if i have productid and orderid only as my clustered primary key how can i specify that the customer ordered this product of this color and of this certain size?
at the moment it can accept orders only in our country only so no need for European/Japanese size equivalents
so you mean, i don't need to create tables for colors and sizes?
about the orderdetails table, if i have productid and orderid only as my clustered primary key how can i specify that the customer ordered this product of this color and of this certain size?
ASKER
ok, i didn't get what u mean at first about the colors and sizes tables lolz
i get it now. but if i made the colorname pk, many products can have color red for example -- but the image is the product itself in that color not just a picture of a cloth/fabric. the relationship is not many to many. (one colorimage to one productid. one productid to one or many colorimage.)
i get it now. but if i made the colorname pk, many products can have color red for example -- but the image is the product itself in that color not just a picture of a cloth/fabric. the relationship is not many to many. (one colorimage to one productid. one productid to one or many colorimage.)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The compound key for order is probably not a good idea. You are better off with using your own generated ID as primary key - you do have order NUMBERs, right? The orderdetail records are really lines in this purchase order that will become lines in the invoice. Number them 1-2-3-4 and so on.
colors:
unless you are planning to rename colors at some time, you can safely ditch colorid and use colorname instead
sizes:
this looks either totally redundant, or, if you just want a dictionary you could again make sizename primary key and ditch sizeID. The table however might be useful if you plan to add European/Japanese size equivalents. For example, in US when you buy cycling stuff (jerseys, shoes, etc.) 70% of it will be sized in EU sizes (46,48,50) and only 30% domestic made willbe M, L, XL. Depends.