Best way to maintain billing rate history?

[Lykus]

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?

[Delerna]

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

[Delerna]

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.

[Jim Doherty]

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?

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 :)

[missinglinq]

For what it's worth, I agree with Jim on this one! Regardless of what the theorists say, storing the current rate (or storing a calculated value, for that matter) in the record when the rate is apt to change over time, simply makes more sense than doing the hucklebuck with another table, having to take the date of sale and comparing it with dates from the rates table to find the rate for that particular time span.

I wouldn't store the results of an area calculation:

Area = Length x Width

will always be true. But supplying gasoline for corporate fleets and running monthly invoices, with the price of gasoline changing daily, using a separate table for dates/prices just makes no sense!

Linq ;0)>

[Delerna]

I also agree with jim and missingling.
Besides, while doing a 4 day course on BI a couple of years ago we spent the first 2 days learning to create denormalised from normalised data using DTS.
This was for the sole purpose of speed. Normalised data has many benefits but also has drawbacks. Execution speed of queries is one of them. Query complexity is another. Its easier to read and understand a query that has a couple of joins as opposed to one that has 50

[mshmyob]

As you can see by the various answers there is always more than 1 way to design a database structure.

My 2 cents worth: By your scenario it appears you are trying to create an Order Entry type of application. If you have only 2 tables (1 for Billing/Orders and 1 for Customers) you will run into a number of problems.

Your Orders (Billing) table should be split into 2 (Billing and Billing Detail). The Billing table will hold the Date of the Bill (and other info) and the Detail table will hold the detail item lines of the Bill (this will give you the history you are looking for).

If each Customer can have different rates or product prices for the same service or product then you should have your Services/Product table split into 2 also. Service/Product table holds the default rates/prices and a Special Pricing table hold rates/prices that are specific to the Customer.

I would not put rates/prices in the Customer table.

In this scenario we get:
1. Customer Billing history with date specific pricing/rates
2. Each Customer can have specific rates/pricing for the same service/product

As everyone pointed out there is a trade off between speed and data integrity. In my opinion with todays hardware speed is almost a moot point. In a billing system you will probably only retain about 2 years of history and then archive your data.

The BI scenario mentioned would be for a data warehouse and this should always be denormalised.

If this is what you are looking for I could supply the exact schema. If it isn't then just ignore this post and I will go back to bed (lol).

[Lykus]

As you can see by the various answers there is always more than 1 way to design a database structure.

My 2 cents worth: By your scenario it appears you are trying to create an Order Entry type of application. If you have only 2 tables (1 for Billing/Orders and 1 for Customers) you will run into a number of problems.

Your Orders (Billing) table should be split into 2 (Billing and Billing Detail). The Billing table will hold the Date of the Bill (and other info) and the Detail table will hold the detail item lines of the Bill (this will give you the history you are looking for).

If each Customer can have different rates or product prices for the same service or product then you should have your Services/Product table split into 2 also. Service/Product table holds the default rates/prices and a Special Pricing table hold rates/prices that are specific to the Customer.

I would not put rates/prices in the Customer table.

In this scenario we get:
1. Customer Billing history with date specific pricing/rates
2. Each Customer can have specific rates/pricing for the same service/product

As everyone pointed out there is a trade off between speed and data integrity. In my opinion with todays hardware speed is almost a moot point. In a billing system you will probably only retain about 2 years of history and then archive your data.

The BI scenario mentioned would be for a data warehouse and this should always be denormalised.

If this is what you are looking for I could supply the exact schema. If it isn't then just ignore this post and I will go back to bed (lol).

Thanks Dalerna, Jim, missingling, and mshmyob, I really appreciate the time you have taken to help me sort through all of this! I am not sure if I follow every point but I definitely get the main message: by creating a separate Rate History table I will be creating joins that will tax the reporting speed of the database. It would seem that entering in the rate with each billing record makes the best sense from a performance perspective.

Perhaps a drop down would be useful for the Rate field on the Billing form, wouldn't it be easiest to draw that customer rate from the Customer table, mshmyob?

In regards to potential Billing table and/or Product table splits I should really put some perspective into how this database is being used. It is for a logistics company, and the rates are shipping rates which in this case change maybe once a year. The bill/invoice/transaction table essentially contains:

