While reading my book Visual Basic .NET Database Programming, Larry Gajdos had these comments:

I'm liking the introductory chapters of your book. You touch on some of the design issues (wrt normalization) that other authors ignore or gloss over.

One important issue that I have never seen addressed very well deals with what I'll call dynamic data. A good example in the book's order entry db is the unitprice associated with an orderitem of any particular order. My problem is that prices tend to be very volatile, to the point where the price charged for an item might need to copied from a "current price" entity each time an order is made.

The structure you define seems to result in a wholesale update of all orders in the system whenever an item's price changes in the inventory entity!

It might be possible to store each item's price history in the table such that an given order items price could be obtained by looking it up in the price history, based on the order date. This seems a bit cumbersome and lacking in flexibility, which is why (in my simple attempts to deal with this problem) I just store the price with the order item.

But what are the more common approaches to this problem? In general, the care and feeding of a database that ages in the manner just described, with even more complicated phenomena, would be a very useful discussion.

I think the standard way to do this sort of thing is to keep the prices in a separate table. If you update an item in that table, you update the price for that item forward and backward in time. And yes this can cause big problems in industries where prices change frequently.

The "easy" way out (easy for the developer, at least) is to create a new item to represent the product at different prices. When the price goes up, you make a new item with all the same information as the old one except with the new price. The old items stay in the database forever so you can look up old prices.

Storing prices in a price history table works okay if you have a very large volume. If you sell thousands of units between price changes, it may be worthwhile to store the data this way. It will be less convenient if you need to create a new price history record for every few sales.

As you suggest, I would probably store the prices with the order items. In fact, the most recent system I built where this was an issue did it more or less this way. There were only a few items "for sale" (license fees, late payment penalties, etc.) but the system had to allow for the amounts of these items to change. We kept the current prices handy in variables loaded from the database and stored the actual amount billed in the billing records. Later, if the values changed, new records would use the new values. Previously generated bills contained the values in effect at that time.

If you take this approach, you might also want some sort of table to record the prices as they change so you have a history of them. Our prices were set by legislative decree (it was a tax application) so it would have been easy to look up when the values changed. They also changed very rarely so it wasn't a big concern but it could be a more pressing issue for some applications.

I worked in the Marketing Dept. of a biotech company for 20 years and was, among other things, responsible for establishing the list and discount prices of 5000 items in 12 currencies (pre-Euro days). The list prices reflected essentially "this is what we wish we could charge" since virtually all of the 50,000 customers had negotiated special quantity discounts and, to make things even more challenging, the discounts did not follow a predictable rule like "10 units=10% discount".

The system which ultimately evolved was a table of "exceptions" which contained the customerID, the ArticleID and the pricing exception for items which had been quoted. Every time an order was placed, the system would look in the "exceptions" table and take the price stored there if a record was found; if not, the price was taken from the "list price" table.

This approach may be more complicated for most classical order entry systems and therefore I would suggest simply avoiding retroactive price changes by storing the actual price charged on a specific day in the invoice table. Each time a new order is placed, the simplified system could do a look-up in the invoice table and attempt to fetch the price which the customer was last charged and give the user the option of using the old price charged or the new list price. Storing prices actually charged also opens the door for doing "reality checks"; i.e. "Is our asking price the price we are charging at the end of the day?" or "what annual quantity justifies this invoice price".

In looking at this specific comment about sales prices being stored with the sales order, I happen to agree with Larry's comments. The main reason is that sometimes the sales price is nowhere near a standard (stored) price.

I don't believe that it is a normalization issue since there are two completely separate fields: the product's list price and the order's product sales price.

In case you're not a database administrator (DBA), Gregory's comment about normalization is important because DBA's spend a great deal of effort normalizing databases (see my book Visual Basic .NET Database Programming for a discussion of normalization and where it is and is not appropriate).

DBAs generally don't like to break normalization without a good reason. Noting that asking price and actual price are different means you can store both without messing up the normalization.

