I'm working on an application where organisations pay for each user they add to their 'environment'. Organisations have a credit/balance, and as long as the user is not removed, the organisation pays a small hourly fee per user. Organisations can choose between recurring and one-time payments. An invoice will be made for every payment.

I'm trying to find the best way to implement this. So far, I've come up with two possible solutions:

Option A

The first possible solution is to 'log' every change of state in the system regarding users, payments and pricing.

When an organisation adds or removes a user, a log entry will be written. When a payment is being made, a log entry will be written. When the price changes, a log entry will be written. Every relevant state will be recorded.

Using these these logs, the application can calculate the credit/balance.

Option B

The second possible solution is to implement a physical credit/balance. Instead of logging everything, an hourly cron job will run that subtracts the fee per user from the organisation's credit/balance.

This second option seems much simpler to me. It avoids making all kinds of complex calculations using multiple tables and records. I'll probably need to log payments and stuff anyway, but I'm not using those logs to determine the credit/balance.

Any thoughts? What's the best way to implement this kind of system?


After considering @Ewan's answer, I do believe he brings up a good point. That's why I'm going with option A, or something that looks like option A.

Here's a simplified DB schema I created:

DB Schema

Does this look like it could work?

  • Have you talked to your accountant to see what manner the contracts fit better? And the sales department about which one is easier to sell? – user40980 Nov 15 '15 at 20:54
  • @MichaelT has a point. Of course it's important to match the method to your contract commitments, and understand what is easiest to sell. But all of this strikes me as too detailed for a typical sales conversation. To have that conversation you would have to turn these into simple plain english, explaining what customers would experience in the shortest terms possible. That's a good exercise. Often leads to a better design. – joshp Nov 16 '15 at 3:50
  • no. you need a BillingEvents table separate from your domain model. this is very much like a log, Events are added and never changed or deleted They all have the same structure, customerId, eventType, date. Again It should link to customerIds in your accounting software rather than a domain model if possible – Ewan Nov 16 '15 at 21:59
  • What is exactly the added benefit of such a table? If all the needed data is present in the domain model, why would I need to add such a table? Would I also use that table to calculate the customer's credit/balance? Just curious. – Willem-Aart Nov 18 '15 at 14:54

Option A is far superior.

Imagine your cron job crashes and doesnt run over a weekend. Or you find a bug and its been calculating the price slightly wrong for ages. You would have no way to figure out the correct bills.

In any system like this you have to consider how you would check the results. so you need to be able to not only bill correctly, but go back and explain the bill calculation and prove it is correct.

| improve this answer | |

Have you considered something like a combination of the two?

Once per hour, compute the charge, and record everything that comprises that charge in a write-only table. Then once/month (or however long) an invoice can be generated off that data. If there's any disputes or discrepancies, it should be relatively easy to track down.

This doesn't take the place of having good logs, which is what you would get in Option A.

| improve this answer | |
  • Interesting suggestion. However, that would mean storing a record once an hour for each organisation. That table would soon become quite large. Probably not problematic though. – Willem-Aart Nov 15 '15 at 21:23
  • @Willem-Aart: Depending on your database, it may be possible to embed this computation in your database. For example, Microsoft Sql Server supports this strategy using indexed views (though the requirements to allow indexed views are stringent). This caches the value, but guarantees it will be kept up to date. – Brian Nov 16 '15 at 16:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.