Pisanie prostego schematu bankowego: Jak zachować synchronizację salda z historią transakcji?

Piszę schemat prostej bazy danych banku. Oto podstawowe specyfikacje:

• Baza danych będzie przechowywać transakcje z użytkownikiem i walutą.
• Każdy użytkownik ma jedno saldo na walutę, więc każde saldo jest po prostu sumą wszystkich transakcji wobec danego użytkownika i waluty.
• Saldo nie może być ujemne.

Aplikacja bankowa będzie komunikować się ze swoją bazą danych wyłącznie poprzez procedury składowane.

Oczekuję, że ta baza danych będzie akceptować setki tysięcy nowych transakcji dziennie, a także wyrównywać zapytania o wyższym rzędzie wielkości. Aby szybko obsłużyć salda, muszę je wstępnie zsumować. Jednocześnie muszę zagwarantować, że saldo nigdy nie będzie sprzeczne z historią transakcji.

Moje opcje to:

1. Utwórz osobną balancestabelę i wykonaj jedną z następujących czynności:

   1. Zastosuj transakcje do tabel transactionsi balances. Użyj TRANSACTIONlogiki w mojej warstwie procedur składowanych, aby upewnić się, że salda i transakcje są zawsze zsynchronizowane. (Obsługiwane przez Jacka ).

   2. Zastosuj transakcje do transactionstabeli i uruchom wyzwalacz, który zaktualizuje balancestabelę dla mnie o kwotę transakcji.

   3. Zastosuj transakcje do balancestabeli i uruchom wyzwalacz, który doda transactionsdla mnie nowy wpis w tabeli z kwotą transakcji.

   Muszę polegać na podejściach opartych na bezpieczeństwie, aby upewnić się, że nie można wprowadzić żadnych zmian poza procedurami przechowywanymi. W przeciwnym razie, na przykład, jakiś proces mógłby bezpośrednio wstawić transakcję do transactionstabeli, a zgodnie ze schematem 1.3odpowiednie saldo nie byłoby zsynchronizowane.

2. Mieć balanceswidok indeksowany, który odpowiednio agreguje transakcje. Mechanizmy magazynowania gwarantują, że salda są zsynchronizowane z ich transakcjami, więc nie muszę polegać na podejściach opartych na bezpieczeństwie, aby to zagwarantować. Z drugiej strony nie mogę już wymuszać, aby salda były nieujemne, ponieważ widoki - nawet widoki indeksowane - nie mogą mieć CHECKograniczeń. (Obsługiwany przez Denny ).

3. Miej tylko transactionstabelę, ale z dodatkową kolumną do przechowywania salda efektywnego zaraz po wykonaniu transakcji. Zatem najnowszy rekord transakcji dla użytkownika i waluty zawiera również ich bieżące saldo. (Sugerowane poniżej przez Andrew ; wariant zaproponowany przez garik .)

Kiedy po raz pierwszy poradziłem sobie z tym problemem, przeczytałem te dwie dyskusje i zdecydowałem się na opcję 2. W celach informacyjnych możesz zobaczyć jego wdrożenie od podstaw .

• Czy zaprojektowałeś lub zarządzałeś taką bazą danych o profilu wysokiego obciążenia? Jakie było twoje rozwiązanie tego problemu?

• Czy uważasz, że dokonałem właściwego wyboru projektu? Czy jest coś, o czym powinienem pamiętać?

  Na przykład wiem, że zmiany schematu w transactionstabeli będą wymagać odbudowania balanceswidoku. Nawet jeśli archiwizuję transakcje, aby baza danych była niewielka (np. Przenosząc je gdzieś indziej i zastępując je transakcjami podsumowującymi), konieczność odbudowania widoku z dziesiątek milionów transakcji przy każdej aktualizacji schematu prawdopodobnie oznacza znacznie dłuższy czas przestoju na wdrożenie.

• Jeśli dobrym pomysłem jest widok indeksowany, jak mogę zagwarantować, że saldo nie będzie ujemne?

Archiwizacja transakcji:

