CODEWITHSUNDEEP
mysqldatabasedatabase-schema
pixelfreak
pixelfreak

Reputation: 17844

Database design for e-commerce site

I am more of a frontend guy, so if these questions come out stupid, I apologize in advance :)

I am designing a database schema for an e-commerce site. The product sold on this site can contain other products. Eq: Think of a car as a product. Car can also contain other products like seats, steering wheel, windshield, etc, each of which can be sold separately.

Question #1: What should Product table have to take this into account? Does it make sense for a Product table to have a field called Products that contain all the productIds associated with this product? (or normalized into its own table for optimization)

Question #2: This site will also have discounts every now and then that can be applied to a product (either on an individual product or a product that contains other products). There are also discounts that is applied to an entire order. What is the best approach to design the Discount table?

Upvotes: 2

Views: 2853

Answers (2)

Abe Miessler
Abe Miessler

Reputation: 85126

Question 1:

Do not include a column with comma separated IDs. You will end up hating life. It's hard to query, hard to update and does not allow you to enforce data integrity. I would recommend using something like this:

Product
--------
ProductId
ProductName
ParentProductId  --nullable

This will give you a table with a recursive hierarchical structure. If the Product does not have any parents just leave the ParentProductId column null.

Question 2:

I would use this structure for the discounts:

ProductDiscounts
-----------------
DiscountId
ProductId  --nullable
Discount

Order
------
OrderId
DiscountId --nullable
OtherStuff

With this structure you would have to build in the logic to apply discounts without a specific product ID to the entire order.

Upvotes: 2

evan
evan

Reputation: 12553

Without knowing more, this should fit your needs:

Q1.

Products table with ids
Contains table with multiple rows of containing product id and contained product id

Q2.

Discounts table which has:
Discount Start datetime
Discount End datetime
Amount decimal
Amount type - percentage or straight dollar amount
Product Id - if null then the discount applies to entire order, not just one product

Hope this helps.

Upvotes: 1

Related Questions