Updating Product Prices Changes Pricing on All Orders

I am creating a database in which the prices for specific materials come from a table which is linke开发者_开发技巧d and which you can update and modify all the records linked to that value.

The problem is that when I update it it is universal and prior clients who purchased at a lower/higher price are also modified. I just want to be able to update the price and not change all records but only from that point on.

How can this be done?

In the table that stores purchase information, you need to store the price they purchased the product at.

I normally use the products table mostly as a lookup table. This means that when the user enters a part number in the Order Entry screen, I simply use DLookups (or recordsets) to pull in the description, price, and any other relevant information into my order details table. This allows the user to change the description or price for the item on their specific order without affecting the products table. It does increase the storage capacity needs but I'm under the impression that this is how most order entry systems work. It's not possible to have the same flexibility and data integrity with the design that you are apparently using.

I also store any information about the customer that could possibly change at a later date, in an effort to preserve history. This practice is certainly open to debate. However, I've found it handy to be able to review previous orders and, for example, see that a specific customer was still located at his old address when we sold him x.

Doing what HK1 suggested (additionally and explicitly storing the applicable item prices for each individual order) is probably the way you'd want to go, given the amateuristic DBMS you mention.

This does not constitute "redundancy" with the items table holding the prices, because that table is about current prices, and the prices that will be recorded in the order items table will be about past prices.

Note, however, that there is still a kind of "redundancy" in that two distinct order items, which are for the same item and on the same moment in time, will have to have the same price value. Strictly speaking, this is a constraint that you should be enforcing.

The "truly" elegant solution, therefore, is to keep a separate history table of item prices, and if you want to fully reconstruct past orders, join that one in with the past orders, with an appropriate selection on date/time.