1

Hi i'm designing a database for a toy shop this shop offer many products with different categories. boystoys,girlstoys,puzzles,beds,stroller, etc i designed my database as it is consists of 5 tables (Customer,Order,order-details,product, product-category)

here is the database diagram

in the product category i added 2 columns one the primary key(ProductCategoryID) and the second for the (ProductCategoryName) i made it a unique so i can make relation between it and the product table.

My question : do you think that this design is True or not i'm still a beginner in designing a database and i want to know do you advice me to add another tables ?

note: i will not let the customer to pay here i just want from him to order his item and the shop will call him to confirm his order , the customer only will choose his products and then click Order and then a message come's to the shop owner with his details and order

i dont know why my client want to do this in his web site but im trying to develop it :D

Improve this question
4
  • You might find the data models at databaseanswers.org/data_models helpful to your thinking.
    – RLF
    Sep 30, 2014 at 15:51
  • 1
    You do not want to call a table Order unless you really want to escape the name every time you reference it
    – billinkc
    Sep 30, 2014 at 16:34
  • I think about Amazon. There if I'm buying Tea it might show up in "Grocery & Gourmet Food" or it might show up in "Home & Kitchen". Do you really think your customer wants to lose sales because a box of Lego is categorized as a "Boy" toy? I would think not. Therefore, I would think a Product could have multiple categories
    – billinkc
    Sep 30, 2014 at 16:38
  • 1
    en.wikipedia.org/wiki/Junction_table
    – billinkc
    Sep 30, 2014 at 17:01

1 Answer 1

Reset to default
1

Your design commonly looks good except few mistakes.

1) You had used a wrong field in order to establish a relation between ProductCategory and Product. You have to use ProductCategoryID in Product table instead of using ProductCategoryName. Howeever, some products may belong to more than one product category.
If this is a valid case for your project, you should add additional table something like "ProductVsProductCategory" which will allow you to establish a "many to many" relation between Product and ProductCategory tables if you intend to add some products into multiple categories.

Basic structure of ProductVsProductCategory might look like following:

ProductVsProductCategoryID (primary key)

ProductID (refers to Product table)

ProductCategoryID (refers to ProductCategory table)

2) OrderProductName field in the OrderDetail table is unnecessary. You have a relation to Produt table and you can access ProductName from there anyway. You can cancel it.

3) OrderNumber in the Order table is unnecessary if it is not a special value rather than unique identity number. You can use OrderID field as an identity of the order.

4) Similary, OrderQuantity and OrderTotalPrice fields might be unnecessary if you have not special reason to place them there. You can calculate order quantity and total price values by means of using basic sql statements that sums related OrderDetail rows.

There are many additional tables can be added depending on your project requirements. I also recommend you to follow MSDN articles and specially CodeProject in order to improve your abstraction skills.

Improve this answer
0

Not the answer you're looking for? Browse other questions tagged or ask your own question.