0

Scenario

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

  1. Have at least 1 quantity
  2. Have 0 or more discounts
  3. Have 1 or more attributes
  4. 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):

enter image description here

Questions

  1. 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?
  2. 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?
Improve this question
3
  • I don't think you would want to make attributes that go across products, even if they are "similar". If one changes, but the other does not, you're making a new attribute anyway. Might as well just keep them all 1:1 with the products and not worry about that. As for "discounts", most ERP or inventory systems I've used have a "Price List" entity. Which holds the calculations for your discounts. You can have multiple price lists, each with any number of items on them. The only problem is that this is confusing to end users, who logically think the opposite. Sale on item, not insert item to sale.
    – Jacob H
    Commented Jun 10, 2019 at 12:16
  • Can you explain that Price List system? I don't understand how it works.
    – JeremyVm
    Commented Jun 11, 2019 at 10:48
  • Check out this link for ideas
    – Jacob H
    Commented Jun 11, 2019 at 12:33

1 Answer 1

Reset to default
1

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

Improve this answer
3
  • Thanks for your answer. I always thought that storing null values should be avoided. So wouldn't it be a better solution to have 4 columns? Date | Product | Discount Type | Discount Value (which is stored as a decimal value)
    – JeremyVm
    Commented Jun 12, 2019 at 8:19
  • my 'solution' is just an example, you can change it anyway you want it to be. If you want to store a column 'discountOnFridayOnlyAfter1600hours', please help yourself.... 😊, About storing null values: If It is of no use to store a value in a column, because of the definition of your table, and your table definition allows storing null-values.... then why not store a null value?
    – Luuk
    Commented Jun 13, 2019 at 17:01
  • @Luuk has an excellent data structure. I used this on my project with additional column expiration date.
    – Arelancelot
    Commented Aug 18, 2020 at 3:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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