Jak przechowywać ceny z obowiązującymi datami?

Mam listę produktów. Każdy z nich jest oferowany przez N dostawców.

Każdy dostawca podaje nam cenę na określony termin. Cena ta obowiązuje, dopóki dostawca nie zdecyduje się ustalić nowej ceny. W takim przypadku dostawca poda nową cenę z nową datą.

Nagłówek tabeli MySQL wygląda obecnie tak:

provider_id, product_id, price, date_price_effective

Co drugi dzień tworzymy listę produktów / cen obowiązujących w danym dniu. Dla każdego produktu lista zawiera posortowaną listę dostawców posiadających ten konkretny produkt. W ten sposób możemy zamówić określone produkty od każdego, kto oferuje najlepszą cenę.

Aby uzyskać efektywne ceny, mam instrukcję SQL, która zwraca wszystkie wiersze, które mają date_price_effective >= NOW(). Ten zestaw wyników jest przetwarzany za pomocą skryptu ruby, który wykonuje sortowanie i filtrowanie niezbędne do uzyskania pliku, który wygląda następująco:

product_id_1,provider_1,provider_3,provider8,provider_10...
product_id_2,provider_3,provider_2,provider1,provider_10...

Działa to dobrze dla naszych celów, ale wciąż mam wrażenie, że tabela SQL prawdopodobnie nie jest najlepszym sposobem na przechowywanie tego rodzaju informacji. Mam wrażenie, że ten rodzaj problemu został wcześniej rozwiązany na inne, bardziej kreatywne sposoby.

Czy istnieje lepszy sposób przechowywania tych informacji niż w SQL? lub, jeśli używasz SQL, czy istnieje lepsze podejście niż to, którego używam?

W przypadku przedmiotów, które różnią się w zależności od czasu (takich jak np. „Jaka była cena X w dniu D” lub „która krowa była w magazynie Q w dniu E”), polecam przeczytanie książki „Rozwijanie zorientowane na czas” Aplikacje bazodanowe w SQL. ” Chociaż ta książka jest wyczerpana, autor łaskawie udostępnił plik PDF książki, a także powiązaną płytę CD na swojej stronie internetowej.

http://www.cs.arizona.edu/~rts/publications.html (poszukaj pierwszego elementu w „książkach”).

Krótkie wprowadzenie online, patrz:

• http://talentedmonkeys.wordpress.com/2010/05/15/temporal-data-in-a-relational-database/
• http://martinfowler.com/eaaDev/TemporalProperty.html

Z pewnością zapisałbym datę wejścia w życie w bazie danych. W końcu prawdopodobne jest, że ludzie będą chcieli uruchamiać zapytania, aby zobaczyć, jak zmieniała się cena w czasie lub aby sprawdzić dziwności zamówień w porównaniu z historyczną tabelą cen produktów. W zależności od rodzaju zapytań i częstotliwości zmian cen, sensowne mogą być osobne tabele dla ceny bieżącej i cen historycznych.

W większości systemów, które przechowują ceny, oprócz daty wejścia w życie powinna być także kolumna z datą wygaśnięcia, aby ułatwić ustalenie, która cena jest aktualnie obowiązująca, ponieważ oszczędza ci konieczności patrzenia na poprzedni lub następny wiersz do liczb która cena obowiązywała w danym momencie. Nie jestem NOW() >= date_price_effectivepewien, co robi twój stan - prawdopodobnie zwraca bieżącą cenę wraz ze wszystkimi wcześniejszymi cenami historycznymi, co wydaje mi się dziwne. Sądzę, że „cena efektywna” byłaby ceną bieżącą, która byłaby określona przez coś podobnegoNOW() BETWEEN date_price_effective AND date_price_expired

Nie jestem również pewien, jak powinien wyglądać twój plik. Nie jest dla mnie jasne, co provider_1reprezentuje - dostawca_id = 1 cena? - lub jak zamawiasz dane dostawcy - dlaczego provider_1pojawia się jako pierwszy, product_id_1a trzeci jako product_id_2.

Jeśli dobrze rozumiem twoje zgłoszenie problemu, potrzebujesz sposobu na obsługę danych generacyjnych (tj. Tabela zawiera wiele wierszy dla każdej pary id_dostawcy / id_produktu, z których każda jest wrażliwa na datę). W takim przypadku szukasz najnowszej ceny produktu o wartości efektywnej daty i wartości, która jest mniejsza lub równa dzisiejszej. Ten typ sytuacji można łatwo rozwiązać za pomocą podselekcji 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() 
     );

Cena obowiązuje tak długo, jak długo ma największą efektywną datę_procedury, która jest mniejsza lub równa dacie wykonania zapytania. Wartość efektywna dla daty, która jest większa niż obecnie, jest przyszłą datą wejścia w życie. Powyższy kod zwraca dane wiersza dla każdej pary id_dostawcy / id_produktu, która ma wartość efektywną_data, która jest najbliższa, ale nie później niż data wykonania zapytania. Rozwiązanie automatycznie grupuje ceny w obowiązujące zakresy dat. Podstawowym kluczem dla tej tabeli byłby potrójny {dostawca_id, product_id, date_price_effective};