Without even reading on: veto. Never seen. [I.e. he hasn't seen a system that uses updating prices. Rod] I know at least 5 systems well enough, one was a custom job by me but following the mechanics of an older clipper-based system, another was an SAP instance, others I had my fingers in deep enough to do custom statistics, and so on (I'm the only person to my knowledge who ever did a successful migration *away from* SAP - oops, I'm bragging. Strike that last comment.).

Copy the unit price to the order item record. Full Stop.

In my opinion, the only other viable alternative is to store prices together with validity ranges and leave that as sole price repository. I've done it, it works, but you get more hassles than it's worth, e.g. how do you join this to other tables when constructing SQL statements? The results are invariably SQL dialect specific and have suboptimal performance. Yes, you should store prices together with validity ranges, but still copy the current value to the order.

create a new item to represent the product at different

With a new product ID? Or decouple the user-visible product ID from the internal primary key? I like to keep the ID as PK whenever possible. Yes, a product ID should change (and a fresh copy of the product record be made) when significant product characteristics change, but I wouldn't place the price amongst these. Example: An imported gadget with a user manual in, say, english. You have the manual translated to fit your audience's. New Product ID? I'm in favor. Have a typo in the manual fixed - new Product ID? Open to discussion, but I'd vote 'Nay'. Same box, new price? No thanks. A good order entry employee *will* start memorizing those ID's and work far more efficiently by entering them directly after a short while. I say, support this style in your forms and DB structure.

Pardon my jumping in without reading your book, you may well have covered my comments, but the posts on the website seemed a little over simplified.

Seems to me that this is a very multifaceted discussion since "price" covers many potential instances of the value construct.

There is List price as in the current catalog, order price, as in the price pertinent to the order or Quotation, Final or Invoiced price as in that which needs to get recorded for accounting purposes, and depending upon the business potentially many other in between options (standard cost, unit cost, transfer price, export price, GSA price etc). I suggest that any DBAs understand the business model first and then do the appropriate normalization. The Business rules will differ depending upon how the company conducts their business and therefore that will affect which prices have to be tied to and order ID or and Invoice ID versus which ones can flow from a price book/catalog. Any final model needs to be as flexible as possible given the potential for new products, new ways of doing business and new government regulations.

As for New product id for modifications. One possible solution is a two part ID. A main part and a revision suffix. Price may or may not fluctuate depending upon the company product rules if the suffix changes, but then you can always tie to the main body of the product ID or the whole ID. Otherwise you have a major issue in supercession tracking as far as product history goes.

None of this even touches the discussion of component part pricing and the problems of grouping sub assemblies or packages each with it's own pricing. For instance, special discounts for combinations of products.

Similarly we haven't discussed pricing for export control where there may be commercial paper involved and pricing solely for customs purposes or international intercompany trading mark-ups.

I've been involved with retail food, home furnishings, software procurement, manufacturing, computer parts and equipment, telecommunications circuit pricing, overseas trading and developing a replacement price book for a major computer manufacturer and it seems to me that each industry brings their own unique pricing issues to the table, most driven by the business rules and custom and practice within the company and industry concerned.

My own preference would be to store the transaction price at the time of the transaction (if it was locked at that point) it's a call as to whether the volume dictates you look those up based upon a date (or some other key) or carry the unique price. I guess as a heretic it seems to me that once I have a locked price it's better to carry that with the record than to keep having to go back and to retrieve it. That's slow, and still requires I store a PriceKey, so I don't save any "space" on the Db. However, if I'm looking at pricing that is presented as the "current" price, I have no choice but to look it up. Both of these are doubly important when dealing with product prices that are affected by exchange rates

Don't know if this helps or hinders the discussion but maybe it will stimulate some deeper thinking around these issues.

I think there are two main business models. In the first, prices seldom change and they change for every customer at the same time (think retail store). Every customer pays the same price and there is no bargaining. You can model volume or package pricing as separate products. For example, a dozen units might have a special package price and its own product ID. It might even have its own packaging depending on the product.

In the second model, prices change frequently. Different customers may pay different rates and may dicker over price (think car or appliance dealership). There may be a huge number of possible prices, exchange rates, a complex volume discount structure, discounts that span transactions (for example, your purchases are tracked throughout the year and the price drops after you've purchased a certain number of items), etc. In this model, Adrian is right that it makes little sense to try to look prices up. Every transaction could potentially use different prices so you would need to make a separate price record for every one and link it to the order. You'll use no more space just putting the prices in the order.

You would probably want price look up tables to set default values and guidelines but the user is going to need the ability to override them easily.

If you know of other methods for storing frequently changing values such as prices, please let me know.