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

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

Возникла мысль упростить учет бонусов.
Например зачисляется 100 бонусов со сроком действия 100 суток.
1. Обновляется баланс бонусного счета: update bonus set amount=amount+100 where bonus_id=1
2. Во вспомогательную таблицу добавляется запись со сроком окончания бонуса: insert into clearing (date, amount, bonus_id) values (sysdate+100, 100, 1)
3. В БД добавляется ежедневное задание, которое проверяет даты в таблице clearing и для наступивших строк уменьшает значение баланса бонусного счета.

Но тут сложность в том, как правильно учитывать факт использования бонуса (и удаления его из таблицы clearing).
Хотелось бы убедиться, что я ничего не упустил:
1. Используется 150 бонусов. QTY=150
2. Обновляю баланс бонусного счета: update bonus set amount=amount-QTY where bonus_id=1
3. Получаю список временных зачислений, отсортированных по дате: select * from clearing where bonus_id=1 and date>sysdate order by date
4a. Если в первой строке amount>QTY, то для этой строки уменьшаю amount на QTY.
4b. Если в первой строке amount<=QTY, то удаляю это строчку и уменьшаю QTY=QTY-amount, после чего повторяю с пункта 4a.

Все верно?

Leonid KudryavtsevКакие остались? Сгорающие или нормальные? Резонно считать, что человек в первую очередь тратил сгорающие
В первую очередь тратятся бонусы с ближайшей датой (order by date).
Поэтому несгораемые будут использоваться только если сгораемых более не осталось.

Leonid KudryavtsevЕще через день доложил еще 40 несгорающих. Получилось на счете 60.... прошло еще 98 дней (100 дней с момента акции)...
В приведенном примере 20 оставшихся — это несгораемые, к которым добавляется еще 40.
То есть на счете 60 бонусов без ограничения срока действия.
Те бонусы, у которых срок использования был 100 дней, уже полностью потрачены, про них можно забыть.

L_argoЭто самый правильный метод.
Во втором варианте запросы и реализация гораздо проще, транзакционный (партионный) учет.
Единственный существенный недостаток — в этом варианте довольно сложно получить баланс счета на произвольный момент времени в прошлом. Но я не могу придумать, зачем в бонусной программе ретроспектива баланса.

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

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

SergueiЧто тут такого страшного в хранении исходных данных по бонусами и вычислении баланса на лету?
Клиентов, бонусов и начислений будет много. Скажем, порядка 1кк записей в год.
И на мой взгляд select bonus_id, amount from bonus всегда будет быстрее и лучше, чем select bonus_id, sum(amount) from charges where sysdate between sdate and edate group by bonus_id.
При втором подходе вычитать бонусы (списывать) нужно будет раз в сутки и алгоритм достаточно прозрачный.