How to improve this product rental schema?
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)  --&gt; inventory_items
location_id              (FK)  --&gt; locations (Store 1, Store 2 etc.)
checked_out_flag            
status_type_id           (FK)  --&gt; status_id (rented out, stolen, on service etc.)
comment


And you will have

inventory_events
==========================
inventory_item_id          (PK, FK) --&gt; inventory_items
location_id                (FK)     --&gt; locations (Store 1, Store 2 etc.)
checked_out_flag   
status_type_id             (FK)     --&gt; As per above 
comment
event_date                 (PK)     --&gt; 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.