Написание простой схемы банка: как мне сохранить баланс в синхронизации со своей историей транзакций?

Я пишу схему для простой банковской базы данных. Вот основные характеристики:

  • В базе данных будут храниться транзакции с пользователем и валютой.
  • Каждый пользователь имеет один баланс на каждую валюту, поэтому каждый баланс представляет собой просто сумму всех транзакций против данного пользователя и валюты.
  • Баланс не может быть отрицательным.

Банковское приложение будет связываться с его базой данных исключительно через хранимые процедуры.

Я ожидаю, что эта база данных будет принимать сотни тысяч новых транзакций в день, а также балансовые запросы на более высоком уровне. Чтобы быстро настроить весы, мне нужно предварительно их заполнить. В то же время я должен гарантировать, что баланс никогда не противоречит истории транзакций.

Мои параметры:

  1. Уделите отдельную таблицу balances и выполните одно из следующих действий:

    1. Применяйте транзакции к таблицам transactions и balances. Используйте логику TRANSACTION на моем уровне хранимой процедуры, чтобы гарантировать, что балансы и транзакции всегда синхронизируются. (Поддерживается Джек .)

    2. Примените транзакции к таблице transactions и получите триггер, который обновляет таблицу balances для меня с суммой транзакции.

    3. Примените транзакции к таблице balances и введите триггер, который добавляет новую запись в таблицу transactions для меня с суммой транзакции.

    Мне приходится полагаться на основанные на безопасности подходы, чтобы убедиться, что вне хранимых процедур не могут быть сделаны какие-либо изменения. В противном случае, например, некоторый процесс мог непосредственно вставить транзакцию в таблицу transactions, а по схеме 1.3 соответствующий баланс был бы несинхронизирован.

  2. Имеет индексированный вид balances , который агрегирует транзакции соответствующим образом. Балансы гарантируются механизмом хранения, чтобы оставаться в синхронизации со своими транзакциями, поэтому мне не нужно полагаться на основанные на безопасности подходы, чтобы гарантировать это. С другой стороны, я не могу обеспечить баланс, который будет неотрицательным больше, поскольку представления - даже индексированные представления - не могут иметь ограничения CHECK. (Поддерживается Denny .)

  3. Имейте только таблицу transactions , но с дополнительным столбцом для сохранения баланса, действующего сразу после этой транзакции. Таким образом, последняя запись транзакции для пользователя и валюты также содержит их текущий баланс. (Предлагается ниже Andrew , вариант, предложенный Гарик .)

Когда я впервые решил эту проблему, я прочитал эти два обсуждения и определил вариант 2. Для справки вы можете увидеть его реализацию здесь .

  • Вы создали или управляете такой базой данных с профилем высокой нагрузки? Каково было ваше решение этой проблемы?

  • Как вы думаете, я сделал правильный выбор дизайна? Есть ли что-нибудь, о чем я должен помнить?

    Например, я знаю, что изменения схемы в таблице transactions потребуют перестроить представление balances. Даже если я занимаюсь архивированием транзакций, чтобы сохранить базу данных небольшой (например, перемещая их где-то в другом месте и заменяя их сводкими транзакциями), необходимость перестроить представление из десятков миллионов транзакций с каждым обновлением схемы, вероятно, будет означать значительно большее время простоя на развертывание.

  • Если индексированный просмотр - это способ, как я могу гарантировать, что баланс не является отрицательным?


Архивирование транзакций:

Позвольте мне подробно рассказать о транзакциях архивирования и о «суммарных транзакциях», о которых я упоминал выше. Во-первых, регулярное архивирование будет необходимостью в такой высокой нагрузке. Я хочу поддерживать согласованность между балансами и их историями транзакций, позволяя переносить старые транзакции в другое место. Чтобы сделать это, я заменю каждую партию архивных транзакций сводкой своих сумм на пользователя и валюту.

Итак, например,этот список транзакций:

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

архивируется и заменяется следующим:

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

Таким образом, баланс с заархивированными транзакциями поддерживает полную и последовательную историю транзакций.

47 голосов | спросил Nick Chammas 10 thEurope/Moscowp30Europe/Moscow09bEurope/MoscowSat, 10 Sep 2011 04:59:04 +0400 2011, 04:59:04

7 ответов


14

Не позволяя клиентам иметь баланс менее 0 - это бизнес-правило (которое быстро изменится, поскольку сборы за такие вещи, как сквозной проект, - это то, как банки зарабатывают большую часть своих денег). Вы захотите обработать это при обработке приложения, когда строки будут вставлены в историю транзакций. Тем более, что вы можете столкнуться с некоторыми клиентами, имеющими защиту от овердрафта, а некоторые получают сборы сборы, а некоторые не допускают ввода отрицательных сумм.

