I'm designing the database for an application that will need to track inventory. I'm not sure if it's good database design to use "inventory pulls" and "inventory pushes" tables to store each change to the inventory, and calculate each item's count based on those pulls and pushes.

Doing it this way would would allow me to associate a "pull" with another data entity, like the employee who pulled the item, or the order for which the item was pulled. On the other hand, it might make the application very slow in the future when the list gets really big.

I need to be able to associate these transactions ("pulls" and "pushes") to other entities, but I'm concerned about the performance of the application. If I use the inventory table method, I would need to track this data elsewhere.

Does this design choice make sense?

  • 1
    That design choice makes sense if you need that data and history. Only you know for sure if you actually need that information. – FrustratedWithFormsDesigner Apr 18 '17 at 16:20
  • I do need that information. If I were to just have an inventory table, I would need to keep track of that data elsewhere. – Hassan Apr 18 '17 at 16:20
  • 1
    Well if you need that information, this design will probably work. Myself, I'd probably just use one table with positive and negative amounts for adding to/removing from inventory, instead of separate "push"/"pull" tables, since I can't see any major differences between a "push" and a "pull" other than affecting the inventory amount positively or negatively - the data of a push/pull is structurally the same. Isn't it? – FrustratedWithFormsDesigner Apr 18 '17 at 16:25
  • That's actually a good point, I hadn't considered that. What would you call that table, "Inventory Transactions"? Anyway, I think this is a good answer. – Hassan Apr 18 '17 at 16:27
  • 1
    A term I've heard used in ERP systems is Ledger (from fairly generic term for pen-and-paper financial book-keeping), because an inventory is usually something with a financial value (i.e. how much did you buy or sell something for at the time of the stock adjustment). In pen-and-paper systems a ledger is usually a book with many Entries. In an electronic system it could be a table where each entry represents one stock level adjustment - such as resulting from a purchase, sale, positive/negative correction, refund to customer, return to supplier, etc. – Ben Cottrell Apr 18 '17 at 19:26

It does make sense to store pushes and pulls as events that have other qualities or properties associated with them, such as who or when.

It also makes sense to store the true inventory count, as that tally is often needed as well.

In other words these two are not necessarily mutually exclusive, even though the latter tallies can ultimately be derived from the former set of transactions: engineers do make choices to cache/store derivable information, hopefully based on measured performance considerations.

Storing events as an append-only log is, in part, what is behind Event Sourcing from Command Query Responsibility Segregation.

CQRS is an approach to scaling that seeks to separate access paths by type (command/request vs. query). This ends up with a segregated Write Model and the Read Model. Thus, the write model may be a normalized database, whereas the read model is denormalized for frequent application queries.

Event Sourcing builds on that, using an append-only log of events as the ultimate source of truth, allowing system restarts that build the write and read models from the events.

FYI, CQRS/ES is complex architecture designed to address performance at scale, and is probably not for everyone. Internally, it makes multiple copies of the information, in different forms, for different purposes.

| improve this answer | |
  • 2
    It's worth noting that while event sourcing and CQRS are two patterns that work well together, you can use either without the other. See Martin Fowler's description of event sourcing for a discussion of the pattern outside of the context of CQRS. – Jules Apr 18 '17 at 18:23

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.