1

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
Improve this question
5
  • It's unclear to me why event_date, location_id and Inventory_status_id are not simply columns in order_items, with checked_out_flag a column of *inventory_status table. This reduces you to a single normalized table.
    – Pieter Geerkens
    Sep 14 '14 at 12:38
  • @PieterGeerkens, thanks for your comment. The columns you mention are required for inventory events not related to an order. E.g. when an item has been transfered to another location/store or is on service. The checked_out_flag is a denormalized helper for convenience and related to the inventory item itself. The same check can be achieved by checking the inventory_status_id (as you correctly pointed out in your comment).I guess I'm trying to achieve some kind of CQRS or event-sourcing scheme.
    – Jonah
    Sep 14 '14 at 15:27
  • You really should familiarize yourself with the details of how a traditional, paper=based, double-entry bookkeeping Journal and Subledger system works. Those who constantly reinvent the wheel willend up with square ones.
    – Pieter Geerkens
    Sep 14 '14 at 16:26
  • In many cases, the traditional Journal becomes the base table, and the subledger can be implemented as a Materialized VIew (or Indexed View in SQL Server), allowing a declarative rather than procedural implementation of the inherent redundancy..
    – Pieter Geerkens
    Sep 14 '14 at 16:28
  • @PieterGeerkens, ok so how should I mark an inventory item as stolen or on service in your recommendation above? Am I wrong to see similarities between a Journal/Ledger system and a CQRS system with rolling snapshots? Many thanks for your time!
    – Jonah
    Sep 15 '14 at 8:43
0

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.

Improve this answer
3
  • Good point on collapsing inventory_item and inventory_status tables! One question though; in both tables above you have the FK inventory_status_id (which to me is the PK in the inventory_status table). I still need a table to list all available statuses, such as stolen etc., so I assume you meant a new FK such a inventory_status_type_id? Thanks!
    – Jonah
    Sep 16 '14 at 16:26
  • Correct and well spotted. I will amend the answer.
    – Thomas Kejser
    Sep 16 '14 at 17:58
  • Happy to help Jonah. Good luck
    – Thomas Kejser
    Sep 17 '14 at 8:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.