Quando si conserva la cronologia delle modifiche del prezzo del prodotto, quali sono i pro e i contro di tenere traccia solo degli eventi di modifica dei prezzi?

Domain Driven Design con Event Sourcing , direi che memorizzi gli eventi usando una voce immutabile che va in appendice -solo log. L'evento viene archiviato solo nel momento in cui si verifica, quindi la data di un singolo evento (ad esempio, nessuna data di fine).

Tuttavia, probabilmente vorresti anche conoscere il prezzo corrente di tutto, quindi CQRS / ES dice che hai un altro tavolo da qualche parte che lo memorizza; il design CQRS / ES chiamerebbe quella tabella una cache, che potrebbe essere gettata via e ricostruita a volontà. (Quindi si concentrano sull'indurimento dei log non sulla cache.)

Quando si memorizza il prezzo corrente, non sono necessarie le informazioni sulla data di fine perché si memorizza solo il prezzo corrente. È possibile includere una data di inizio, se lo si desidera, ma qualsiasi altro calcolo relativo ai vecchi prezzi dovrebbe probabilmente tornare all'archivio eventi (o memorizzare altre informazioni memorizzate nella cache).

(Btw, l'archivio eventi potrebbe essere lo stesso database SQL o qualcosa di diverso.)

Il vantaggio di questo è che i registri immutabili append-only sono facili in una varietà di tecnologie (SQL, NoSQL) e che la cache che memorizza solo la data corrente è una tabella molto semplice da utilizzare.

In definitiva, il secondo approccio è più semplice da utilizzare:

• con una sola data, ovvero la data di inizio di un nuovo prezzo, non sai se un determinato record di prezzo è quello da utilizzare per un calcolo in una determinata data:
  • devi sempre leggere diversi record, accessibili in modo ordinato per scoprire quale prezzo deve essere utilizzato
  • anche per il prezzo corrente non puoi esserne sicuro, poiché potrebbero esserci già dei record per le modifiche dei prezzi pianificate in futuro
• con data di inizio e fine, si lavora con una certa ridondanza. Ma è molto più semplice creare query relazionali che utilizzano il giusto prezzo.

Quando si utilizza il secondo approccio, il ripristino di un prezzo precedente deve essere prima analizzato dal punto di vista aziendale:

• si tratta del vecchio prezzo ma da una nuova data in poi? In questo caso devi solo aggiungere un nuovo record di prezzo come al solito
• si tratta di un errore e il nuovo record dovrebbe essere cancellato? In questo caso è più complesso:
• devi trovare il precedente record di prezzo e aggiornarlo ripristinando la data di fine. La ricerca del record precedente può essere utilizzata con un accesso sequenziale a record di prezzo in ordine decrescente o semplicemente utilizzando la data di fine nota (poiché sappiamo che la data di inizio del record di prezzo deve essere cancellata).
• ma devi anche trovare i calcoli memorizzati che hanno utilizzato il prezzo errato e aggiornarlo
• naturalmente, per motivi di coerenza i passaggi precedenti devono essere eseguiti all'interno di una singola transazione.

Per la data di fine data indeterminata ci sono due scuole:

• il purista lascerebbe il campo nullo per mostrare che questa informazione non è nota.
• il pragmatico metterebbe "31.12.9999" come data di fine. Non è così eccezionale, ma consente una maggiore coerenza (data di fine sempre presente) e flessibilità (utilizzo della data di inizio o della data di fine per l'ordinamento) nelle query. Un importante fornitore di ERP usa questo approccio regolarmente, il che mi fa dire che è una tecnica collaudata, anche se non così bella come nulla.

Sono d'accordo con @Christophe in quanto con l'opzione 1 (una singola data per riga) trovare il prezzo per una determinata data (corrente o altro) implicherebbe la lettura di diversi record nell'ordine (il motore di database lo farà, non il tuo codice ). La query dovrebbe essere come questa:

select
    price
from
    price_history
where
    product_id = myProductID
    date <= myDate
order by date desc
limit 1;

Questa opzione ha però un vantaggio: non devi preoccuparti di lacune e sovrapposizioni (vedi sotto). Ma il grande svantaggio arriva quando vuoi cambiare i periodi di tempo perché dovrai giocare con diversi record e processarli sequenzialmente per andare avanti.

Quindi la seconda opzione è migliore , perché la query sarebbe semplice come:

select
    price
from
    price_history
where
    product_id = myProductID and
    from_date <= myDate and
    ( thru_date >= MyDate or thru_date is null);

Ovviamente penso che il prezzo effettivo dovrebbe essere nullo nella colonna THRU_DATE, ma semplicemente non hanno ancora una data thru.

Tuttavia ci sono alcune cose da prendere in considerazione:

• Il PK della tabella PRICE_HISTORY deve essere (PRODUCT_ID,FROM_DATE) , oppure dovrebbe esserci un indice univoco su (PRODUCT_ID,FROM_DATE) se preferisci che il PK sia un surrogato.
• Attenzione agli spazi vuoti: non c'è modo di utilizzare le restrizioni del database per evitare lacune, ad esempio periodi di tempo senza alcun prezzo. Quindi questo dovrà essere risolto con un trigger o una stored procedure.
• Considerate le sovrapposizioni: come sopra, è necessario fornire un trigger o SP per evitare la sovrapposizione delle date per lo stesso prodotto, poiché ciò causerebbe un errore in cui una query per prezzo produce più di una riga.
• Anche solo una riga per prodotto può avere valore null in THRU_DATE . Trigger o SP di nuovo.

Bottom-line:

Alcune persone preferiscono l'opzione 1 (1 data per riga) perché in questo modo non devono preoccuparsi delle lacune e delle sovrapposizioni, ma la seconda opzione è più leggibile dall'uomo, IMO.

In ogni caso aggiungi una tabella di controllo dei prezzi per riflettere chi ha cambiato quale riga un quando. Se desideri registrare non solo old_price e new_price ma anche le modifiche da from_date e thru_date, il PK sulla tabella PRICE_HISTORY dovrebbe essere un surrogato. E non confondere PRICE_HISTORY con PRICE_AUDIT . Una tabella ha il prezzo dei prodotti nel tempo, la seconda contiene le modifiche apportate ai record sul precedente.

Vedi questa altra risposta relativa alle variazioni di prezzo nel tempo, inclusi AUDIT_TABLE e diagrammi ER.

Vado con l'opzione 2 come la maggior parte delle persone. Una conseguenza positiva di FromDate e ToDate è la possibilità di implementare eleganti sconti temporanei senza rischiare lacune:

Table Product_Price
Product_ID  Price  FromDate  ToDate
----------  -----  --------  --------
1           300    1/1/1900  Null
1           250    1/1/2016  8/1/2016

Quindi devi fare la tua scelta ottenendo il prezzo più basso valido per la data di interesse

A meno che tu non abbia una circostanza molto strana in cui desideri un più alto prezzo temporaneo - nel qual caso devi fare più inserti.

Una modifica permanente dei prezzi viene implementata utilizzando un aggiornamento del vecchio prezzo con una data di fine e un inserto del nuovo prezzo.

Secondo me dovresti invece creare un'altra tabella come Item_Pricing e questo dovrebbe aiutarti a finalizzare la cronologia dei prezzi. La cronologia di Item_Pricing della tabella deve essere come questa Item_Pricing (id [PK], itm_id [FK], prezzo, price_still_in_use , date_price_created, ...). L'idea è che la colonna price_still_in_use dovrebbe essere considerata come una bandiera che può avere valore 1 (sì) o 0 (no). Quindi nella tua tabella transazione questo è il prezzo da prendere in considerazione. Quindi ogni transazione o vendita dovrebbe riguardare il prezzo ancora in uso come segue:

Le operazioni (id [PK], ..., item_pricing_id [FK] // questo punterà a Item_Pricing (item_pricing_id) , quantità, data)

Quindi, se il prezzo dell'articolo cambia nella tua domanda, il prezzo dell'articolo non dovrebbe più essere disponibile se non più utilizzato. Un trigger potrebbe anche aiutarti a ottenere i migliori risultati in modo che lo stesso articolo non possa avere più di un prezzo che ha il valore yes per la colonna (attributo) price_still_in_use