Pozwólcie mi rozwinąć nieco kwestię archiwizacji transakcji i wspomnianych powyżej „transakcji podsumowujących”. Po pierwsze, regularna archiwizacja będzie niezbędna w takim systemie o dużym obciążeniu. Chcę zachować spójność między saldami i ich historiami transakcji, jednocześnie umożliwiając przenoszenie starych transakcji gdzie indziej. Aby to zrobić, zastąpię każdą partię zarchiwizowanych transakcji podsumowaniem ich kwot na użytkownika i walutę.

Na przykład ta lista transakcji:

user_id    currency_id      amount    is_summary
------------------------------------------------
      3              1       10.60             0
      3              1      -55.00             0
      3              1      -12.12             0

jest archiwizowany i zastępowany następującym:

user_id    currency_id      amount    is_summary
------------------------------------------------
      3              1      -56.52             1

W ten sposób saldo z zarchiwizowanymi transakcjami zachowuje pełną i spójną historię transakcji.

Nie jestem zaznajomiony z rachunkowością, ale rozwiązałem kilka podobnych problemów w środowiskach typu magazyn. Przechowuję sumy bieżące w tym samym wierszu z transakcją. Używam ograniczeń, dzięki czemu moje dane nigdy nie są błędne, nawet przy wysokiej współbieżności. W 2009 roku napisałem następujące rozwiązanie :

Obliczanie sum całkowitych jest notorycznie wolne, niezależnie od tego, czy robisz to za pomocą kursora, czy za pomocą połączenia trójkątnego. Denormalizacja jest bardzo kusząca, aby przechowywać bieżące sumy w kolumnie, szczególnie jeśli często ją wybierasz. Jednak, jak zwykle podczas denormalizacji, należy zagwarantować integralność zdormalizowanych danych. Na szczęście możesz zagwarantować integralność działających sum z ograniczeniami - dopóki wszystkie ograniczenia są zaufane, wszystkie działające sumy są poprawne. W ten sposób możesz łatwo upewnić się, że bieżące saldo (sumy bieżące) nigdy nie jest ujemne - egzekwowanie innymi metodami może być również bardzo wolne. Poniższy skrypt demonstruje technikę.

CREATE TABLE Data.Inventory(InventoryID INT NOT NULL IDENTITY,
  ItemID INT NOT NULL,
  ChangeDate DATETIME NOT NULL,
  ChangeQty INT NOT NULL,
  TotalQty INT NOT NULL,
  PreviousChangeDate DATETIME NULL,
  PreviousTotalQty INT NULL,
  CONSTRAINT PK_Inventory PRIMARY KEY(ItemID, ChangeDate),
  CONSTRAINT UNQ_Inventory UNIQUE(ItemID, ChangeDate, TotalQty),
  CONSTRAINT UNQ_Inventory_Previous_Columns 
     UNIQUE(ItemID, PreviousChangeDate, PreviousTotalQty),
  CONSTRAINT FK_Inventory_Self FOREIGN KEY(ItemID, PreviousChangeDate, PreviousTotalQty)
    REFERENCES Data.Inventory(ItemID, ChangeDate, TotalQty),
  CONSTRAINT CHK_Inventory_Valid_TotalQty CHECK(
         TotalQty >= 0 
     AND (TotalQty = COALESCE(PreviousTotalQty, 0) + ChangeQty)
  ),
  CONSTRAINT CHK_Inventory_Valid_Dates_Sequence CHECK(PreviousChangeDate < ChangeDate),
  CONSTRAINT CHK_Inventory_Valid_Previous_Columns CHECK(
        (PreviousChangeDate IS NULL AND PreviousTotalQty IS NULL)
     OR (PreviousChangeDate IS NOT NULL AND PreviousTotalQty IS NOT NULL)
  )
);

-- beginning of inventory for item 1
INSERT INTO Data.Inventory(ItemID,
  ChangeDate,
  ChangeQty,
  TotalQty,
  PreviousChangeDate,
  PreviousTotalQty)
VALUES(1, '20090101', 10, 10, NULL, NULL);

