SQL Server – Database Design for an Online Baby Shop and Toy Shop

database-designsql server

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 😀

Best Answer

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.

Related Solutions

Database design Problem for the web-app

From what I understand of your problem your "duplicity" is fine. If your saying that all of the shops act totally independently then yes in your example shop E and shop D might have the same data but the data is not related so it might look like a duplicate, but I would not consider that a duplicate.

Another example, I am answering this question now, but I expect someone else is going to hit the submit button to a different question at exactly the same time, so in their "answer" table (or whatever) there will be two rows with "duplicate" data on it (the time of answer would be the same), is this a problem..... no because the data is unrelated, its simply a record of what happened.

The only time I would suggest normalising this further is if you had to attach extra information to the time which would be the same (so if there was information that would always be the same whenever any shop does any deal that starts at 6pm on a Sunday). A counter argument to this could be do you have shops in different time zones? If yes then 6pm could mean 6pm local time, but not at the same time, in this case what possible extra information could there be which would always be the same in every country, time-zone, shop and deal.

MySQL Database Design – How to Design a Website Database Structure

What you could do is a design like this:

Product(id,name,description,price,productType)
ProductAttribute(productId,attributeName,value)

Then you would have the possibility to filter for all attributes that are currently used in your database.

If you want to be able to filter for all attributes, even if they are not used in any product at the moment, you'll have to add a table

AttributesForProductType(productType,attributeName)

which contains all possible attributes, and then set up referential integrity (foreign keys) between AttributesForProductType and ProductAttribute. This way you lose the risk of misspelling an attribute name in the ProductAttribute table.

edit: If you take this design this also solves your second question: How to display a details page for every possible product type?

All possible attributes for this product are

select attributeName
from AttributesForProductType
where productType=?

all real attributes for a product with their values are

select attributeName, value
from ProductAttribute
where id=?

and those combined are (off of my head, untested)

select apt.attributeName, pa.value
from Product as p
left outer join AttributesForProductType as apt on p.productType=apt.ProductType
left outer join ProductAttribute as pa on apt.attributeName=pa.attributeName
where p.id=?

Best Answer

Related Solutions

Database design Problem for the web-app

MySQL Database Design – How to Design a Website Database Structure

Related Question