Efficient database design for billing

Question

I've a simple requirement but with little experience in designing.

Each client request to rotate a photo will cost 1 cent. I need to add each request in a table for history purpose:

CREATE TABLE Rotate_Photo
(
license VARCHAR(35),
reqtype INT NOT NULL, --each will cost 1 cent
updated TIMESTAMP,
FOREIGN KEY (license) 
        REFERENCES customer(license)
        ON DELETE CASCADE
        ON UPDATE CASCADE
)ENGINE=InnoDB;

Now another Payments table will hold credit balance for each client.

CREATE TABLE Payments (
license VARCHAR(35),
Amount FLOAT(5,0) NOT NULL,
receive_date DATE NOT NULL,
updated TIMESTAMP,
FOREIGN KEY (license) 
        REFERENCES customer(license)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB;

How would I know when the client has run out of balance? Given that fact that server will get many requests every second.

If a request comes then I can sum all records in Rotate_Photo to know how many requests have been received and subtract with client credit. But will it not be very inefficient?

What will be better way?

Have you measured how long it would take to count/sum the records? — David Aldridge, Aug 11 '15 at 08:06

crafter · Answer 1 · 2015-08-13T07:00:48.580

Option 1

As you said

Payments table will hold credit balance

Therefore, when a request comes it, you need to consult the balance of the client and check for a positive amount.

SELECT Amount from Payments where license = :Rotate_Photo.licence;

You can use the count() function to get the number of records from Rotate_Photo in an efficient manner.

SELECT count(*) AS rotation_count from Payments where license = :Rotate_Photo.licence;

Option 2

A much more efficient design might be to maintain a balance against the licence record.

You would then have a table for licences:

CREATE TABLE Payments (
license VARCHAR(35),
Amount FLOAT(5,0) NOT NULL,  -- current balance
purchased_amount FLOAT(5,0),  -- amount purchased so far
receive_date DATE NOT NULL,
updated TIMESTAMP,
FOREIGN KEY (license) 
        REFERENCES customer(license)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB;

Obviously, you will have to manage the amount and purchased_amount fields each time a purchase is made (decrease amount and increase purchased_amount).

The disadvantage of this approach is that you cannot get stats for a specific period of time (for example a month), but you can get a snapshot of the account.

The advantage is that you will need a single read and update to manage the amount.

But how much money has been spent will come from Rotate_Photo table which will be .1* total number of records. — user5858, Aug 11 '15 at 07:28
Oh ya .. but will it be fast enough? This query running sevaral times every second? — user5858, Aug 11 '15 at 10:16
Generally, count(*) will be fast enough, if you add an index on your filter fields and optimise your table. However, if you are running several times every second (this was not made clear in your question, so perhaps add this important fact) it might make sense to create another table to store the count. You might also want to consider changing the table engine to MyISAM. Refer : http://stackoverflow.com/questions/1332624/speeding-up-row-counting-in-mysql?rq=1 — crafter, Aug 11 '15 at 10:54

score 1 · Accepted Answer · answered Aug 11 '15 at 10:59

"Better" is subjective - but my strong recommendation is to build a normalized solution first (this means calculating the balance on the fly), and only de-normalize if you have a performance problem you cannot solve with better queries, archiving strategies, or bigger hardware.

On moderns systems, that probably means tens or hundreds of millions of records.

If you're really concerned about this, build a test harness with twice the number of records you expect to have on production, and see how your application performs.

There are a number of solutions to the performance problem you're worried about.

The most common is "denormalization" - every time the user pays, or uses the service, you update a "currentBalance" field on their profile. You can do this either in application logic or using database triggers. Application logic means that every piece of code that deals with money needs to do it correctly; just a single bug could mean you're giving away free image rotations. Database triggers are hard to test, hard to maintain, and can create performance problems of their own.

You say "calculating the balance on the fly" but then shouldn't I run a program once in a month just to verify that on the fly calculations are correct. Just like the phone bill or credit card usage amount before final statement is generated. — user5858, Aug 13 '15 at 04:26

Efficient database design for billing

2 Answers2