-- cannot begin the inventory for the second time for the same item 1
INSERT INTO Data.Inventory(ItemID,
  ChangeDate,
  ChangeQty,
  TotalQty,
  PreviousChangeDate,
  PreviousTotalQty)
VALUES(1, '20090102', 10, 10, NULL, NULL);


Msg 2627, Level 14, State 1, Line 10

Violation of UNIQUE KEY constraint 'UNQ_Inventory_Previous_Columns'. 
Cannot insert duplicate key in object 'Data.Inventory'.

The statement has been terminated.


-- add more
DECLARE @ChangeQty INT;
SET @ChangeQty = 5;

INSERT INTO Data.Inventory(ItemID,
  ChangeDate,
  ChangeQty,
  TotalQty,
  PreviousChangeDate,
  PreviousTotalQty)

SELECT TOP 1 ItemID, '20090103', @ChangeQty, TotalQty + @ChangeQty, ChangeDate, TotalQty
  FROM Data.Inventory
  WHERE ItemID = 1
  ORDER BY ChangeDate DESC;

SET @ChangeQty = 3;

INSERT INTO Data.Inventory(ItemID,
  ChangeDate,
  ChangeQty,
  TotalQty,
  PreviousChangeDate,
  PreviousTotalQty)

SELECT TOP 1 ItemID, '20090104', @ChangeQty, TotalQty + @ChangeQty, ChangeDate, TotalQty
  FROM Data.Inventory
  WHERE ItemID = 1
  ORDER BY ChangeDate DESC;

SET @ChangeQty = -4;

INSERT INTO Data.Inventory(ItemID,
  ChangeDate,
  ChangeQty,
  TotalQty,
  PreviousChangeDate,
  PreviousTotalQty)

SELECT TOP 1 ItemID, '20090105', @ChangeQty, TotalQty + @ChangeQty, ChangeDate, TotalQty
  FROM Data.Inventory
  WHERE ItemID = 1
  ORDER BY ChangeDate DESC;

-- try to violate chronological order
SET @ChangeQty = 5;

INSERT INTO Data.Inventory(ItemID,
  ChangeDate,
  ChangeQty,
  TotalQty,
  PreviousChangeDate,
  PreviousTotalQty)

SELECT TOP 1 ItemID, '20081231', @ChangeQty, TotalQty + @ChangeQty, ChangeDate, TotalQty
  FROM Data.Inventory
  WHERE ItemID = 1
  ORDER BY ChangeDate DESC;

Msg 547, Level 16, State 0, Line 4

The INSERT statement conflicted with the CHECK constraint 
"CHK_Inventory_Valid_Dates_Sequence". 
The conflict occurred in database "Test", table "Data.Inventory".

The statement has been terminated.

SELECT ChangeDate,
  ChangeQty,
  TotalQty,
  PreviousChangeDate,
  PreviousTotalQty
FROM Data.Inventory ORDER BY ChangeDate;

ChangeDate              ChangeQty   TotalQty    PreviousChangeDate      PreviousTotalQty
----------------------- ----------- ----------- ----------------------- -----
2009-01-01 00:00:00.000 10          10          NULL                    NULL
2009-01-03 00:00:00.000 5           15          2009-01-01 00:00:00.000 10
2009-01-04 00:00:00.000 3           18          2009-01-03 00:00:00.000 15
2009-01-05 00:00:00.000 -4          14          2009-01-04 00:00:00.000 18


-- try to change a single row, all updates must fail
UPDATE Data.Inventory SET ChangeQty = ChangeQty + 2 WHERE InventoryID = 3;
UPDATE Data.Inventory SET TotalQty = TotalQty + 2 WHERE InventoryID = 3;

-- try to delete not the last row, all deletes must fail
DELETE FROM Data.Inventory WHERE InventoryID = 1;
DELETE FROM Data.Inventory WHERE InventoryID = 3;

-- the right way to update
DECLARE @IncreaseQty INT;

SET @IncreaseQty = 2;

