# DB Design - comments to the scheme

## sp_compensation_links

SPs compensation models are very likely to change over time, so i think table should include sth like
effective_from: date
effective_to: date
with a model validation making sure only one is active at any time

## price_models

I would imagine price models like these:
Let's assume we have only two price models

#### Linear
`price = qty * unit_price`

#### Regressive
(price decreased after some treshold of qty is reached ):

if qty > some_treshold
  price = (some_treshold * unit_price) + ((qty - some_treshold) * discounted_price )
else
  price = (qty * unit_price) // normal, linear price
end

I would have two classes like `LinearPriceModel` and `RegressivePriceModel` both descendants of `PriceModel::Base or sth` with calculation logic

and then I'd have a price model row containing all possible args that get passed to instance of a price model class

and then have sth like

@startuml

class PriceModelAssignment {
  price_model_type
  product_id
  begins_at:date
  ends_at:date
  .. // params for PriceModel STI such as:
  qty_discount_treshold eg. 100 units
  discount_value eg. 50
  discount_unit eg. 'percent'
}

class Product {}

Product "1" -- "*many" PriceModelAssignment

@enduml

id, price_model_type, product_id, begins_at, ends_at, qty_discount_treshold, discount_value, discount_unit
1, LinearPriceModel, 123, 2021-08-01, 2021-08-31, NULL, NULL, NULL
2, RegressivePriceModel, 123, 2021-09-01, 2021-09-30, 2, 50, 'percent'

which would mean: in September, whenever you buy Socks (productID 123) whenever you buy more than 2 of them, 3rd and every next one comes at 50% of price.

## extra_services

can we have some examples please? I'm not sure what cases are to be handled

## orders

1. stripe_charge_id -> payment_provider_charge_id

2. why have `marketing_consent_at` and `save_user_consent_at` in here?

3. should't we have a reference to an addrtess object in t he order?

4. having coupon code as a field limits our ability to handle multiple coupons. Consider having coupons as a type of LineItem in the order.

## sp_type_links

Is it really that SPs can be of multiple types at the same time and require join table? Seems like total edge case to me.

## Line items
1. Discount(integer) - is it a FK or some value?
2. How lineItem is being related with booking? It looks like that there is only a poisibility to by Products? What happend with sellableThings?
3. How about adding some calculated prices/discounts/etc?