Как правильно организовать историю денежного оборота?

Question

[nepster-web]

Есть некая интересная задачка, которую я уже на протяжении очень долгого времени не могу красиво порешать.
Это реализация структуры финансового учета в проекте.

Давайте представим, что нужно сделать раздел "История денежного оборота", где будет выводиться список движения финансов с более-менее подробной информацией, например вот так:

Пользователь Вася перевел $10 пользователю Пете 
Пользователь Вася вывел $100 через систему "Приват Банк"
Пользователь Петя перевел Вам $15
Активация в тарифный план "Старт" за $25
Оплата $15 за услугу "Смена логина"
Оплата $100 за услугу "Поднять объявление в топ". 
Квартира на сутки №1, Квартира на сутки №2, Квартира на сутки №3, Квартира на сутки №4

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

Теперь более детальный пример

К примеру есть следующие типы услуг:
- Поднять объявления в топ
- Сменить логин
- Купить подписку на "тарифный план"
- Оплатить заказ товара

Задача: зафиксировать идентификаторы сущностей, которые фигурируют в заказе и отобразить из.

Рассмотрим на примере "Поднять объявления в топ" и "Купить подписку на 'тарифный план'".
Наша задача записать где-то список id из таблицы объявлений и id из таблицы тарифных планов, чтобы получить необходимые данные для рендера.

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

Тоесть если у нас задача отобразить 100 записей, то на каждую запись будет по 1 запросу и это выходит более 100 запросов в бд для рендера страницы. Как-то не хорошо.

Собственно поделитесь пожалуйста советом по реализации...

Answer 1

[Анатолий]

Mysql поддерживает if
Вы можете разделить всю историю на логические блоки:
кто сделал, что сделал, кому сделал, зачем сделал, сумма
и совать в разнобой айдишники пользователей или операций + добавить поле которое однозначно идентифицирует операцию
в итоге вы будете всегда выбирать 4 поля
перевод: от айди, 'перевел', к айди, '', 10 баксов
поднятие в топ: кто, "поднял в топ", '', '', 10 баксов
покупка подписки: кто, "купил подписку", "", "", 10 баксов
итд, заранее продумайте какие данные возможны в истории, сделайте поля на все случаи жизни (айдишники можно совмешать, пользователя и операции, потом по ифу выводить нужное

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

Можно обойтись без джойнов, выбрать все данные, отдельно взять из них айдишники нужные, и выбрать отдельно :) иногда это быстрей.

Comments

[nepster-web]

"Mysql поддерживает if" - придется дублировать логику еще на стороне базы данных. Я бы предпочел сервер баз данных вообще не трогать логикой.

"поднятие в топ: кто, "поднял в топ", '', '', 10 баксов
покупка подписки: кто, "купил подписку", "", "", 10 баксов"
Не совсем понял, но ключевой момент, ЧТО ПОДНЯЛИ В ТОМ. В нашем случае интересуют id объявлений и соответственно получить инфу, что за объявления.

"итд, заранее продумайте какие данные возможны в истории,"
Вы имеете ввиду хранить что-то типа такого:
advert_id | activation_id | ....
Тогда будет куча null, это вроде нарушает нормальную форму бд?

"И главное и самое плохое, вам придется джойнить все таблицы с данными из которых берете информацию." - не все так плохо если нужно отрендерить 100 записей на страницу и закешировать их.

"Можно обойтись без джойнов, выбрать все данные, отдельно взять из них айдишники нужные, и выбрать отдельно :) иногда это быстрей. "

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

[Анатолий]

nepster09: все по одному делать, зачем вам знать все записи поднятые в топ в одной строке? одно действие, одна запись, тогда один раз выбираете все данные из основной истории, джойните пользователей, обьявления итд, просто чтоб иметь возможность подставить айди

[nepster-web]

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

[Анатолий]

