0

Let's suppose we have this relationship:

relationship

An example of my problem:

  • A client comes to the store and buys a product for let's say 20$ and has a discount card with a reduction_rate of 5%. The database is going to register 20$ in amount and 19$ in amount_discounted.

Now let's say the discount_cards all drop the reduction_rate to 4% and the amount entered by the employee was wrong because he typed 21$ instead of 20$. It would be an error since the value of reduction_rate was 5% when the product has been bought. I don't know how to keep the value of the reduction_rate of a payement that has been done except by maybe storing the reduction_rate in the payements table but is this the "right way" to do this?

Thank you for reading.

Shadow
  • 33,525
  • 10
  • 51
  • 64
Pouissante
  • 56
  • 1
  • 5
  • 25
  • put a table in between those 2 tables with the reduction calculated at the time of the payment was made – Alberto Sinigaglia Apr 20 '21 at 22:00
  • Thank you for your qucick answer @Berto99. If I understood correctly I would need to put an additional table in between those two to store the reduction_rate at the time the payement was made? – Pouissante Apr 20 '21 at 22:12
  • since a payment can have only 1 discount card, you can put a table in the middle, with pk and fk to payments like `payment_id`, then a fk to the discount , like `discount_card_id`, and than the fields that you want to be immutable (so that if the discount card changes, you still have the original value), so for example the `discount_card_reduction_rate` – Alberto Sinigaglia Apr 20 '21 at 22:16
  • however this would lead you to a ton of `0` on the payments with no discount, so i would actually create a third table (not in the middle), e.g. `discount`, with just pk and fk `payment_id` and then a filed with the discount, like `total_disc` or `discount_card_reduction_rate`, but this depends on what you want to achieve – Alberto Sinigaglia Apr 20 '21 at 22:19

0 Answers0