UPDATE Data.Inventory 
SET 
     ChangeQty = ChangeQty 
   + CASE 
        WHEN ItemID = 1 AND ChangeDate = '20090103' 
        THEN @IncreaseQty 
        ELSE 0 
     END,
  TotalQty = TotalQty + @IncreaseQty,
  PreviousTotalQty = PreviousTotalQty + 
     CASE 
        WHEN ItemID = 1 AND ChangeDate = '20090103' 
        THEN 0 
        ELSE @IncreaseQty 
     END
WHERE ItemID = 1 AND ChangeDate >= '20090103';

SELECT ChangeDate,
  ChangeQty,
  TotalQty,
  PreviousChangeDate,
  PreviousTotalQty
FROM Data.Inventory ORDER BY ChangeDate;

ChangeDate              ChangeQty   TotalQty    PreviousChangeDate      PreviousTotalQty
----------------------- ----------- ----------- ----------------------- ----------------
2009-01-01 00:00:00.000 10          10          NULL                    NULL
2009-01-03 00:00:00.000 7           17          2009-01-01 00:00:00.000 10
2009-01-04 00:00:00.000 3           20          2009-01-03 00:00:00.000 17
2009-01-05 00:00:00.000 -4          16          2009-01-04 00:00:00.000 20

Nie zezwalanie klientom na saldo mniejsze niż 0 jest regułą biznesową (która zmienia się szybko, ponieważ opłaty za np. Przekroczenie salda są sposobem, w jaki banki zarabiają większość swoich pieniędzy). Będziesz chciał poradzić sobie z tym podczas przetwarzania aplikacji, gdy wiersze zostaną wstawione do historii transakcji. Zwłaszcza, że ​​niektórzy klienci mogą korzystać z ochrony w rachunku bieżącym, a niektórzy pobierają opłaty, a niektórzy nie pozwalają na wprowadzenie ujemnych kwot.

Jak dotąd podoba mi się to, dokąd zmierzasz, ale jeśli chodzi o rzeczywisty projekt (nie szkołę), trzeba dużo myśleć o regułach biznesowych itp. Po uruchomieniu systemu bankowego a w bieganiu nie ma zbyt wiele miejsca na przeprojektowanie, ponieważ istnieją bardzo szczegółowe prawa dotyczące osób mających dostęp do swoich pieniędzy.

Nieco innym podejściem (podobnym do drugiej opcji) do rozważenia jest posiadanie tylko tabeli transakcji z definicją:

CREATE TABLE Transaction (
      UserID              INT
    , CurrencyID          INT 
    , TransactionDate     DATETIME  
    , OpeningBalance      MONEY
    , TransactionAmount   MONEY
);

Możesz także chcieć mieć identyfikator / zamówienie transakcji, abyś mógł obsługiwać dwie transakcje z tą samą datą i ulepszyć zapytanie dotyczące wyszukiwania.

Aby uzyskać bieżące saldo, wystarczy ostatni rekord.

Metody uzyskania ostatniego rekordu :

/* For a single User/Currency */
Select TOP 1 *
FROM dbo.Transaction
WHERE UserID = 3 and CurrencyID = 1
ORDER By TransactionDate desc

/* For multiple records ie: to put into a view (which you might want to index) */
SELECT
    C.*
FROM
    (SELECT 
        *, 
        ROW_NUMBER() OVER (
           PARTITION BY UserID, CurrencyID 
           ORDER BY TransactionDate DESC
        ) AS rnBalance 
    FROM Transaction) C
WHERE
    C.rnBalance = 1
ORDER BY
    C.UserID, C.CurrencyID

Cons:

• Podczas wstawiania transakcji poza kolejnością (np .: w celu skorygowania problemu / nieprawidłowego salda początkowego) może być konieczne kaskadowe aktualizowanie wszystkich kolejnych transakcji.

• Transakcje dla użytkownika / waluty będą musiały zostać zserializowane, aby zachować dokładną równowagę.

  -- Example of getting the current balance and locking the 
  -- last record for that User/Currency.
  -- This lock will be freed after the Stored Procedure completes.
  SELECT TOP 1 @OldBalance = OpeningBalance + TransactionAmount  
  FROM dbo.Transaction with (rowlock, xlock)   
  WHERE UserID = 3 and CurrencyID = 1  
  ORDER By TransactionDate DESC;

