CODEWITHSUNDEEP
mysqldatabase-design
user5858
user5858

Reputation: 1221

Efficient database design for billing

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?

Upvotes: 0

Views: 1297

Answers (2)

crafter
crafter

Reputation: 6296

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.

Upvotes: 1

Neville Kuyt
Neville Kuyt

Reputation: 29629

"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.

Upvotes: 1

Related Questions