CODEWITHSUNDEEP
mysqlsqldatabasedatabase-designdatabase-schema
Ryan
Ryan

Reputation: 10101

Handle expiry credits in a transaction table?

Consider table Credits 

 CREATE TABLE `Credits` (
  `UserID` int(11) unsigned NOT NULL DEFAULT '0',
  `Amount` int(11) NOT NULL DEFAULT '0',
  `Created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `Expire` datetime NOT NULL DEFAULT '9999-12-31 23:59:59'
)

With data:

UserID    Amount    Created       Expire
1         10        2016-01-14    2016-05-30
1         -2        2016-02-04    9999-12-31
1         3         2016-06-01    2016-09-30

Without the Expiry handing, to get the current amount of a user, it can be handled by a simple select

 SELECT SUM(Amount) FROM Credits WHERE UserID = 1;

Now, I need to write a SELECT query, with an input parameter of date, and able to get the usable amount of credits at that time, like the following..

At..

Is it possible with just the above schema? Or I need to add extra field?

SQLFiddle: http://sqlfiddle.com/#!9/3a52b/3

Upvotes: 4

Views: 1593

Answers (4)

Jeffrey Hantin
Jeffrey Hantin

Reputation: 36494

There's something of a design trap here. If someone has credits that expire on different dates, you need some sort of logic to work out exactly which credits were consumed - it matters, because the expiry could produce different balance outcomes depending on which credits were selected. If expenditures can be refunded, this becomes even messier.

Therefore, I suggest breaking this up into two tables. I have also taken the liberty of making some fields NOT NULL without DEFAULT - that is, making them mandatory to supply on INSERT - and dropping the identifier quoting for clarity.

CREATE TABLE Transactions (
  TransactionID int(11) unsigned NOT NULL AUTO_INCREMENT,
  Timestamp datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  Caption nvarchar(50) NOT NULL,
  PRIMARY KEY (TransactionID)
)

CREATE TABLE Credits (
  UserID int(11) unsigned NOT NULL,
  Amount int(11) NOT NULL,
  Expire datetime NOT NULL DEFAULT '9999-12-31 23:59:59',
  ProducingTransactionID int(11) unsigned NOT NULL REFERENCES ,
  ConsumingTransactionID int(11) unsigned NULL,
  PRIMARY KEY (UserID, Amount, Expire, ProducingTransaction),
  INDEX (UserID, ConsumingTransaction, Expire),
  FOREIGN KEY (SourceTransactionID) REFERENCES Transactions (TransactionID),
  FOREIGN KEY (SinkTransactionID) REFERENCES Transactions (TransactionID),
)

The idea here is that transactions are forced to be explicit about exactly which credits they are affecting, and these explicit choices are recorded. Credits cannot be added without a link back to the SourceTransactionID they are sourced from; when credits are used, the SinkTransactionID is simply populated with a link to the transaction in which they are used, and only credits where SinkTransactionID is null are potentially available balance.

Note that a Credits row should not be updated in any way other than to set SinkTransactionID, and therefore cannot be partially consumed by each of multiple transactions - instead, a transaction that wants to sink only part of a Credits row needs to insert a new Credits row containing the "change" on the partially used row and referencing itself as the source.

Point-in-time balance queries become slightly more complex, since now you have to join Transactions to filter out transactions that occurred after the intended point-in-time.

Upvotes: 2

user4003407
user4003407

Reputation: 22122

When you make transaction with negative Amount, you should find corresponding transaction with positive Amount and set same expiration date. So that, you store expiration date of credits you spend. Your example table will look like this:

UserID    Amount    Created       Expire
1         10        2016-01-14    2016-05-30
1         -2        2016-02-04    2016-05-30
1         3         2016-06-01    2016-09-30

That makes query for balance on any particular date look as following:

SELECT SUM(Amount) FROM Credits WHERE UserID = 1 and @date between Created and Expire;

Notice, you may have to split one transaction with negative amount to cover credits with different expiration date. For example, you have following table:

UserID    Amount    Created       Expire
1         10        2016-01-14    2016-05-30
1         10        2016-02-04    2016-06-30

and you want to make transaction with Amount=-15, then you need to make two records:

UserID    Amount    Created       Expire
1         -10       2016-04-26    2016-05-30
1         -5        2016-04-26    2016-06-30

To find out not yet spend or expired credits along with their expiration date, you can use following query:

select sum(Amount) as Amount, Expire
from Credits
where UserID = 1 and curdate() <= Expire
group by Expire
having sum(Amount) > 0
order by Expire

Upvotes: 2

Mohsen Heydari
Mohsen Heydari

Reputation: 7284

SELECT 
CASE WHEN SUM(c.amount) > 0 then SUM(c.amount) else 0 end as total_amount
FROM Credits c 
where c.userId = 1 and c.Expire <= CURDATE() -- or any date

Sqlfiddle demo.

Upvotes: 0

Ankit Agrawal
Ankit Agrawal

Reputation: 2454

SELECT SUM(t.Amount) 
from (select case amount 
             when current_date>expiry_date then 1
             else 0
             end as amount
      FROM Credits WHERE UserID ＝ 1 AND Expire <= CURDATE()
     )t.

Upvotes: 0

Related Questions