Come conservare i prezzi con date valide?

Per gli articoli che variano in base al tempo (come essere in grado di rispondere a cose come "quale era il prezzo di X alla data D" o "quale mucca era nel feedlot Q alla data E") Consiglio di leggere il libro "Sviluppo Applicazioni di database orientate al tempo in SQL. " Mentre questo libro è esaurito, l'autore ha gentilmente messo a disposizione il PDF del libro e il CD associato sul suo sito web.

link (cerca il primo elemento sotto "libri").

Per una breve introduzione online, vedi:

• link
• link

Conserverei sicuramente la data effettiva nel database. Dopo tutto, è probabile che le persone vorranno essere in grado di eseguire interrogazioni per vedere come il prezzo è cambiato nel tempo o per verificare le stranezze negli ordini rispetto alla tabella dei prezzi dei prodotti storici. A seconda del tipo di query che stai eseguendo e della frequenza delle variazioni di prezzo, potrebbe essere logico disporre di tabelle separate per il prezzo corrente e i prezzi storici.

Nella maggior parte dei sistemi che memorizzano i prezzi, si vorrebbe una colonna della data di scadenza oltre alla data effettiva per rendere più facile determinare quale prezzo è attualmente in vigore poiché si risparmia la fatica di dover guardare al precedente o al successivo fila per capire quale prezzo era in vigore in un dato momento. Non sono chiaro su cosa stia facendo la tua condizione NOW() >= date_price_effective -- presumibilmente, che restituisce il prezzo corrente insieme a tutti i precedenti prezzi storici che a me sembrano strani. Penserei che il "prezzo effettivo" sarebbe il prezzo corrente che sarebbe definito da qualcosa come NOW() BETWEEN date_price_effective AND date_price_expired

Inoltre non sono sicuro di come dovrebbe essere il tuo file. Non è chiaro per me cosa rappresenti provider_1 - il provider_id = 1 prezzo? - o come stai ordinando i dati del provider-- perché provider_1 viene visualizzato per primo per product_id_1 e terzo per product_id_2 .

Se comprendo correttamente la tua affermazione sul problema, hai bisogno di un modo per gestire i dati generazionali (ad esempio, la tabella contiene più righe per ogni coppia id_prodotto / id_prodotto ciascuna delle quali è sensibile alla data). In questo caso, stai cercando il prezzo più recente per un prodotto con un valore data_price_effective che è inferiore o uguale a oggi. Questo tipo di situazione è facile da gestire usando una sottoselezione SQL.

 SELECT 
   provider_id, product_id, price, date_price_effective 
 FROM 
   price_table a 
 WHERE 
   date_price_effective = 
     (
       SELECT 
         MAX(date_price_effective) 
       FROM 
         price_table b 
       WHERE 
         b.provider_id = a.provider_id AND 
         b.product_id = a.product_id AND
         b.date_price_effective <= NOW() 
     );

Un prezzo è efficace purché abbia il valore di date_price_effective più grande che è minore o uguale alla data in cui viene eseguita la query. Un valore data_price_effective maggiore di oggi è una data di validità futura. Il codice sopra elencato restituisce i dati di riga per ogni coppia id_prodotto / id_prodotto che ha il valore data_price_effective più vicino a, ma non oltre la data di esecuzione della query. La soluzione raggruppa automaticamente i prezzi in intervalli di date efficaci. La chiave primaria per questa tabella sarebbe la tripla {provider_id, product_id, date_price_effective};