mysql - Having to insert a record, then update the same record warrants 1:1 relationship design? -

let's order has many line items , we're storing total cost of order (based on sum of prices on order lines) in orders table.

-------------- orders -------------- id ref total_cost --------------  -------------- lines -------------- id order_id price -------------- 

in simple application, order , line created during same step of checkout process. means

insert orders ....   -- id of inserted order record insert lines values(null, order_id, ...), ... 

where order id after creating order record.

the problem i'm having trying figure out best way store total cost of order. don't want have

1. create order
2. create lines on order
3. calculate cost on order based on lines update record created in 1. in orders table

this mean nullable total_cost field on orders starters...

my solution far have order_totals table 1:1 relationship orders table. think it's redundant. ideally, since required calculate total costs (lines on order) in database, work out value every time need it, expensive.

what thoughts?

i echo other answers , that, unless it's unacceptably expensive so, i'd calculate total cost lines table as-and-when required.

alternatively, 1 define triggers update orders.total_cost appropriate. however, 1 need define triggers after insert, update , delete on lines:

create trigger after_insert_lines after insert on lines each row   update orders set total_cost = total_cost + new.price;  create trigger after_update_lines after update on lines each row   update orders set total_cost = total_cost - old.price + new.price;  create trigger after_delete_lines after delete on lines each row   update orders set total_cost = total_cost - old.price;