数据库设计：自定义费用/计费费率
用户可以具有自定义的计费费率，该费率可能因用户而异。
您很可能有一个默认的计费费用表，看起来像这样
FEE
Id
FeeTypeId
FixedCharge
VariableCharge
当您向客户收费时，您可能会向他们发送发票等，然后您有一个存储计费/收费金额的表，可能看起来像这样！
USER_TRANSACTION_CHARGE
Id
UserId
FeeId
ChargeName
ChargeAmount
DateTime
如何针对每个特定用户的自定义计费费率进行设计？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您的 FEE 表似乎没有任何历史记录。能够开具 7 或 10 年前的发票是一项常见的法律要求。 FEE 记录可以更改，那么这对发票表中的链接 (feeid) 有何影响？
您可以创建一个 USER_FEE 表来“覆盖”特定用户的 FEE 表。因此，对于每个给定的 FeeTypeId，用户可能会在 USER_FEE 中进行覆盖，或者可能会退回到 FEE。
至于 USER_TRANSACTION_CHARGE，我严重质疑其中的链接列 FeeId，除非您维护 FEE 的历史记录，其中 FeeId + DateTime 将链接到该历史记录。我会将该表放到
我假设 ChargeName 以某种方式链接到 FeeTypeId 的位置？
ChargeAmount 是从固定/可变费用计算出的任何内容
，因此本质上，(USER_)FEE 表用于查找值，但除此之外，它们存储为值，而没有到 (USER_)FEE 表的反向链接，因为这似乎是一个没有版本控制的简单系统。
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?
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
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.
保留默认费用表并添加交叉引用列表用户、费用类型和覆盖。在计费时通过分辨率确定费用（http://database-programmer.blogspot.com/2008/04/advanced-table-design-resolutions.html）该博客条目实际上使用时间计费系统作为示例，接近你正在做的事情。
谨防错误的标准化——我的意思是您应该具体化实际费用并将其永久保存在发票上。这是因为在创建发票时，收取的实际费用成为有关交易的历史事实，因此将其保存为非规范化。外键仅用于识别项目/活动/feeType，并非用于获取费用，费用将保存到发票上并永久保存。
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.
默认费用应该与实际收取的费用无关，因此 User_Transaction_Charge 必须丢失 FeeID 外键。
如果您需要存储用于生成 User_Transaction_Charge 的组件，请将它们存储在 Charge 中或作为单独的实体。
和
或
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.
and
or