Database design: Custom fee/billing charge rates

A user can have custom billing rates, which may differ from one user to another.

You would most likely have a default billing fee table, that would look something like this

FEE
Id
FeeTypeId
FixedCharge
VariableCharge

When you charge the customer you might send them an invoice etc and then you have a table that stores the billed/charge amount that might look like this!

USER_TRANSACTION_CHARGE
Id
UserId
FeeId
ChargeName
ChargeAmount
DateTime

How do you design it for custom billing rates per specific user?

It doesn't look as if your FEE table has any history. It is a common legal requirement to be able to produce invoices up to 7 or 10 years ago. A FEE record can change, so what does that do to the link (feeid) in the invoice table?

FEE: Id, FeeTypeId, FixedCharge, VariableCharge
USER_FEE: Id, UserId, FeeTypeId, FixedCharge, VariableCharge

You could create a USER_FEE table that "overlays" the FEE table for a specific User. So for each given FeeTypeId, a user may have an overlay in USER_FEE or could just fall back to FEE.

As for USER_TRANSACTION_CHARGE, I seriously challenge the link column FeeId in there, unless you maintain history against FEE, which FeeId + DateTime would link to. I would drop that table to just

Id, UserId, ChargeName, ChargeAmount, DateTime

Where I assume ChargeName is somehow linked to FeeTypeId? And ChargeAmount is whatever has been worked out from Fixed/VariableCharge

So in essence, the (USER_)FEE tables are used to look up the values, but beyond that, they are stored as values without a backlink to the (USER_)FEE table, since this appears to be a simple system without versioning.

Keep the table of default fees and add a cross-reference the lists user, feeType, and the override. At billing time determine the fee with a resolution (http://database-programmer.blogspot.com/2008/04/advanced-table-design-resolutions.html) That blog entry actually uses as its example a time-billing system, close to what you are doing.

Beware of false normalization -- by which I mean you should materialize the actual fee and save it permanently on the invoice. This is because at the time an invoice is created the actual fee charged becomes a historical fact about the transaction, and so it is not denormalizing to save it as such. The foreign key is there only to identify the item/activity/feeType, not for the purposes of obtaining the fee, the fee is saved onto the invoice and kept forever.

The default fee should be independant of what was actually charged, so the User_Transaction_Charge has to lose the FeeID foreign key.

If you need to store the components used to generate the User_Transaction_Charge, store them within the Charge or as a separate entity.

DEFAULT_FEE
Id
UserId
FeeTypeId
FixedCharge
VariableCharge

and

USER_TRANSACTION_CHARGE
Id
UserId
ChargeName
ChargeAmount
DateTime

USER_TRANSACTION_CHARGE_FEE
Id
UserTransactionChargeId
FixedAmount
VariableRate

or

USER_TRANSACTION_CHARGE
Id
UserId
ChargeName
ChargeAmount
FixedAmount
VariableRate
DateTime