2

I've built several web applications using the Ruby on Rails framework and have noticed a common design problem: due to the variable nature of some data, I often need to store a copy of a record with an event that occurs at a certain time.

For example, consider a store application with products and orders tables. When a user places an Order, the application should store copies of the Product records in the order so as to preserve the state of those products at the time of the order. This way, future modifications to a Product do not affect past orders.

I've typically solved this issue by introducing another table, say order_products, to store the products associated with an order. Each OrderProduct then belongs_to a base Product, but still stores copies of the attributes of the Product; I've written save callbacks to keep the two records' attributes synchronized until the user places the order, decoupling the records.

An issue with this design is duplication of data fields between the products and order_products tables; whenever I modify Product attributes, I must remember to modify OrderProduct attributes in the same way and update the save callbacks. Moreover, if a Product has associations that must be copied to the corresponding OrderProduct, repetition in tables starts to become unwieldy.

How can I build a schema to store these product "snapshots" associated with orders without or with minimal repetition?

(Note that I've provided concrete products and orders tables here for clarity, but I've run across the same abstract problem in several different scenarios.)

EDIT: Here's a similar question whose author suggests using history tables to store snapshots: database structure for storing historical data. The accepted answer suggests the solution I presented above.

w_hile
  • 541
  • 6
  • 7

1 Answers1

1

Here are two designs that seem to minimize schema repetition in this scenario. Both solutions rely on single table inheritance (STI).

Option 1

STI option 1

Let RootProduct and OrderProduct extend Product through STI (add a type column to the products table). Also add order_id and root_id columns to the products table. A RootProduct represents a standalone product (not associated with an order, order_id = null and root_id = null). An OrderProduct, by contrast, is associated with an order, and also the RootProduct from which it was created. When a user adds a RootProduct to an order, clone the product as an OrderProduct and save a reference to the root product in root_id. As long as the order remains unplaced, synchronize the attributes of the two product records, but decouple the records after the user places the order. The OrderProduct records serve as product snapshots.

Option 2

STI option 2

As in option 1, let RootProduct and FrozenProduct extend Product through STI (add a type column to the products table). Also add a root_id column to the products table. A RootProduct represents the state of a product at the present point in time; a FrozenProduct represents a past snapshot of the product at the time when an order was placed. An order_products table is introduced to store the products associated with an order. Before an order is placed, each OrderProduct in the order links to a RootProduct. After an order is placed, each OrderProduct links to a FrozenProduct created by cloning the corresponding RootProduct.

w_hile
  • 541
  • 6
  • 7