How to deal with Variable data over time in associations -

in linked models (let's drink transaction, waiter, , restaurant), when want display data, informations in linked content :

where beer bought ?

fetch drink transaction => fetch waiter => fetch waiter's restaurant : beer purchased

so @ time t, when display transactions, fetch data following associations, can display :

transactionid   waiter   restaurant 1               julius   caesar's palace 2               cleo     moe's tavern 

let's my waiter moved restaurant.

if refresh table, result be

transactionid   waiter   restaurant 1               julius   moe's tavern 2               cleo     moe's tavern 

but know transaction n°1 made in caesar's palace !

---

solution 1

don't modify waiter julius, clone it.

upside : keep association between models, , still can filter every field of every associated models.

downside : every modification on every model duplicates content, can lot when time passes.

solution 2

keep copy of current state of associated models when create transaction.

upside : don't duplicate contents.

downside : can't anymore use fields on content display, sort or filter them, original , real data inside, let's say, json field. have to, if use mysql, filter data makin plain-search queries in field.

---

what solution ?

---

[edit]

the problem goes further, it's not matter when association changes : simple modification on associated model causes problem too. mean :

what's amount of order ?

fetch drink transaction => fetch product => fetch product's price => multiply order quantity : total amount of order

so @ time t, when display transactions, fetch data following associations, can display :

transactionid   qty   productid 1               2     1  productid   title   price 1           beer    3 

==> amount of order n°1 : 6.

let's beer costs 2,5.

if refresh table, result be

transactionid   qty   productid 1               2     1  productid   title   price 1           beer    2,5 

==> amount of order n°1 : 5.

so, once again, 2 solutions available : clone beer product when price changed ? save copy of beer in order when order made ? have third solution ?

i can't add "amount" attribute on orders : yes can solve problem (partially) it's not scalable solution many other attributes in same situation , can't multiply attributes this.

event sourcing

this use case event sourcing. martin fowler wrote article it, advise read it.

there times when don't want see are, want know how got there.

the idea never overwrite data instead create immutable transactions want keep history of. in case you'll have waiterrelocationevents , pricechangeevents. can recreate status of given time applying every event in order.

if don't use event sourcing, lose information. it's acceptable forget historic information, it's not.

lambda architecture

as don't want recalculate on every single request, it's advisable implement lambda architecture. architecture explained bigdata technology , frameworks, implement plain old java , cronjobs.

it consists of 3 parts: batch layer, service layer , speed layer.

the batch layer regularly calculates aggregated version of data, example you'll calculate monthly income once per day. current month's income change every night until month over.

but want know income in real-time. therefore add speed layer, apply events of current date immediately. if request of current month's income arrives, you'll add last result of batch layer , speed layer.

the service layer allows more advanced queries combing multiple batch results , speed layer results 1 query. example can calculate year's income summing monthly incomes.

but said before, use lambda approach if need data , fast, because adds complexity. calculations needed, should run on-the-fly. example: waiter creates income @ saturday evenings?

example

restaurants: | timestamp  | id | name            | | ---------- | -- | --------------- | | 2016-01-01 |  1 | caesar's palace | | 2016-11-01 |  2 | moe's tavern    |  waiters: | timestamp  | id | name     | firstrestaurant | | ---------- | -- | -------- | --------------- | | 2016-01-01 | 11 | julius   |               1 | | 2016-11-01 | 12 | cleo     |               2 |  waiterrelocationevents: | timestamp  | waiterid | restaurantid | | ---------- | -------- | ------------ | | 2016-06-01 |       11 |            2 |  products: | timestamp  | id | name     | firstprice | | ---------- | -- | -------- | ---------- | | 2016-01-01 | 21 | beer     |       3.00 |  pricechangeevent: | timestamp  | productid | newprice | | ---------- | --------- | -------- | | 2016-11-01 |        21 |     2.50 |  orders: | timestamp  | id | productid | quantity | waiterid | | ---------- | -- | --------- | -------- | -------- | | 2016-06-14 | 31 |        21 |        2 |       11 | 

now let's information order 31.

1. get order 31
2. get price of product 21 @ 2016-06-14
   • get last pricechangeevent before date or use firstprice if none exists
3. calculate total price multiplying retrieved price quantity
4. get waiter 11
5. get waiter's restaurant @ 2016-06-14
   • get last waiterrelocationevent before date or use firstrestaurant if none exists
6. get restaurant name retrieved restaurant id of waiter

as can see becomes complicated, therefore should keep history of useful data.

• i wouldn't involve relocation events in calculation. stored, store restaurant id , waiter id in order directly.
• the price history on other hand interesting check if orders went down after price change. here use lambda architecure calculate full order prices raw order , price history.

summary

• decide of data want keep history.
• implement event sourcing data.
• use lambda architecture speed commonly used queries.