I have a rental system where each product's stock item can be rented from a location and returned to a different location (think Alamo, Hertz etc.). When saving an order, first an inventory event is saved (to lock the inventory), and then each order item is saved.
I'm curious to know how my schema can be improved to avoid denormalization. Can I somehow use the event table in a better way as a base for my order items?
Also if necessary, how should I adjust my design to avoid data inconsistency?
inventory_events ========================== id (PK) inventory_item_id (FK) --> inventory_items location_id (FK) --> locations (Store 1, Store 2 etc.) checked_out_flag inventory_status_id (FK) --> inventory_status (rented out, stolen, on service etc.) comment event_date order_items ============================= id (PK) order_id (FK) --> orders inventory_item_id (FK) --> inventory_items status_id (FK) --> order_status (on hold, processed, prepared etc.) unit_price item_description comment deliver_at delivery_to_address_id (FK) --> addresses due_at returned_at