-3

I am designing a inventory stock-management system.

My current understanding is that it should be similar to bank transactions, in which you take inventory from location A and put it at location B. In that case I would have a transaction table like so:

id  
product_id  
amount_of_units
from_location_id    
to_location_id

To get the quantities-on-hand, I would run through all transactions and spit the sum for a location. So far so good.

The problem comes with the fact that more than one thing can change at a time. It's status can become ready or waiting. To track status, I have to add 2 columns:

id  
product_id
amount_of_units
from_location_id    
to_location_id
from_status_id
to_status_id

I have 10 or so properties that can change, and I need to keep history of those. Do I add them all to this table with a from and to prefix? Or do I have to split those in separate tables: location_transactions and status_transactions, in which case how would I connect them?

Research: I read a bunch of questions and answers but they all revolve around simple transaction table or double-entry system. Non contain additional information for the item being transacted or changing properties.

mgPePe
  • 5,677
  • 12
  • 52
  • 85
  • 1
    [Inventory database design](https://stackoverflow.com/questions/287097/inventory-database-design) is one place. – Gilbert Le Blanc Aug 21 '21 at 16:21
  • I read that in the research - it doesn't seem to pose the same questions - `how do you structure the database if you have other properties changing of the inventory`? – mgPePe Aug 21 '21 at 19:37
  • You create separate tables for Product, ProductStatus, and ProductLocation. If you have seven more properties, you create seven more ProductProperty tables. ProductStatus has a status indicator and a timestamp. The structure is similar for all the other property tables. – Gilbert Le Blanc Aug 21 '21 at 19:43
  • Okay, so let's say I import 30 products `waiting` status. I make one record for the transactions table and one record with 30 units that are being `waiting`. If 15 become status `ready` then I make another record in the statusTable, is that correct? But how would I connect them? In other words, If I mark 5 as `broken` how would we say it is from the waiting ones or from the ready ones? There seems to bee the need for interconnectedness somehow... – mgPePe Aug 21 '21 at 19:52
  • You have a Transaction table for that purpose. Every property change requires a transaction row. The Product and ProductProperty tables hold the current status. The ProductProperty tables contain a history based on the older timestamps. The Transaction table shows the property change. – Gilbert Le Blanc Aug 21 '21 at 19:57
  • So the TransactionTable has a one-to-one with the ProductStatus and also with the ProductQuality? – mgPePe Aug 21 '21 at 20:07
  • Let me repeat: Every property change requires a transaction row. – Gilbert Le Blanc Aug 21 '21 at 20:09
  • I don't mean to be annoying, I am just trying to wrap my head around your concept. So TransactionTable has `id, amount_of_units, status_id, quality_id`. If I add 30 products i make 3 rows: `1, 30, null null` then `2, 30, 'related_id_of_status', null` and thirdly `3, 30, null, related_id_of_quality`? – mgPePe Aug 21 '21 at 20:22

1 Answers1

0

I'd probably create a Transaction table that looks like this:

Transaction
-----------
Transaction ID
Product ID
Units
Prior Status
Current Status
Prior Quality
Current Quality
...
Timestamp

From your prior comment example

1, 1, 30, null, intake, null, waiting, timestamp
2, 1, 15, null, null, waiting, ready, timestamp
3, 1, 5, null, null, ready, broken, timestamp
Gilbert Le Blanc
  • 50,182
  • 6
  • 67
  • 111