nepster09:
1. можно использовать вложенные запросы в селекте при ифе в зависимости от поля (будет поле операции) (да, с джоинами не туда меня понесло)
2. можно хранить разные виды операций в разных таблицах, и выбирать их вместе с помошью UNION
3. можно использовать вариант что я писал выше, выбирая отдельно операции, и отдельно перебирать айдишники и отдельно их выбирать (пользователь, переводы итд)

лично я бы в таком порядке пробовал, первые выбирает все данные сразу, видно что откуда идет, но может быть медленней, второй очень гибкий (можете добавлять безболезненно новые действия), но разные данные лежат в разных местах, третий это уже просто потому что надо :)

[nepster-web]

1) не подходит, так как придется знакомить базу со всякими типами денежного оборота, да и запросы такого рода меня пугают.
2) честно говоря мало знаю про UNION, в стиле, что это типа аналога JOIN, нужно будет ознакомиться. Спасибо.
3) Вы имеете ввиду взять все результаты денежного оборота, потом пройтись по ним циклом, посмотреть, что нужно еще и найти все это дело. Как-то так:
- получаем запросом 100 записей из истории
- обходим в цикле каждую запись и формируем данные для запросов. Например нужна информация для объявлений, активаций и тп.
- получаем все эти данные, затем снова прогоняемся циклом по истории и подставляем с помощью условий, что куда нужно.
Тогда будет 1 запрос на историю и по 1 запросу на "левые" сущности. Обычно их будет 2 - 3.
Я правильно Вас понял ?

[Анатолий]

nepster09: запросы такого рода (вложеные) это нормально в определенных случаях. А по пункту 3 да, будет один запрос на историю, и затем по одному на каждый тип действия с выборкой сразу всех нужных айди, и затем снова обработка массива истории для того чтоб собрать данные в нужном формате.
Union из себя представляет не ждойн как таковой, а грубо говоря склейку данных
выбираем один тип данных
выбираем второй тип данных, и лепим в конец первый выборки
выбираем третий тип данных и лепим в конец второй выборки
...
выбираем Н-ный тип данных и лепим в конец Н-1 выборки

В итоге у вас получается один результат, но из разных таблиц

Answer 2

[jus0807]

Может я не правильно проблему понял, но разве нельзя просто сделать так:
Есть таблицы: Пользователи Таблицы Действия Лог
Таблица Лог: id user_id action_id target_id datetime comment
action_id связано с таблицей таблицы - в таблицах хранятся названия таблиц, которые логируются, в Действиях - названия действий для этих таблиц типа Оплатил заказ, поднял в топ, сменил логин и тп.
В итоге чтобы отрисовать 100 записей, достаточно сделать 1 запрос с 3 join или 4 отдельных запроса.
select * from log l
join users u on u.id = l.user_id
join actions a on a.id = l.action_id
join tables t on t.id = a.table_id

В коммент можно сохранить текстовое описание действия типа "Вася удалил запись" или же diff в качестве json или какие-то варианты на эту тему.

Comments

[nepster-web]

"В коммент можно сохранить текстовое описание действия типа "Вася удалил запись" " - нельзя. Что если Вася сменит Логин на Петю, а Петя на Васю, тогда мы уже не сможем доверять истории.

+ Не очень хорошо хранить название таблиц и отталкиваться от них, так как изменение имени таблицы (например префикса) сломает структуру запросов.

[jus0807]

nepster09: Если Вася сменит логин на Петю, значит в истории будет написано что он это сделал. На момент написание лога он был Васей. Он же не меняет id своей учетки.

Если изменение имени таблицы рядовая операция то я даже и не подскажу какого-то решения которое учитывает что таблица может быть переименована по 10 раз на дню.

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

[nepster-web]

jus0807: вот я тоже уже годик над этим думаю, как-то вроде работает нормально, но когда логика чуть усложняется, то все архитектура идет коту под хвост. Под усложнением логики имеется ввиду задача отображать что оплатили, зачем и когда на одной странице.

Answer 3

[Артемий]

Создайте в базе отдельную таблицу для баланса в разрезе эккаунтов и в нее пишите начисления и оплаты и будет у Вас и баланс и история.