保持计费率历史记录的最佳方法？ [英] Best way to maintain billing rate history?

问题描述

我已经创建了一个客户账单表，以及一个客户档案表，其中包含了与他们相关的费率。问题是，在实施新的费率变更之后，我希望能够通过报告查看旧账单，并使其反映出旧费用。适当的历史背景下的比率。我正在寻找有关如何在我的报告中保持计费率历史记录时如何最好地管理持续费率变化的建议？

I have created a table for customer bills, and a table for customer profiles which contains rates associated with them specifically. The problem is that after a new rate change has been imposed I want to be able to look at an old bill, via a report, and have it reflect the "old" rate within proper historical context. I''m looking for suggestions on how to best manage ongoing rate changes while maintaining a billing rate history in my reports?

推荐答案

很难想出一个最好的方式如果你没有参与整个开发过程，但是在决定最佳方式时你可以考虑一下。


有另一个表存储客户ID号，客户评级，费率适用的日期和自动编号字段。每次客户对更改进行评级时，都会将其添加到该表中。


然后在创建客户账单（或查询客户账单）时，它可以使用具有最接近但更少的日期的费率比该特定账单的客户账单日期。如果您认为这是最佳方式，您需要弄清楚如何将这个想法融入您的项目中。对于你的项目。


我相信其他人会有其他想法，最好用的将取决于任何特定项目的特点


希望这有帮助

Its difficult to come up with a "best way" when you are not involved in the entire development process, but heres 1 idea you can consider when deciding the "best way".

Have another table that stores the Customers ID number, the customers rate, the date that the rate applies from and an autonumber field. Each time a customers rate changes it gets added to that table.

Then when creating a customers bill (or querying them) it can use the rate that has the date that is nearest but less than the date of the customers bill for that particular bill. You will need to figure out how to incorporate that idea into your project if you consider that to be the "best way" for your project.

I am sure others will have other ideas and the best one to use will depend on the peculiarities of any particular project

Hope this helps

顺便说一句，如果你想在某处保存对费率记录的引用，你真的只需要费率表中的自动编号。从简单的表中，如果您这样做，您可能会直接在客户订单中保存来自customers表的费率，并完全取消该表。

By the way, you really only need the auto number in the rates table if you want to save a reference to the rate record somewhere. From a table as simple as this, if you did that you might as will save the rate from the customers table directly in the customers order and do away with that table altogether.

我创建了一个客户账单表，以及一个客户档案表，其中包含与他们相关的费率。问题是，在实施新的费率变更之后，我希望能够通过报告查看旧账单，并使其反映出旧费用。适当的历史背景下的比率。我正在寻找有关如何最好地管理持续费率变化的建议，同时在报告中保持计费率历史记录？

I have created a table for customer bills, and a table for customer profiles which contains rates associated with them specifically. The problem is that after a new rate change has been imposed I want to be able to look at an old bill, via a report, and have it reflect the "old" rate within proper historical context. I''m looking for suggestions on how to best manage ongoing rate changes while maintaining a billing rate history in my reports?

针对关系理论的个人设计观点总是很有意思。我提出我的意见......你不可避免地构成了自己的想法。


与任何具有时间点相关性的费率或数据项一样它的原则是捕获该数据项并将其存储在能够正确，准确地反映数据的地方。


如果我们有一个处理销售的记录，那么在我看来，销售时的费率应与销售记录一起存储。作为一个整体的销售记​​录在逻辑上应该具有日期时间戳，以便将其放置在任何记录堆栈中。然后可以对销售行中存储的费率进行任何计算，因为它反映了当时相关的费率。


它当然可以说前一段通过对比率进行非规范化来打破关系模型，因为你可以在一个单独的tblRates表中存储单个费率和一个日期变化，并存储一个ID或其他参考来引用费率变化


这个论点有其优点，但在我看来，它可能会变得有点抑制，并且可能会有不必要的复杂性。如果我们在系统构建的上下文中看一下''性能与开销''的原理，那么任何构建的目的都是提供一个在速度方面有效而不影响完整性的数据库数据的。如果我们这样做，那么对我来说，实际利益超过了纯粹主义理论。


要从单独的表中检索需要查找的任何内容，需要某种 JOIN ，例如从我们可能定义为的表中tblRates（一个存储速率值查找的表）。


你本质上​​可能没有问题.....但是在你的应用程序的整个生命周期中它是一个 LESS 在后续无法预料且可能复杂的SQL查询的情况下加入竞争，你可能需要在某些时候构建它们。


您可以通过引用查询特定的费率本身您可能用于记录记录和/或customerid / orderid / saleid的日期时间字段，即：我们对此客户的费率更改有什么日期或 对于17.5的比率，客户在特定时间和日期之间获得此费率的比率。所有这些都可以使用针对单个销售表的简单SELECT语句进行查询，如果索引正确，也将快速提供响应时间和排序条款。


当然没有什么可以防止将默认的新值存储为单独的现在AS查找表，因为它适用于个别客户或实际上基于某些计算值的新记录，即：一个可能决定重要性级别或价值的客户''到现在为止''所以这些新的价值显然会附加到任何新的销售行，并成为历史，它们就会成为历史的第二个。


问候


/>

吉姆：）

Personal design opinion set against relational theory is always an interesting one. I offer my opinion... you make up your own mind inevitably.

As with any ''rate'' or item of data that has a ''point in time'' relevance to it the principle is to capture that item of data and store it in a place that can reflect that data correctly and accurately.

If we have a record that deals with a sale then the ''rate at the time of the sale'' in my view should be stored with the sale record. The sale record as a whole should logically have a date time stamp for its placement in any stack of records. Any calculations can then be conducted against the ''rate'' stored in the sale row as it reflects the rate that was relevant at the time.

It could be argued of course that the previous paragraph breaks the relational model by denormalising the rate in that you could store a single rate and a date change in a separate tblRates table and store an ID or other reference to reference the ''rate change''

There is merit in this argument, but in my view can become a little inhibiting and potentially unnecessarily complex. If we look at the principle of ''performance versus overhead'' for a moment within the context of system build the purpose of any build is to provide a database that is effective in terms of speed without compromising integrity of data. Provided we do that, then for me the practical benefits outweigh the purist theory.

To retrieve anything that needs ''looking up'' from a separate table requires a JOIN of some sort, for instance from a table we might define as tblRates (a table storing rate value lookups).

No problem you might think in essence..... but over the lifetime of your application it is one LESS join to contend with in the case of subsequent unforeseen and potentially complex SQL queries that you might have to build at some point.

You can query the specific rates themselves by reference to an datetime field that you might use to ''time stamp'' the record and/or customerid/orderid/saleid ie: "What dates do we have for rate changes for this customer" or "for a rate of 17.5 what customers have been given this rate between a certain time and date period." All of this can be queried with a simple SELECT statement against the single sales table which if indexed properly will also provide for swiftness it terms of response times and sorting.

There is nothing of course to prevent storing default ''new'' values a rate ''AS of now'' in a separate lookup table as it applies to individual customers or indeed basing new records on some calculated value ie: one that maybe determine the ''importance'' level or the ''value worth'' of a customer ''as of now'' so to speak.

These new values obviously append to any new sales row and become history the very second they become so appended.

Regards

Jim :)

这篇关于保持计费率历史记录的最佳方法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！