2

I am trying to wrap all interactions with inventory items into one transactional table. Currently I calculate QoH by querying multiple fact tables (production, acquisition, shipment, return order, and stocktake). I want to be able to calculate QoH, without rewriting code anytime a transaction type is added or removed, rather only update a record in a table. I also want the ease/completeness of querying the transaction table once and running a sum.

Each of these fact tables contains their own private relations to other tables. For example, order return, ties product received into inventory to a previously shipped order.

Do I essentially copy the data I need from the original fact tables and place it into the transaction table - creating redundant information. And supertype the fact tables to have a common id for use as a FK in the transaction table? Or is there something I am missing?

In summary, I need to have a common key amongst the various fact tables to isolate interactions with an inventory item to one table. I also need to maintain the private data/relations from each of the fact tables.

1
  • 1
    QoH: Quantity on Hand: "The total number of stock-keeping units (SKUs) that are physically located in the warehouse location at the current time"
    – Vy Do
    Commented Sep 30, 2014 at 0:24

1 Answer 1

2

Try making an inventory transaction table. This table will likely consist of all the columns common to your various transaction type records. For some record types, this may be all the columns. Add a transaction type column to differentiate the records.

You can then create an optional relationship to any subtypes which will contain the columns specific to the subtype.

Alternatively, you can add nullable columns for this data. This will require additional constraints to ensure only the right columns are set.

EDIT: If you have null columns, there will usually be the only table. The shipment column would be required for all types having a shipment id (might include returns), and NULL for other types. The alternative would be to have rows in InventoryShipments for shipments, and rows in InventoryReturns for returns. This quickly gets complicated.

If certain (transaction) types have a lot of type specific columns, you could use a hybrid model, with most types entirely in the Inventory table and some types with type specific table. This may occur when you use sub-type tables, as there may be types which only have columns which are common to all types.

Using either approach requires effort to ensure data integrity. Null columns should always be null for columns, and not null when required for the records type. If there are subtype records, there usually must be only one per inventory record.

3
  • 1
    Agreed. Further to this, if you think you really want to run queries against one particular transaction type, create views on the transaction table(s). If the big table with nulls approach is used, the views on the table will almost certainly be writable as well.
    – Joel Brown
    Commented Sep 30, 2014 at 2:30
  • Just to be sure I follow. You suggest only capturing facts related to an inventory item transaction in the "big null table", and only capturing specific info related to that transaction type in the optional relation? To query inventory items related to a specific shipment for instance, I would just search the new transaction table for all records with the optional shipment id? Seems to simple! :) Thanks.
    – N.Balauro
    Commented Oct 1, 2014 at 18:48
  • @N.Balauro It can be that simple. I've updated my response with some additional columns
    – BillThor
    Commented Oct 2, 2014 at 0:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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