Portal aukcyjny - jak przechowywać zmiany danych

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?

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();

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.

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.

Może podejście z event sourcing?

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.

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

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;

SQL ponoć ma triggery czytałem