How to store product and pricing history in a product database?

Question

What is the best way to store historical information about products and pricing in a database?

Our suppliers provide us with catalogues in various formats that usually contain

• manufacturer's ID of the product,
• product's name,
• GTIN-13,
• and MSRP.

Based on that information I would like to create a database of products featuring

• my ID for the product,
• my name for the product,
• product's manufacturer,
• product's supplier,
• manufacturer's ID of the product,
• manufacturer's name for the product,
• GTIN-13,
• MSRP,
• acquisition cost,
• bulk price for my customers,
• and of course retail price for my customers.

Since we buy directly from manufacturers or their sole distributors, and each product has one unique provider, this would be a simple one table db. Except, I would also like to store historical information about pricing and products.

First, obviously the price changes. I'd like to retain information about the current price as I import the new one. Each price (MSRP, acquisition, bulk, retail) can change independently, with retail even being adjusted manually at times to fit market conditions.

Second, minor product changes. Fairly regularly, manufacturers will release new versions of old products with slight changes to manufacturer's ID, product's name, and GTIN-13. Sadly, new and old versions can coexist for a while, distinguished by their manufacturer's ID. I'd like to retain the information about changes to the products (old manufacturer's ID, name, GTIN-13, MSRP, acquisition cost) under the same my_ID.

Finally, major product changes. Occasionally, manufacturers will release completely new products replacing some of the old ones. And not necessarily 1:1, even coexisting for a long while. A single new product can be set to replace multiple currently carried products, and multiple new products can replace a single currently carried product. I'd like to have some sort of genealogical tree of that.

What would be a smart choice of data(base) structure here? Ideally something that can be supported by PostgreSQL.

I was thinking about having that single table for current information and another one for event sourcing to keep changes. With that in place, I would simply swap in new info from the supplier under the same my_ID. Is that appropriate or will I consider this a painful mistake in the future? What should I do about the major product changes?

Answer 1

I'd opt to leave the current table as it is, and add a table to hold the pricing histories, which are related via your product id and carrying a timestamp, and a daily job to snapshot today's pricing into the history.

You might want to give further thought to what business queries might come down the pike having to do with pricing histories. One example that comes to mind is tracking whether or not a promotion was in effect, and hence the reason for the price.

There is also likely going to be a need to track sale quantities per day, so you can show weekly, monthly, quarterly, yearly trends on sales.

Going beyond a single table is not a bad thing at all. The more business intelligence your application can provide the better.

Answer 2

Keep the same structure you have, except extract the pricing data to a separate table. This table would share the primary key of the item table, except it would add a DATETIME field for effective date. You get a full history of old prices and when they became effective, and can easily query for the current price by finding the record matching a given item with the most recent effective date.

I know this works because I have worked with similar systems several times and they all used this same model. It worked very well.

Answer 3

I think prices of anything are very crucial and it is essential to store pricing so that they can be retrieved accurately without any complex logic except a single join.

For example: ProductA's price is 200 coins. At any given point, Product A's price might change but it will only sell at 1 single price. so the Product table has an inner join with the product price table and will always return only one price after a join.

your join condition might vary a bit, where different CustomerType has different prices and for that, you will have an additional column customerType in the product prices table.

Now, for getting prices of Premium customers for Product A's, your join condition will look something like this:

@ProductId=ProductPrice.ProductId AND @CustomerTypeId=ProductPrice.CustomerTypeId

Then you get one unique price in the table.

I don't agree with the ValidTillDate and ValidFromDate Approach. Because then you will have duplicate maps.

The history of the prices can be stored in a logging table. If prices change often, or we need to stage the prices like: on 2nd September, the price for ProductA will change to 300 coins.

then such a scenario can be handled by a scheduler that updates the mapping table with the prices from the priceStaging table as and when they are applicable. I know initially it can cause some bugs related to the scheduler. but eventually, you will further refine it.

In short, do not store price history in the same ProductPrice Table. Historical data can be logged instead on Every update or create of product Pricing.