[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

database design (MS SQL)

Posted on 2006-05-13
4
Medium Priority
?
263 Views
Last Modified: 2008-03-06
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
0
Comment
Question by:JaimeJegonia
  • 2
4 Comments
 
LVL 15

Expert Comment

by:m1tk4
ID: 16673321
orderdetails:
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.

0
 

Author Comment

by:JaimeJegonia
ID: 16673332
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?
0
 

Author Comment

by:JaimeJegonia
ID: 16673353
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.)

0
 
LVL 2

Accepted Solution

by:
Omnibuzz earned 2000 total points
ID: 16673429
Try this, There seems to be lots of redundancy.
If you need the colorsize table as all combinations of colors and sizes, then you can use it as a view.

Create view colorsize
as
select colorid, sizeid from colors, sizes

But I don't think that will be the case.
Then you can do this. Let me know if this is fine.

tbl orderdetails
-orderid [pk]
-ProductDefnID [pk]
-qunatity

(Here you don't need the unit cost, its already in the product definition. So mutiply quntity with unit cost to get total cost
and any change in cost price update in Product table)


tbl colors
-colorid [pk]
-colorname
-colorimage
-fabric

tbl sizes
-sizeid [pk]
-sizename

tbl ProductDefn
-ProductDefnID [pk]
-ProductID
-colorid
-sizeid

tbl products
-productid [pk]
-categoryid
-subcategoryid
-productimage
-productimagethumb
-modelnumber
-modelname
-unitcost
-description

tbl shoppingcart
-recordid [pk]
-cartid
-ProductDefnID
-quantity
-datecreated
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question