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