How to model parent status with individual child statuses

Question

I'm creating an application that allows you to input Orders, an order has some basic information: who ordered it, what date, etc. It also has a list of line items that are ordered. Each line item can (probably 50% of the time) have it's own delivery date separate from each of the other line items, they can also be started and cancelled individually.

I'm struggling with how to model the status of each order and how the user should be able to change those statuses.

One thought I've had is for the whole Order to be just Opened/Closed and have that computed based on whether all the line item statuses are Closed or not. The other option is to be able to set status explicitly on the overall order but then order status and line item status could get out of sync (why is the order closed if the line item is open).

Anybody have insight as to what would be a good approach here? Are there any examples out there that deal with this in an elegant way?

Answer 1

Your choice is essentially between speed of execution and "single source of truth." You'll have to decide which approach is better, based on which metric you prefer.

Calculating the status of each invoice is fine until you potentially run into performance problems because you're running over all of the invoices in a 30 day period to generate a report, and you need all of their statuses.

Storing the status, as you have already suggested, could potentially cause problems with the stored status getting out of sync with the actual status. In systems where everything is done through an API using transactions (and not by manipulating values in the database directly), the value getting out of sync would be considered a bug.

There are several ways to ensure that this value stays in sync. One way is via database triggers or stored procedures. Another way is to maintain the value through a transactional API, and only allow API calls to manipulate the database.

Since you have to calculate this status either way, my suggestion is to write the code to calculate it on a per-invoice basis, and use this calculation in your reports. If you find things slowing down, it's easy enough to switch to writing this value to the database during ordinary invoicing operations, using the very same code you've already written.