0

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?

3

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.

| improve this answer | |
2

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.

| improve this answer | |
  • I have no current need for tracking what's in stock or sales. There is a separate system that does this using manufacturer's IDs. At the moment, it's purely a pricing manager project and I hope it stays that way. The seasonal offer (promotions) may be an important factor, I should probably add a reason for the price change. – Damn Terminal Jun 25 '17 at 15:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.