До сих пор мне нравилось, где вы собираетесь с этим, но если это для реального проекта (а не для школы), должно быть чертовски много мыслимых бизнес-правил и т. д. После того, банковская система работает и там не так много места для редизайна, поскольку существуют очень конкретные законы о людях, имеющих доступ к своим деньгам.

ответил mrdenny 10 thEurope/Moscowp30Europe/Moscow09bEurope/MoscowSat, 10 Sep 2011 07:54:20 +0400 2011, 07:54:20
11

Я не знаком с бухгалтерским учетом, но я решил некоторые подобные проблемы в среде инвентарного типа. Я храню текущие итоги в той же строке с транзакцией. Я использую ограничения, так что мои данные никогда не ошибаются даже при высоком параллелизме. Я написал следующее решение тогда в 2009 году: :

Вычисление текущих сумм заведомо медленнее, независимо от того, выполняете ли вы это с помощью курсора или с треугольным соединением. Очень заманчиво денормализовать, хранить текущие итоги в столбце, особенно если вы его часто выбираете. Однако, как обычно, когда вы денормализуете, вам необходимо гарантировать целостность ваших денормализованных данных. К счастью, вы можете гарантировать целостность текущих итогов с ограничениями - до тех пор, пока вам доверяют все ваши ограничения, все ваши текущие итоги верны. Также таким образом вы можете легко убедиться, что текущий баланс (текущие итоги) никогда не отрицателен. Применение других методов также может быть очень медленным. Следующий скрипт демонстрирует технику.

 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
 
ответил A-K 16 J0000006Europe/Moscow 2012, 23:42:18
10
  

Прочитав эти два обсуждения, я решил вариант 2

Прочитав эти обсуждения, я не уверен, почему вы решили решение DRI наиболее разумным из другие варианты, которые вы очертите:

  

Применить транзакции как к таблицам транзакций, так и к балансам.   Используйте логику TRANSACTION на моем уровне хранимой процедуры, чтобы убедиться, что   балансы и транзакции всегда синхронизируются.

Такое решение имеет огромные практические преимущества, если у вас есть возможность ограничить доступ all к данным через ваш транзакционный API. Вы теряете очень важное преимущество DRI, которое гарантирует целостность базы данных, но в любой модели достаточной сложности будут существовать некоторые бизнес-правила, которые не могут быть реализованы DRI .

Я бы посоветовал использовать DRI, где это возможно, для обеспечения соблюдения бизнес-правил, не слишком сильно изгибая вашу модель, чтобы сделать это возможным:

  

Даже если я архивирую транзакции (например, перемещая их где-нибудь   else и заменяя их сводкими транзакциями)

Как только вы начнете рассматривать загрязнение вашей модели, как это, я думаю, вы переходите в область, где преимущество DRI перевешивается трудностями, которые вы вводите. Рассмотрим, например, что ошибка в вашем процессе архивирования может в теории привести к тому, что ваше золотое правило (которое уравновешивает always равное сумме транзакций) на тихое молчание с решением DRI .

Вот краткий обзор преимуществ транзакционного подхода, который я вижу:

  • Мы должны делать это в любом случае, если это вообще возможно. Независимо от того, какое решение вы выбрали для этой конкретной проблемы, это дает вам больше гибкости дизайна и контроля над вашими данными. Затем весь доступ становится «транзакционным» с точки зрения бизнес-логики, а не только с точки зрения логики базы данных.
  • Вы можете сохранить свою модель аккуратной
  • Вы можете «применять» гораздо более широкий диапазон и сложность бизнес-правил (отмечая, что концепция «принудительного исполнения» является более свободной, чем с DRI).
  • Вы все равно можете использовать DRI везде, где это необходимо, чтобы дать модели более надежную базовую целостность - и это может действовать как проверка вашей транзакционной логики.
  • Большинство проблем с производительностью, которые вас беспокоят, будут таять.
  • Представление новых требований может быть намного проще - например: сложные правила для оспариваемых транзакций могут заставить вас отказаться от чистого подхода DRI дальше по линии, что означает много потерянных усилий.
  • Разделение или архивирование исторических данных становится гораздо менее рискованным и болезненным.

- изменить

Чтобы разрешить архивирование без добавления сложности или риска, вы могли бы сохранить сводные строки в отдельной сводной таблице, генерируемой непрерывно (заимствование из @Andrew и @Garik)

