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
The following diagram represents my complete database schema as I have it now (only the section regarding products is relevant here):
- 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?