Accepted approach regarding data-modelling regarding Product - Order - deleted Product

Question

We are having a dilemma with modelling our data for a new application. The situation is somewhat complex; so I write down a simplified situation that is equivalent to our problem:

• In a web app, the administrator creates a list of products; title, discription, linked to certain product categories; etc. So there is a Product entity

• In the front-end, a user orders certain products; so there is an Order entity with a array of OrderLine entities associated with it; each OrderLine entitity has a Product associated with it.

• After a few days, multiple Products are ordered. The administrator however needs to alter the price of a Product, or even deletes a Product, since it is no longer available.

• In the database, there are still Orders associated with OrderLines, which are linked to Products. What happens when the adminstrator reviews the Orders in the database which where recorded before the product was altered in the database and that are associated to the product that was altered?

• With avoiding redundancy as much as possible; on a database-level, how would you model such a case?

Answer 1

I've modelled Products, Orders and OrderItems in a similar way; the only thing I have to add to @Neville K's answer is that I've previously made ProductPrices domain entities in their own right; adding a new price isn't the same thing as adding a new Product, after all. A ProductPrice has a price, a currency and a start and end date; modelling prices like this also makes it possible to have group-specific prices or discounts for particular customers.

Answer 2

The solution I've used to this problem is to record the changes to "Product" as a first-class domain concept. Typically, that means including "valid_from" and "valid_until" fields in the order table, and recording changes by setting the "valid_until" date of the current record to now() and inserting a new row for the product with a valid_from of now() and a null valid_until.

This allows your order to retrieve the product information as it was at the time the order was created, which in turn allows your business logic to reason about that data - honour the price as it was at the point the order was created, for instance.

An alternative is to include a "version" ID in the product table, as part of the primary key, and join the orderlines table to both product_id and version.

Deleting products should not result in a SQL "delete" statement - instead, the Products table should include a flag to indicate whether the product is still available. When a product becomes unavailable, you set the old record in the product table to have a valid_until of now(), and insert a new row with the "availableFlag" set to false.