Например, если сводки ежемесячно:

  • каждый раз, когда есть транзакция (через ваш API), есть соответствующее обновление или вставка в сводную таблицу
  • сводная таблица архивируется never , но транзакции с архивированием становятся такими же просто a (удалить или удалить раздел?)
  • каждая строка в сводной таблице включает «начальный баланс» и «сумма»
  • контрольные ограничения, такие как «начальный баланс» + «сумма»> 0 и «начальный баланс»> 0, могут быть применены к сводной таблице
  • сводные строки могут быть вставлены в ежемесячную партию, чтобы упростить блокировку последней итоговой строки (всегда будет строка для текущего месяца).
ответил Jack Douglas 12 thEurope/Moscowp30Europe/Moscow09bEurope/MoscowMon, 12 Sep 2011 11:45:38 +0400 2011, 11:45:38
9

Несколько иной подход (похожий на ваш параметр 2nd ), чтобы рассмотреть, состоит в том, чтобы иметь только таблицу транзакций с определением:

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

Вам также может понадобиться идентификатор транзакции /Заказ, чтобы вы могли обрабатывать две транзакции с той же датой и улучшать поисковый запрос.

Чтобы получить текущий баланс, все, что вам нужно получить, это последняя запись.

Способы получения последней записи : сильный>

 /* 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
 

Минусы:

  • При вставке транзакции из последовательности (то есть: для исправления проблема /неправильный стартовый баланс), вам может потребоваться каскадное обновление для всех последующих транзакций.
  • Транзакции для пользователя /валюты необходимо будет сериализовать для поддержания точного баланса.

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

Плюсы:

  • Вам больше не нужно поддерживать две отдельные таблицы ...
  • Вы можете легко проверить баланс, и когда баланс не синхронизируется, вы можете точно определить, когда он вышел из строя, поскольку история транзакций становится самодокументированной.

Изменить: некоторые примеры запросов на поиск текущего баланса и выделение con (спасибо @Jack Douglas)

ответил Andrew Bickerton 13 thEurope/Moscowp30Europe/Moscow09bEurope/MoscowTue, 13 Sep 2011 11:35:39 +0400 2011, 11:35:39
5

Ник.

Основная идея заключается в сохранении записей баланса и транзакций в одной таблице. Это случилось исторически, я думал. Таким образом, в этом случае мы можем получить баланс, просто найдя последнюю итоговую запись.

  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 
 

Лучшим вариантом является уменьшение количества сводных записей. У нас может быть одна запись баланса в конце (и /или начале) дня. Как вы знаете, каждый банк имеет operational day, чтобы открыть и закрыть его, чтобы выполнить некоторые сводные операции за этот день. Это позволяет нам легко вычислить интерес , используя дневную балансовую запись, например:

 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)
 

Luck.

ответил garik 14 thEurope/Moscowp30Europe/Moscow09bEurope/MoscowWed, 14 Sep 2011 22:08:46 +0400 2011, 22:08:46
3

В соответствии с вашими требованиями вариант 1 окажется лучшим. Хотя мне бы хотелось, чтобы мой дизайн разрешал вставки в таблицу транзакций. И иметь триггер в таблице транзакций, чтобы обновить таблицу баланса в реальном времени. Вы можете использовать разрешения базы данных для контроля доступа к этим таблицам.

В этом подходе гарантируется синхронизация реального времени с таблицей транзакций. И не имеет значения, используются ли хранимые процедуры или psql или jdbc. При необходимости вы можете проверить свой отрицательный баланс. Производительность не будет проблемой. Чтобы получить баланс в реальном времени, это однопользовательский запрос.

Архивирование не повлияет на этот подход. Вы можете иметь еженедельную, ежемесячную, годовую сводную таблицу, если это необходимо для таких вещей, как отчеты.

ответил Elan Fisoc 22 ThuEurope/Moscow2011-12-22T22:18:43+04:00Europe/Moscow12bEurope/MoscowThu, 22 Dec 2011 22:18:43 +0400 2011, 22:18:43
2

В Oracle вы можете сделать это, используя только таблицу транзакций с быстрым обновляемым материализованным представлением на нем, что делает агрегацию для формирования баланса. Вы определяете триггер на материализованном представлении. Если Materialized View определен с помощью ON ON COMMIT, он эффективно предотвращает добавление /изменение данных в базовых таблицах. Триггер обнаруживает достоверные данные [in] и вызывает исключение, когда он откатывается от транзакции. Хороший пример здесь http://www.sqlsnippets.com/en/topic-12896. HTML

Я не знаю sqlserver, но, возможно, у него есть аналогичный вариант?

ответил ik_zelf 14 thEurope/Moscowp30Europe/Moscow09bEurope/MoscowWed, 14 Sep 2011 13:30:33 +0400 2011, 13:30:33

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132