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