Client Code
Carrier Code
Pro#
Ship Date
Entry Date
Bill of Lading #
PO #
Shipper
COnsignee
# Pieces
Shipping Class
Wt
Amt Billed
Amt Paid
General Ledger Code

...and then I am thinking I should add a drop down field for the Shipping Rate. mshmyob, bearing this in mind, do you still think the billing table could be further normalized?

[mshmyob]

I think we can now see a better idea of what you want.

I have a few questions.

1. Are you just storing information in your database based on a report you receive from another program or are you trying to input live information.

2. Do you currently have only 2 tables - Customer and Billing

3. What is the primary key for your Billing table.

4. Is the amount billed field based on your Customer rate and the weight or something else.

The way I see it now you are just plugging in some numbers and not really using the power of a DBMS.

Your idea of a combo box is not something you need to think about at this time. We need to get you a proper design first and you will be surprised at how easily things fall into place.

If you only have the 2 tables you will get no history the way you have it now. Give me some more details and we can get you a proper design.

Thanks Dalerna, Jim, missingling, and mshmyob, I really appreciate the time you have taken to help me sort through all of this! I am not sure if I follow every point but I definitely get the main message: by creating a separate Rate History table I will be creating joins that will tax the reporting speed of the database. It would seem that entering in the rate with each billing record makes the best sense from a performance perspective.

Perhaps a drop down would be useful for the Rate field on the Billing form, wouldn't it be easiest to draw that customer rate from the Customer table, mshmyob?

In regards to potential Billing table and/or Product table splits I should really put some perspective into how this database is being used. It is for a logistics company, and the rates are shipping rates which in this case change maybe once a year. The bill/invoice/transaction table essentially contains:

Client Code
Carrier Code
Pro#
Ship Date
Entry Date
Bill of Lading #
PO #
Shipper
COnsignee
# Pieces
Shipping Class
Wt
Amt Billed
Amt Paid
General Ledger Code

...and then I am thinking I should add a drop down field for the Shipping Rate. mshmyob, bearing this in mind, do you still think the billing table could be further normalized?

[Lykus]

I think we can now see a better idea of what you want.

I have a few questions.

1. Are you just storing information in your database based on a report you receive from another program or are you trying to input live information.

2. Do you currently have only 2 tables - Customer and Billing

3. What is the primary key for your Billing table.

4. Is the amount billed field based on your Customer rate and the weight or something else.

The way I see it now you are just plugging in some numbers and not really using the power of a DBMS.

Your idea of a combo box is not something you need to think about at this time. We need to get you a proper design first and you will be surprised at how easily things fall into place.

If you only have the 2 tables you will get no history the way you have it now. Give me some more details and we can get you a proper design.

The information inputted here is done manually from paper invoices received daily. The Amt Billed and Amt Paid are based on a pre-paid/estimated amount paid and a final adjusted amount.

Here are the table/primary key combinations I am using:

tblBill/ProNumber
tblCarrier/CarrierCode
tblClient/ClientCode
tblShipper/ShipperID
tblConsigne/ConsigneeID

The tblBill table has a many-to-one relationship with all the other tables.

[mshmyob]

Sorry for the delay. It looks to me like you are just using Access as a repository of Historical data (just like a data warehoue). You seem to be using it as an Analytical Database rather than a transactional database (becuase you are using another program to generate invoices and then just taking certain results from that invoice and storing results in your Billing table to be used for historical reference). If this is the case you could just make another field in your Billing table and store the Rate in there for future reference. It won't do anything but that Bill will have a reference to the Rate that was used to generate the results for that Bill.

If you intend to make a real transactional database then you would just put a bridge table between the Customer and Billing table to generate a "Billing Detail" table as all the previous posts indicated.

Let me know if I am understanding your requirements correctly and if this was any help.

The information inputted here is done manually from paper invoices received daily. The Amt Billed and Amt Paid are based on a pre-paid/estimated amount paid and a final adjusted amount.

Here are the table/primary key combinations I am using:

tblBill/ProNumber
tblCarrier/CarrierCode
tblClient/ClientCode
tblShipper/ShipperID
tblConsigne/ConsigneeID

The tblBill table has a many-to-one relationship with all the other tables.