Plusy:

• Nie musisz już utrzymywać dwóch oddzielnych tabel ...
• Możesz z łatwością zweryfikować saldo, a kiedy saldo zsynchronizuje się, możesz dokładnie określić, kiedy wyleciało ono z równowagi, gdy historia transakcji staje się dokumentacją.

Edycja: Kilka przykładowych zapytań dotyczących pobierania aktualnego salda i podświetlenia con (Thanks @Jack Douglas)

Po przeczytaniu tych dwóch dyskusji zdecydowałem się na opcję 2

Po przeczytaniu również tych dyskusji nie jestem pewien, dlaczego zdecydowałeś się na rozwiązanie DRI w stosunku do najbardziej sensownej z innych opcji, które przedstawisz:

Zastosuj transakcje zarówno do tabeli transakcji, jak i sald. Użyj logiki TRANSAKCJA w mojej warstwie procedury składowanej, aby upewnić się, że salda i transakcje są zawsze zsynchronizowane.

Takie rozwiązanie ma ogromne praktyczne korzyści, jeśli masz luksus ograniczając wszystkie dostępu do danych za pośrednictwem interfejsu API transakcyjnej. Tracisz bardzo ważną zaletę DRI, czyli integralność gwarantowana przez bazę danych, ale w każdym modelu o wystarczającej złożoności będą pewne reguły biznesowe, których DRI nie będzie w stanie egzekwować .

Radzę używać DRI tam, gdzie to możliwe, aby egzekwować reguły biznesowe bez zbytniego wyginania modelu, aby było to możliwe:

Nawet jeśli archiwizuję transakcje (np. Przenosząc je gdzie indziej i zastępując je transakcjami podsumowującymi)

Gdy tylko zaczniesz rozważać zanieczyszczenie swojego modelu w ten sposób, myślę, że przenosisz się do obszaru, w którym korzyści wynikające z DRI przeważają nad trudnościami, które wprowadzasz. Rozważmy na przykład, że błąd w procesie archiwizacji teoretycznie może spowodować, że twoja złota reguła (która zawsze będzie równa sumie transakcji) po cichu złamie się z rozwiązaniem DRI .

Oto podsumowanie zalet podejścia transakcyjnego, jakie widzę:

• I tak powinniśmy to robić, jeśli to w ogóle możliwe. Niezależnie od tego, jakie rozwiązanie wybierzesz dla tego konkretnego problemu, zapewnia ono większą elastyczność projektowania i kontrolę nad danymi. Cały dostęp staje się wówczas „transakcyjny” pod względem logiki biznesowej, a nie tylko logiki bazy danych.
• Możesz zachować swój model w porządku
• Możesz „wymusić” znacznie szerszy zakres i złożoność reguł biznesowych (zauważając, że pojęcie „egzekwowania” jest luźniejsze niż w przypadku DRI)
• Nadal możesz używać DRI wszędzie tam, gdzie jest to praktyczne, aby nadać modelowi solidniejszą podstawową integralność - a to może działać jako sprawdzenie logiki transakcyjnej
• Większość problemów z wydajnością, które Cię niepokoją, zniknie
• Wprowadzenie nowych wymagań może być znacznie łatwiejsze - na przykład: złożone reguły dotyczące spornych transakcji mogą zmusić Cię do rezygnacji z czystego podejścia DRI w dalszej linii, co oznacza dużo zmarnowanego wysiłku
• Partycjonowanie lub archiwizacja danych historycznych staje się znacznie mniej ryzykowne i bolesne

--edytować

Aby umożliwić archiwizację bez zwiększania złożoności lub ryzyka, możesz pozostawić wiersze podsumowań w osobnej tabeli podsumowań, generowanej w sposób ciągły (pożyczki od @Andrew i @Garik)

Na przykład, jeśli podsumowania są miesięczne:

