1

What’s a good strategy for storing point-in-time data in a relational database?

For example, let’s say you have products and orders. Every order has some products. But they’re not the products as they exist now, they’re the products as they existed at the time of the order. You may later change the product price, remove a guarantee from the description, or delete the product, but the order remains the same. The difference is important when later fulfilling orders, and also for historical data. How do you represent this?

The systems I’ve seen de-normalize: when you place an order, order-item rows are created that copy in the relevant parts of product rows. This makes it difficult or impossible to answer questions like “when we moved feature X from product Y to product Z, how did that affect sales?”, encourages data loss (did we tell that customer, in the description, that they have that guarantee?), and of course stores a lot of duplicate data.

I’m considering immutable products with versioning. Instead of updating products, create a new product, and internally track the version chain and the latest version. This lets you reference products directly without it later becoming wrong. It makes the above queries easy. It reduces duplication. But it concerns me that I’m new to database design and considering something that I don’t see people writing about.

There’s also further opportunity for improvement - updating one field causes the rest of the row to be duplicated. Fixing this is a metadata/tracking burden, I think it’d be easier to just move big fields out into their own (optionally versioned) tables - but that wouldn’t be necessary with a native implementation of this.

Products and orders is a very typical use case for relational databases, right? How is everybody solving this?

5

You have stumbled upon what is sometimes referred to in data warehousing circles as "slowly changing dimensions".

Depending on exactly how often / rapidly your product tables change, you have a couple of standard ways to approach this issue.

You may be stuck with the option you already cited of denormalizing everything when the order is placed. This option is best if things are complex and change quickly, for example if every product is more or less custom because you change it that often that each order comes out a little bit different. It doesn't help you with questions like "when did we change product x?" - but this approach assumes the answer to that question is more like "when didn't we change product x?"

Another typical way to go, especially if your changes are not that frequent (yes, this is a subjective measure) is to add versioning to your product table. This doesn't mean you make an entirely new product, with a new SKU, for example for each change, but you do add effective and expiry times to the product table. Pro tip here: for the current version use an expiry date of MAX_COLLATING_DATE for your database. This will keep your queries from getting more complicated than they need to be. You might have to add some UI level magic to turn 9999-12-31 into "until further notice" or something to avoid confusing your users, but believe me it will be worth the trouble.

In this scheme you might keep old versions of a product in the main product table, or you might archive them off to another table that only has the versioned data. That's up to you. Each version has a date range on it, so you know when changes happen and you know that if the customer bought product x on such and such a date, the effective version of the product at that time looked like so.

To be clear, in this second option, an update to the product record (from the user's perspective) is actually an update to the current record to set the expiry date (nothing else!) and an insert of a new record with effective = right now and expiry = forever.

  • Thanks! The Wikipedia article for Slowly Changing Dimensions helped even more. Your versioning description is actually what I was trying to describe in the question, though your expiration dates are a better invalidation method than I'd come up with. – twhb Sep 25 '18 at 7:45
3

Some good answers here, but let me highlight one downside of immutable products records. If you have lots of references to the "always newest" product, not the historical ones, and those relationships use the primary key of the Product table as reference, you need to update all those relationships whenever one product attribute changes. This can lead to cascading updates, and it can become quite impossible if the ProductIDs are used also in parts of the system which are not in your database or not under your control.

For example, if you have an InStock table, with a foreign key ProductID, every update of the pricing of several products will produce a lot of new ProductIDs for things which are actually "the same", and so require to update all the affected records in the InStock table.

The solution to this is to separate the concepts of the product itself from the product's (historical) attributes. The top answer to this former SE question shows this. This may lead to a Product table with nothing but a ProductID column, and a separate ProductData table, referencing the former, with immutable records and a some time stamp(s). Then you can distinguish in your data model between references to the Product table, using the ProductID, or references to the data in one specific point in time, using ProductDataID as foreign key.

2

For example, let’s say you have products and orders. Every order has some products. But they’re not the products as they exist now, they’re the products as they existed at the time of the order. You may later change the product price, remove a guarantee from the description, or delete the product, but the order remains the same. The difference is important when later fulfilling orders, and also for historical data. How do you represent this?

It seems to me that there are 3 separate things, though you may divide it how you wish:

  • What you claim to sell

    These are items you would like to sell to customers, you might have them in stock and they are available immediately or you might be sold out; yet you would still like the customer to be able to place a reservation (something less than an "order" because you can't bill for something that you don't know you can sell).

    If you are sold out you might have to obtain new stock from a different source, this might affect pricing and guarantee along with delivery time.

  • What the customer wants

    It can be a 'wish list' which shows how many customers are interested in something that you don't actually stock but might consider stocking (or having drop shipped) if there is enough interest and it aligns with what you currently sell.

    It can be an "order". An order is exactly that, the customer orders what you promised. They have agreed to the fine print of your terms and are paying for what they want - they don't want something different (you'd be surprised how many places have employees whom having established exactly what the customer wants immediately think that they would want something different, it's not upselling it's arguing and time wasting).

  • What you can offer.

    You can immediately offer what is in stock, ship it by end of day, and it will arrive when it's delivered.

    Items not in stock are on backorder, you can't establish a certain price or guarantee unless you are willing to absorb any losses associated with doing that, the customer certainly isn't.

    Thus, the package description, color, weight, price, guarantee, and whatever else are fixed at the time of sale.

    You can alter any of those for subsequent sales but any sales already made are tied to what they are when the sale is made - changing things often and always making it worse (shorter guarantees and higher prices) won't go over well. Having a "sale" where prices are temporarily lower or buying a particular quantity nets a discount is acceptable but you need to be clear about the terms and duration (no pulling the rug out, or bait and switch).

The flowchart is:

The customer is: "just looking", "asking if you have" (this can be a "wish list", questions in an email, or you can examine your website's searches), or is actually "buying".

You have the item and will put it on the next truck or you need to backorder, if it's a "wish list item" do you wish to make such items orderable and either drop ship them or stock them.

Also there are your terms treat this as though it was something you stock and include in the box when you ship the item ordered. Items ordered are decremented from stock when sold so they can't be sold twice and the "price", "guarantee", "terms and conditions", etc. are treated like something packaged in the box that was shipped - they don't change in transit.

This makes it difficult or impossible to answer questions like “when we moved feature X from product Y to product Z, how did that affect sales?”, encourages data loss (did we tell that customer, in the description, that they have that guarantee?), and of course stores a lot of duplicate data.

I believe that the way I described of looking at things avoids those problems.

Products and orders is a very typical use case for relational databases, ...

One of many.

One reason you might want to have a relational database is you can query (for example) "What color umbrellas do men buy in winter" (from your stores, if sales are low perhaps they must go to a different store to obtain their favorite brand and color; without a "wish list" (non-binding sale) you don't know how you are coming up short you are assuming you know best - sometimes thst works, sometimes it's rejected - you can only ask what you know "from your stores" you can't query what's the latest fashion or most popular widget - the database doesn't perform miracles).

A relational database allows complex queries and analysis beyond "what do we stock and how many do we have". You can ask how long does it take for you to order from the manufacturer and have it in the customer's hands, that allows you to order in advance for seasonal items based on predicted sales from previous years.

You can determine which brands and colors that you have previously sold were most popular and order a greater number of those, you can't ask the database about information not contained within, why did the customer walk out without buying (price?, selection?, service?, or lack thereof).

You need additional sources of information like an email address, customer service department, feedback forms, polite and non-intrusive surveys (don't follow with a clipboard like some stores do), even free samples or EOL sales and discounts. The relational database alone won't solve everything.

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.