php - Effective management of data changes -

php - Effective management of data changes -

i have table called bookings. table contains info representing booking made particular service, many variables.

a while ago came across problem current info construction whereby changes booking affected times, dates or prices impact other associated financial records, bookings lists dates etc.

my solution @ time create modifications table track changes made booking. then, whenever booking model asked homecoming booking, add together on modifications made (in afterfind() cake callback) , nowadays up-to-date version of booking, (excuse paint drawing):

this method works fine when inquire booking model homecoming booking #1234. returns up-to-date representation of booking including modifications (layered on top of each other), including array containing modifications and original booking info reference.

my problem i've realised need able query model custom conditions, , if 1 of conditions realised in 1 of modifications, result wouldn't match because model searching original record rather presented record. illustration query model homecoming rows abc bluish (not grey):

in example, model looks straight @ original info rows abc bluish , doesn't homecoming result, because bluish value in modification attached after original results found.

what i've done set query beforefind() callback of booking model modifications match given criteria, joining booking create sure other criteria still matches. when returns bluish in illustration above, stores result in array class property , continues regular find(), excludes booking's id beingness returned (because we've found more up-to-date verison of it). it'll merge them together, sort them 1 time again etc in afterfind().

this works, although it's little more long-winded hoping for.

after that, i've realised in other parts of application, there models manually joining bookings table , searching bookings. need way able incorporate modifications of manual joins straight table in mysql without affecting original info , preferably without changing much of code.

my thoughts need remove manual bring together , create model association instead. beforefind() , afterfind() of booking model still run when query client model hasmany bookings (to apply modifications each booking)?

my other alternative homecoming more rows mysql necessary removing criteria might contained in modifications, utilize php filter results per search criteria. alternative scared me little because result set has potential massive without criteria...

how can accomplish info structure? key requirements still not want alter original booking record, rather add together modification records on top, need able query bookings (including modifications) through model.

i want seek , maintain much of integration behind scenes possible won't have go through entire application alter n number of queries this:

$get_blue = $this->booking->find('all', array( 'conditions' => array( 'booking.abc' => 'blue' ) ));

i want able implicitly include modifications made bookings up-to-date booking returned in above query.

the other problem when booking model manually joined search query, this:

$get_transactions_on_blue_bookings = $this->transaction->find('all', array( 'joins' => array( array( 'table' => 'sql_bookings_table', // non-standard cake format, know - it's illustration 'alias' => 'booking', 'type' => 'left', 'conditions' => 'booking.booking_id = transaction.booking_id' ) ), 'conditions' => array( 'booking.abc' => 'blue' ) ));

as can see, above query won't include modification in mspaint illustration above, because it's manually joining table in sql (the modification integration in before , afterfind() callback functions of booking model).

any help on appreciated.

edit

i know long plenty already, thought i'd add together reason want track these changes , not update original record financial aspect can't change, because impact reporting.

the quickest , easiest solution can see far apply modifications straight original booking in cases except when affects financial information, still tracked modification (because don't need search based on info).

it sounds you're trying implement temporal database. temporal back upwards 1 of major additions ansi/iso sql:2011 standard. mysql (like rdbms) lags behind standard. think of temporal database dbms equivalent of cvs/svn/git.

by contrast, traditional database utilize without temporal features can called current database.

in current database, if seek implement temporal support, can fail in many ways different approaches:

the one-table approach. when need create modifications, updates on original records, , unless have sort of homegrown trigger/audit logic, history trail absent. if have audit/change log, you'd have ugly digging reconstruct alter history.

the two-table approach. instead of making modifications in-place, split out info 2 tables, 1 base/original records (e.g. booking), , table changes/modifications/deltas. @ to the lowest degree have original info preserved, 1 time again have write complex logic view original info modifications layered on. gets worse if want some of modifications applied.

the precalculated resultant table approach. maintain 3 or more tables: base of operations records, modifications, , table attempts have resultant (keeps date base of operations + modifications). luck writing triggers , procedures calculation whenever inserts, , heaven help if update or delete needed. setup fragile , break out of sync, such deadlocks & rollback. if don't within db triggers/procedures, seek implement resultant calculation in application code, have luck @ -- , ugly multi-threaded consumers. , still, don't have easy access resultants some modifications applied.

conclusion: if you're not limited mysql, should consider using db has built-in temporal support. otherwise, you're going re-implement wheel.

php mysql cakephp database-design cakephp-1.3