Portal aukcyjny - jak przechowywać zmiany danych

0

Załóżmy, że tworzę stronę typu portal aukcyjny, takiego klona Allegro. Użytkownicy tak mogą tworzyć aukcję składającą się z:

  • ceny
  • opisu

Chciałbym móć śledzić zmiany aukcji, tzn. wiedzieć, że ktoś zmienił cenę, opis itd. Jak powinienem to zrobić? Pomyślałem o kilku możliwościach

  1. Mieć tabelę offer:
| id | account_id |
|----|------------|
| 7  | 10         |
| 8  | 11         |

oraz tabelę ze stanami aukcji:

| id | offer_id | price | description | created                 |
|----|----------|-------|-------------|-------------------------|
| 12 | 7        | 100   | opis 1      | 2021-05-11 16:06:06.149 |
| 13 | 7        | 100   | opis 2      | 2021-05-11 18:06:06.149 |
| 14 | 7        | 120   | opis 3      | 2021-05-11 19:06:06.149 |
| 15 | 8        | 50    | test        | 2021-05-12 11:06:06.149 |
| 16 | 8        | 45    | test        | 2021-05-12 12:06:06.149 |

Gdy użytkownik dodaje opcję, to tworzę wpis w tabeli offer, oraz offer_changes, a gdy modyfikuje aukcję, dodaję kolejny wpis do offer_changes. Dzięki temu mam informacje o wszystkich zmianach, ale minusem jest to, że mam trochę nadmiarowych danych (gdy zmieni się opis, a nie cena, to i tak zapisuję zarówno nowy opis, jak i cenę.

  1. Mieć tabelę offer:
| id | account_id |
|----|------------|
| 7  | 10         |
| 8  | 11         |
| 9  | 22         |

Oraz tabele price_history:

| id | offer_id | price | created                 |
|----|----------|-------|-------------------------|
| 1  | 7        | 100   | 2021-05-11 16:06:06.149 |
| 2  | 7        | 120   | 2021-05-11 19:06:06.149 |
| 3  | 8        | 50    | 2021-05-12 11:06:06.149 |
| 4  | 8        | 45    | 2021-05-12 12:06:06.149 |

oraz analogiczną tabelę: description_history. Plusem jest to, że każdy wiersz to zmiana (a wcześniej nie zawsze tak było), a minusem jest to, że mam dużo tabel.

  1. Mieć tabelę offer:
| id | account_id | price | description             | created |
|----|------------|-------|-------------------------|---------|
| 7  | 10         | 100   | 2021-05-11 16:06:06.149 |         |
| 8  | 11         | 50    | 2021-05-12 11:06:06.149 |         |

A zmiany logować do pliku. Plusem, jest to, że nie tworzę dodatkowych tabel, a minusem to, że np.ciężko wgrać brakujące dane. To znaczy załóżmy, że była jakaś awaria i przez godzinę nie były przetworzone jakieś zmiany i gdyby je teraz przetworzyć, to nie wiadomo co się zmieniło na co

Macie jakieś propozycje?

2

Ja bym wszystko ogarnął na poziomie bazy danych nie musząc zmieniać nawet jednej literki w głównym kodzie źródłowym strony/serwisu.

Utworzyłbym osibną tabelę o nazwie history_* z takimi samymi kolumnami co w tabeli, z której chcesz trzymać historię + timestamp. Do tego napisałbym trigger onUpdade i onInsert, który bieżący rekord NEW zapisuje w tabeli history_*.

Zalety:
1 Z punktu widzenia programowania serwisu nie musisz w ogóle pamiętać o tym, że w tle robi się automatycznie jakaś historia.
2. Nie ma wpływu na szybkość wyszukiwania po głównych tabelach.
3. Zawsze możesz zrobićkopię bazy bez historii...
4. W każdej chwili możesz ją włączyć albo wyłączyć.

Jak chwilę pomyślisz to jednym skryptem zrobisz mechanizm, który automatycznie będzie Ci generował aktualizacje triggerów na wypadek zmian w strukturze głównych tabel.

W praktyce przykładowo masz tabelę

offers
----------
idOffer
title
description

Poterm robisz tabelę history_offers:

CREATE TABLE history_offers (
    idOffer INT,
    title VARCHAR(255),
    description TEXT,
    operation_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Na koniec trigger:

Następnie zdefiniujemy funkcje, które będą działać przy wywołaniu triggerów:

CREATE OR REPLACE FUNCTION save_history() RETURNS TRIGGER AS $save_history$
BEGIN
    INSERT INTO history_offers (idOffer, title, description)
    VALUES (NEW.idOffer, NEW.title, NEW.description);
    RETURN NEW;
END;
$save_history$ LANGUAGE plpgsql;
CREATE TRIGGER trigger_after_insert
AFTER INSERT ON offers
FOR EACH ROW
EXECUTE FUNCTION save_history();

CREATE TRIGGER trigger_after_update
AFTER UPDATE ON offers
FOR EACH ROW
EXECUTE FUNCTION save_history();
4
  1. Nic nie loguj do pliku. Po to jest baza żeby w niej mieć dane.
  2. Trzymaj zawsze CAŁĄ historię, i nie przejmuj się duplikacją. Snapshottuj luźno jak leci, dyski są tanie. Dodanie wyświetlania historii będzie bardzo proste później.
0

Jak bardzo chcesz rozwiązanie które nie angażuje głównej bazy to możesz postawić bazę dokumentowa i w głównej tylko trzymać ID do tych aukcji które umieścisz w dokumentowej. Zobacz sobie jakieś bazy jsonowe np. Redis.

1

Może podejście z event sourcing?

1

A może bez kombinacji i skorzystać z klasycznego wzorca Memento? Czasami nazywany jest migawką, pamiątką, artefaktem. Dziwnie się czuję jak widzę to od strony bazy jako elementu początkowego.

1

To, co potrzebujesz to zwykłe wersjonowanie wpisów. Sposobów na rozwiązanie tego jest trochę, wszystko zależy od odpowiedzi na pytanie "po co to jest?".

Taki najprostszy to - mniej więcej tak jak napisałeś - stworzenie tabel historycznych. Na twoim miejscu - niespecjalnie przejmowałbym się miejscem, a już na pewno nie tworzyłbym dziesięciu tabeli na dziesięć kolumn, które mogą się zmienić. Nie tędy droga - choćby i przejrzenie historii zmian w danej ofercie będzie drogą przez mękę.
Jeśli tak bardzo zależy ci na miejscu (wątpię) to w tabeli ze zmianami wypełniaj tylko te kolumny, które się zmieniły. Np.

| id | offer_id | price | description | created                 |
|----|----------|-------|-------------|-------------------------|
| 1  | 7        | 80    | opis 1      | 2021-05-11 16:06:06.149 | // pierwszy wpis
| 2  | 7        | NULL  | opis 2      | 2021-05-11 16:08:06.149 | // tu się zmienił opis
| 3  | 7        | 100   | NULL        | 2021-05-11 18:06:06.149 | // tu się zmieniła cena 
| 4  | 7        | 110   | opis 4      | 2021-05-11 19:06:06.149 | // tu się zmienił i opis, i cena

Inne sposoby na trzymanie zmian to:

  • jakiś wynalazek w stylu Debezium
  • trzymanie wszystkiego w Kafce z retencją na 9999 lat
  • po prostu kopiowanie wpisu za każdym razem, jak się zmieni
2

Skoro korzystasz z PostgreSQL to wykorzystaj jego możliwości. Takie, jakich nie mają inne bazy. Np. NEW::text który cały rekord zamienia na TEXXT i możesz to wpisać do jednej kolumny.
Ja osobiście preferuję rozwiązanie, że w audyt mam kolumny:
kto, co, kiedy, z czego i na co
Czyli Insert(update) user, field_name, insertdate, old_value, new_value
Ma to swoje zady i walety, ale jestem z tego rozwiązania zadowloony.

A tu kawałek kodu triggera PostgreSQL, który potrafi rozróżnić które kolumny zmieniły się z czego na co:

if TG_OP='UPDATE' then
  INSERT INTO tabela_zmiany(field_name, old_value, new_value)
	SELECT 
		o.key,o.value,n.value
	FROM 
		(select "key", Coalesce("value"::TEXT, ''::TEXT) "value" from json_each(row_to_json(old))) o 
		JOIN 
		(select "key", Coalesce("value"::TEXT, ''::TEXT) "value" from json_each(row_to_json(new))) n 
			ON n.key=o.key
	WHERE 
		o.value<>n.value
	;
end if;

if TG_OP='DELETE' then 
	return old; 
else
	return new;
end if;

0

SQL ponoć ma triggery czytałem

1 użytkowników online, w tym zalogowanych: 0, gości: 1