Разработка базы данных: расчет баланса счета
Как мне спроектировать базу данных для расчета баланса счета?
1) В настоящее время я вычисляю остаток на счете из таблицы транзакций. В моей таблице транзакций есть "описание", "сумма" и т. Д.
Затем я сложил бы все значения "суммы", и это вычислило бы баланс учетной записи пользователя.
Я показал это своему другу, и он сказал, что это не очень хорошее решение, когда моя база данных будет расти и будет замедляться???? Он сказал, что я должен создать отдельную таблицу для хранения рассчитанного баланса счета. Если это произойдет, мне придется вести две таблицы, и это рискованно, таблица баланса счета может выйти из синхронизации.
Любое предложение?
РЕДАКТИРОВАТЬ: ВАРИАНТ 2: я должен добавить дополнительный столбец в мои таблицы транзакций "Баланс". теперь мне не нужно проходить много строк данных для выполнения моих расчетов.
Например, Джон покупает кредит в 100 долларов, он в долгу 60 долларов, затем добавляет 200 долларов.
Сумма 100 долларов, остаток 100 долларов.
Сумма - 60 долларов, остаток 40 долларов.
Сумма 200 долларов, остаток 240 долларов.
9 ответов
Извечная проблема, которая никогда не была элегантно решена.
Все банковские пакеты, с которыми я работал, сохраняют баланс с лицом счета. Вычислить его на лету из истории движения немыслимо.
Правильный путь это:
- Таблица движения имеет транзакцию "начального баланса" для каждой учетной записи. Это понадобится вам через несколько лет, когда вам нужно переместить старые движения из таблицы активных движений в таблицу истории.
- У субъекта счета есть поле баланса
- В таблице движения есть триггер, который обновляет остатки на счетах для зачисленных и списанных счетов. Очевидно, он имеет контроль над обязательствами. Если у вас не может быть триггера, то должен быть уникальный модуль, который записывает движения под контролем фиксации
- У вас есть программа "Сеть безопасности", которую вы можете запустить в автономном режиме, которая пересчитывает все сальдо и отображает (и при необходимости исправляет) ошибочные сальдо. Это очень полезно для тестирования.
Некоторые системы хранят все движения как положительные числа и выражают кредит / дебет, инвертируя поля from / to или с флагом. Лично я предпочитаю кредитное поле, дебетовое поле и подписанную сумму, это значительно упрощает отслеживание разворотов.
Обратите внимание, что эти методы применяются как к денежным средствам, так и к ценным бумагам.
Операции с ценными бумагами могут быть намного более хитрыми, особенно для корпоративных действий, вам нужно будет выполнить одну транзакцию, которая обновляет один или несколько остатков денежных средств покупателя и продавца, их остатки на обеспечительных позициях и, возможно, брокера / депозитария.
Это дизайн базы данных, который я получил только с одной таблицей для хранения истории операций / транзакций. В настоящее время работает шармом на многих небольших проектах.
Это не заменяет определенный дизайн. Это универсальное решение, которое подходит для большинства приложений.
id: int стандартный идентификатор строки
операция_тип: тип операции int. платить, собирать, проценты и т. д.
source_type: int, откуда продолжается операция. целевая таблица или категория: пользователь, банк, поставщик и т. д.
source_id: int id источника в базе данных
target_type: int, к которому применяется операция. целевая таблица или категория: пользователь, банк, поставщик и т. д.
target_id: int id цели в базе данных
сумма: десятичное (подписано 19,2) значение цены положительное или отрицательное к суммированному
account_balance: десятичное (19,2 подписано) итоговое сальдо
extra_value_a: десятичное число (19,2 со знаком) [это был самый универсальный вариант без использования хранения строк], вы можете сохранить дополнительное число: процентный процент, скидку, скидку и т. д.
made_at: отметка времени
Для source_type и target_type было бы лучше использовать перечисление или таблицы appart.
Если вам нужен конкретный баланс, вы можете просто запросить последнюю операцию, отсортированную по убыванию предела созданного значения до 1. Можно выполнить запрос по источнику, цели, типу операции и т. Д.
Для повышения производительности рекомендуется сохранять текущий баланс в требуемом целевом объекте.
Вы должны хранить текущий баланс счета и постоянно обновлять его. Таблица транзакций - это просто запись того, что произошло в прошлом, и ее не следует использовать с высокой частотой просто для получения текущего баланса. Учтите, что многие запросы не просто нуждаются в балансировках, они хотят фильтровать, сортировать и группировать по ним и т. Д. Из-за потери производительности суммирования каждой транзакции, которую вы когда-либо создавали в середине сложных запросов, могут быть повреждены даже базы данных небольшого размера.,
Все обновления этой пары таблиц должны выполняться в транзакции и должны обеспечивать либо синхронизацию всего (и учетная запись никогда не будет превышать свой лимит), либо откат транзакции. В качестве дополнительной меры вы можете запускать контрольные запросы, которые периодически проверяют это.
Распространенным решением этой проблемы является поддержание (скажем) ежемесячного начального баланса в схеме моментального снимка. Расчет текущего баланса может быть сделан путем добавления транзакционных данных за месяц к ежемесячному начальному балансу. Этот подход часто применяется в пакетах учетных записей, особенно если у вас может быть конвертация валюты и переоценка.
Если у вас есть проблемы с объемом данных, вы можете архивировать старые балансы.
Кроме того, сальдо могут быть полезны для составления отчетов, если у вас нет выделенного внешнего хранилища данных или средства управленческой отчетности в системе.
Конечно, вам нужно хранить текущий баланс с каждой строкой, иначе он будет слишком медленным. Чтобы упростить разработку, вы можете использовать ограничения, чтобы вам не требовались триггеры и периодические проверки целостности данных. Я описал это здесь Денормализация для обеспечения соблюдения бизнес-правил: подведение итогов
Ваш друг неправ, а вы правы, и я бы посоветовал вам не менять вещи сейчас.
Если ваша БД из-за этого идет медленно, и после того, как вы проверили все остальное (правильная индексация), может быть полезна некоторая денормализация.
Затем вы можете поместить поле BalanceAtStartOfYear в таблицу "Счета" и суммировать только записи этого года (или любой аналогичный подход).
Но я бы, конечно, не рекомендовал такой подход заранее.
Мой подход заключается в том, чтобы хранить дебеты в столбце дебетов, кредит в столбце кредита и при получении данных создавать два массива: массив дебетов и кредита. Затем продолжайте добавлять выбранные данные в массив и сделайте это для python:
def real_insert(arr, index, value):
try:
arr[index] = value
except IndexError:
arr.insert(index, value)
def add_array(args=[], index=0):
total = 0
if index:
for a in args[: index]:
total += a
else:
for a in args:
total += a
return total
затем
for n in range(0, len(array), 1):
self.store.clear()
self.store.append([str(array[n][4])])
real_insert(self.row_id, n, array[n][0])
real_insert(self.debit_array, n, array[n][7])
real_insert(self.credit_array, n, array[n][8])
if self.category in ["Assets", "Expenses"]:
balance = add_array(self.debit_array) - add_array(self.credit_array)
else:
balance = add_array(self.credit_array) - add_array(self.debit_array)
Здесь я хотел бы предложить вам, как вы можете сохранить свой начальный баланс очень простым способом:-
Создайте триггерную функцию в таблице транзакций, которая будет вызываться только после обновления или вставки.
Создайте столбец с именем в главной таблице имен счетов, начальный баланс.
сохранить начальный баланс в массиве в столбце начального баланса в основной таблице.
вам даже не нужно использовать язык на стороне сервера, используйте этот массив хранилища, просто вы можете использовать функции массива базы данных, которые доступны в PostgreSQL.
если вы хотите пересчитать начальный баланс в массиве, просто сгруппируйте таблицу транзакций с помощью функции массива и обновите все данные в основной таблице.
Я сделал это в PostgreSQL и работает нормально.
в течение периода времени, когда ваша таблица транзакций станет тяжелой, вы можете разделить таблицу транзакций на основе даты, чтобы ускорить производительность. этот подход очень прост и не требует использования каких-либо дополнительных таблиц, которые могут снизить производительность при присоединении к таблице, поскольку меньшая таблица при объединении даст вам высокую производительность.
Простой ответ: делай все три.
Сохранить текущий баланс; и в каждой транзакции сохраняйте движение и снимок текущего баланса в данный момент времени. Это дало бы что-то дополнительное для согласования в любом аудите.
Я никогда не работал над основными банковскими системами, но я работал над системами управления инвестициями, и, по моему опыту, именно так это и делается.