How to improve this product rental schema?

Question

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

Answer 1

When designing schemas that have a "current state" (in your case: What inventory is currently in use) and a historical log of states, it is generally a good idea to split those two concepts into two different tables.

In your case, you would have:

inventory_status
==========================
id                       (PK)
inventory_item_id        (FK)  --> inventory_items
location_id              (FK)  --> locations (Store 1, Store 2 etc.)
checked_out_flag            
status_type_id           (FK)  --> status_id (rented out, stolen, on service etc.)
comment

And you will have

inventory_events
==========================
inventory_item_id          (PK, FK) --> inventory_items
location_id                (FK)     --> locations (Store 1, Store 2 etc.)
checked_out_flag   
status_type_id             (FK)     --> As per above 
comment
event_date                 (PK)     --> Together with the item and perhaps the status.

Whenever status (or any other thing you want to track) changes, you write a new row in inventory_events and update the row in inventory_status. You may even decided to do this with a trigger. You could argue that this is a slight break with normalisation. But until someone invents a temporal database with reasonable performance, this is the fastest and easiest way to query the database.

From your description, it is not clear to me if an inventory_item can have more than one status at the same time (for example, can a car be BOTH rented out and stolen at the same time?). If each inventory_item can only have one status, you can collapse the table inventory_item and inventory_status into one without breaking normalisation.

A design like this has the advantage that joins are clean and easy to write. You do no need to look for the latest status - as that is always present in the inventory_status table.

A lot of ERP systems handle the balance sheet in a very similar manner - with a snapshot table (the balance sheet) and a log of transactions that add up to it. With the risk of jumping to conclusion, this may be the analogy that Pieter is cleverly hinting at.