Let's say an Order has many Line items and we're storing the total cost of an order (based on the sum of prices on order lines) in the orders table.
--------------
orders
--------------
id
ref
total_cost
--------------
--------------
lines
--------------
id
order_id
price
--------------
In a simple application, the order and line are created during the same step of the checkout process. So this means
INSERT INTO orders ....
-- Get ID of inserted order record
INSERT into lines VALUES(null, order_id, ...), ...
where we get the order ID after creating the order record.
The problem I'm having is trying to figure out the best way to store the total cost of an order. I don't want to have to
create an order
create lines on an order
calculate cost on order based on lines then update record created in 1. in orders table
This would mean a nullable total_cost field on orders for starters...
My solution thus far is to have an order_totals table with a 1:1 relationship to the orders table. But I think it's redundant. Ideally, since everything required to calculate total costs (lines on an order) is in the database, I would work out the value every time I need it, but this is very expensive.
What are your thoughts?