• za każdym razem, gdy występuje transakcja (za pośrednictwem interfejsu API), następuje odpowiednia aktualizacja lub wstawienie do tabeli podsumowań
• tabela podsumowania nigdy nie jest archiwizowana, ale archiwizowanie transakcji staje się tak proste, jak usunięcie (lub usunięcie partycji?)
• każdy wiersz w tabeli podsumowującej zawiera „saldo otwarcia” i „kwotę”
• sprawdzić ograniczenia, takie jak „bilans otwarcia” + „kwota”> 0 i „bilans otwarcia”> 0, można zastosować do tabeli podsumowań
• wiersze podsumowania można wstawiać do partii miesięcznej, aby ułatwić blokowanie ostatniego wiersza podsumowania (zawsze będzie wiersz dla bieżącego miesiąca)

Nacięcie.

Główną ideą jest przechowywanie rekordów sald i transakcji w tej samej tabeli. To się zdarzyło historycznie, myślałem. Więc w tym przypadku możemy uzyskać równowagę po prostu przez zlokalizowanie ostatniego rekordu podsumowującego.

 id   user_id    currency_id      amount    is_summary (or record_type)
----------------------------------------------------
  1       3              1       10.60             0
  2       3              1       10.60             1    -- summary after transaction 1
  3       3              1      -55.00             0
  4       3              1      -44.40             1    -- summary after transactions 1 and 3
  5       3              1      -12.12             0
  6       3              1      -56.52             1    -- summary after transactions 1, 3 and 5 

Lepszym wariantem jest zmniejszanie liczby rekordów podsumowań. Możemy mieć jeden bilans na koniec (i / lub na początku) dnia. Jak wiadomo, każdy bank musi operational dayotworzyć i zamknąć, aby wykonać kilka operacji podsumowujących na ten dzień. Pozwala nam łatwo obliczyć odsetki , wykorzystując codzienny rekord salda, na przykład:

user_id    currency_id      amount    is_summary    oper_date
--------------------------------------------------------------
      3              1       10.60             0    01/01/2011 
      3              1      -55.00             0    01/01/2011
      3              1      -44.40             1    01/01/2011 -- summary at the end of day (01/01/2011)
      3              1      -12.12             0    01/02/2011
      3              1      -56.52             1    01/02/2011 -- summary at the end of day (01/02/2011)

Szczęście.

W zależności od twoich wymagań opcja 1 wydaje się najlepsza. Chociaż chciałbym, aby mój projekt pozwalał tylko na wstawianie do tabeli transakcji. I mieć wyzwalacz w tabeli transakcji, aby zaktualizować tabelę salda w czasie rzeczywistym. Możesz użyć uprawnień do bazy danych, aby kontrolować dostęp do tych tabel.

W tym podejściu saldo w czasie rzeczywistym gwarantuje synchronizację z tabelą transakcji. I nie ma znaczenia, czy używane są procedury składowane, psql czy jdbc. W razie potrzeby możesz sprawdzić saldo ujemne. Wydajność nie będzie problemem. Aby uzyskać równowagę w czasie rzeczywistym, jest to zapytanie pojedyncze.

Archiwizacja nie wpłynie na to podejście. Możesz mieć tygodniową, miesięczną, roczną tabelę podsumowań, także w razie potrzeby, np. W przypadku raportów.

W Oracle można to zrobić przy użyciu tylko tabeli transakcji z szybkim, odświeżalnym widokiem zmaterializowanym, który dokonuje agregacji w celu utworzenia salda. Zdefiniuj wyzwalacz w widoku zmaterializowanym. Jeśli widok zmaterializowany jest zdefiniowany jako „ON COMMIT”, skutecznie zapobiega dodawaniu / modyfikowaniu danych w tabelach podstawowych. Wyzwalacz wykrywa prawidłowe dane [in] i zgłasza wyjątek, w którym wycofuje transakcję. Dobrym przykładem jest tutaj http://www.sqlsnippets.com/en/topic-12896.html

Nie znam sqlserver, ale może ma podobną opcję?