Sql-server – Modelling products along with their attributes and discounts for an e-Commerce database

database-designdatabase-diagramseavsql server

Scenario

My application will have products belonging to 1 company. Each of those products will:

Have at least 1 quantity
Have 0 or more discounts
Have 1 or more attributes
Belong to 1 category, which can have parent a parent category

A quantity can be 1 unit, 1 box, 1 pallet… Each quantity will thus have it's own price and that price can change over time. So I've added a fromDate attribute to know which price was active at which time. Is necessary when a product gets bought so I know the price of the quantity at the time of purchase.

A discount can be something like 50 euro, 5%, 2 for 1… Also having a fromDate so I know which discount is applicable.

Attributes will always belong to 1 product. And these will contain a name of the attribute and the value of the attribute. Their goal is to give the company the possibility to add more detailed information next to the existing required information.

Examples of attributes:

Key Feature 1 => Key Value 1
Name => Something
Color => Black
Size => 10 cm (h) x 5 cm (w)
Weight => 450 gr

Diagram

The following diagram represents my complete database schema as I have it now (only the section regarding products is relevant here):

Questions

How do I create my schema so that my requirement for discounts is met? How do you handle the ability to have different types of discounts? And how do you best keep track when there is an active discount for the product and where there is none?
Does it make sense to simply store attributes as name and value? It's possible that the same attribute name will be used for multiple products. Do I keep a list of all possible attribute names? What is the most dynamic way of tackling this issue?

Best Answer

regarding question 1): A table with the following fields 1) date ,to set the date-from which the discount is valid 2) product, to define which product we are talking about 3) discount_type, i.e. 1=percentage, 2=how_many_product_for_this_price, 3=price 4) percentage, needed for discount_type=1 5) quantity, needed for discount_type=2 6) price, needed discount_type=3

Example: If you have this data (I will leave out date for simplicity):

product, discount_type, percentage, quantity, price
1, 1, 0.5, null, 42
2, 2, null, 2, 42
3, 3, null, null, 25

product 1 has a discount of 0.5*100 = 50%, and a price of $42

product 2 can be sold 2 for the price of one, so 2 for $42

product 3 has a discount for $25

Related Solutions

Modeling a db that has products: What are the good practices/solutions? How to treat grammar variations in the words

How would I guarantee to the user that no matter how he types "chair" (singular, plural, with or without adjectives), he will get all the results that the database can provide?

Wildcard queries could solve your immediate problem. For example, your products table may have the following entries:

productID    companyID    productType
--------------------------------------
1            1            chair
2            2            wooden chair
3            2            desk chairs

If you wanted to get all of the products with the word chair in it, you could run the following query (mysql):

select productID, productType
from products
where productType like '%chair%'

However, that design isn't optimal, because you will encounter a problem when a user types in recliner, or some other type of chair that doesn't explicitly have the word "chair" in it.

A better design would be to have a separate table that specifies the categories of products. For example, you could have a categories table that contained a fixed set of categories:

categories table

categoryID    categoryName
---------------------------
1             chair
2             office furniture
3             wooden furniture

Then, you would have a table to map categories to products. Products can have many categories, which will give your users more flexibility when searching. Here is the productCategories table.

productCategories table

categoryID    productID
-----------------------
1             1
1             2
1             3
1             4
2             3
3             2

Finally, you would have your normal products table, which would contain additional information about your products.

Products table

productID    companyID    productName              price
---------------------------------------------------------------
1            2            Plain chair              40.00
2            2            Modern wood chair        120.00
3            2            Black office chair       160.00
4            3            Leather recliner         800.00

With this model, finding all of the chairs is simple. You just need find all products assigned to the chair category.

select p.productID, p.productName, p.price
from products p
left join productCategories pc
on p.productID = pc.productID
left join categories c
on pc.categoryID = c.categoryID
where c.categoryName = 'chair'

This would return the recliner as well. Also, this model gives the user some flexibility for creating more specific searches. For example we can change only one line (see the last line of the query below) to fetch all of the wooden chairs.

select p.productID, p.productName, p.price
from products p
left join productCategories pc
on p.productID = pc.productID
left join categories c
on pc.categoryID = c.categoryID
where c.categoryName in ('chair', 'wooden furniture')

Efficient database design for multiple price based on quantity and attribute

First of all you must define your entities and these form your tables. From what you provide above I can see two tables based on two entities: Product and price.

Then you will add your attributes to each entity such as colour and colour print.

Then you should decide on the nature of your relationships - product has a 1:M relationship with price.

To avoid anomalies in your database you will have to normalise your tables - see https://beginnersbook.com/2015/05/normalization-in-dbms/

Hope this helps :-)