what is the best data model for simple Inventory tracking? (1 Viewer)

martinr

Registered User.
Local time
Today, 12:07
Joined
Nov 16, 2011
Messages
73
what is the considered to be the best database model for simple inventory tracking?
ie
  • product type X is purchased and sold,
  • product type Y is purchased and sold
with a query at any given point in time to show:
  • what is the current stock level of product X (total purchased - total sold)
  • a running (ie daily) balance of stock purchased, stock sold, current balance etc
what is the best model to achieve this:
  • one table with a field for Quantity and a field for transaction type (and convert qty sold to negative amounts)
  • one table with separate fields for Qty_purchased and Qty_sold
  • two tables - one purchased and one for sold
  • Other??
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 21:07
Joined
May 21, 2018
Messages
8,463
Have you looked at the Northwind design? I do not do these types of DBs (sales) so I am no expert, but that design seems to work well from playing with it.
 

zeroaccess

Active member
Local time
Yesterday, 20:07
Joined
Jan 30, 2020
Messages
671
I agree: when you open up Access, go to Templates and download Northwind.

It is a showcase of what can be done with a database, so while the structure is sound (the most important part), some of the methods on the forms would be altered by an experienced user.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 20:07
Joined
Feb 28, 2001
Messages
27,001
This site has MANY discussions regarding inventory. Each of us will probably give you a superficially different view on such things. Here is what I would answer as my summary, and this is ONE PERSON'S OPINION, probably to be joined by many others and NONE of us is guaranteed to be right or wrong for you. More likely, you will see some ideas and pick-and-choose from among what you read and synthesize your "perfect" answer for your own situation. (And none of us will feel slighted if you don't pick our main method.)

1. A table of items, with PK of some kind of part or item identifier. If not an autonumber, then perhaps a numeric SKU or a mixed alphanumeric field. Include descriptive data as needed.
2. A table of transactions with an autonumber prime key and a foreign key of the part/item identifier. These transactions will include type, cost, date, and quantity. More about this in a moment.
3. You would have purchase orders and invoices, probably as two tables, and you could have child tables of line items that tie back each invoice or each purchase order. The line items tie back to the transactions for full accounting of where the items originated and where they went.
4. Among all the "definition" and "translation" tables you might have, you would have a transaction type table that converts a transaction code into a longer name AND includes a direction: +1 for stock adds and a -1 for stock removes.
5. You would of course also have customer and supplier tables. The previously mentioned sales invoice and purchase order line items will be linked to the item table and either the invoice or purchase order table. Those tables will be linked to your customer and supplier tables.

A transaction will be a stock add due to a purchase order or a return-to-stock, and those two will have slightly different transaction type codes. You would also have a stock remove due to a sales order or a breakage/shrinkage/whatever. Again, different transaction types for each detail. You can have transactions called InventoryAdjustUP and InventoryAdjustDOWN (or shorter names) if you do a sight inventory and find that your count and your reality are out of sync.

To get a listing of stock-on-hand at any time, you write a summation query of a JOIN between the transaction table and that transaction-type table where you sum the transaction quantity times the direction (+ or - 1, NEVER 0), GROUP BY part-identifier. To get a listing of stock-on-hand as of a given date other than today, take that first query and add a WHERE XActDate < Targetdate.

One last wrinkle... after a while this inventory can get awkward. Sometimes you decide you have too many records and that they go back longer than you need to go for historical purposes. So you run one of those queries to pick a cutoff date and generate "replacement" transactions that are the sum of all transactions prior to the cutoff date. Then you do an insert of the summary transaction which has as its date the chosen cutoff date. Next, you immediately run a delete of all non-summary transactions for that part that have dates earlier than the selected summary date. I.e. the summary replaces all transactions prior to its date INCLUDING any previous summary transaction, of which there would only be one at a time for each part ID.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 21:07
Joined
Feb 19, 2002
Messages
42,981
Do NOT use any of the Microsoft samples for inventory. They are not fully formed and will only confuse you or get you to make poor design decisions.

My solution would be along the lines of what Doc recommended. To summarize.
1. Don't separate transactions into separate tables. All transactions should be in the same table and either + or - inventory.
2. When an Order is fulfilled, the ordered items should create inventory transactions. Although some systems encumber ordered items so as to not oversell, that is more complicated so let us know if you have that issue.
3. You also need to create other inventory transactions to handle returns or adjustments when cycle counts don't match your table records.

As you can see by the options that Doc and I have mentioned, inventory can get very complex. We don't have any idea what you need but simpler is better for someone new to development.

One method which newcomers frequently choose when they don't have to worry about serialized items (products with unique serial numbers for each instance), is to keep one inventory record per product with a bucket that they just add to or subtract from. This might seem pretty simple to implement but it is trickier than you can imagine AND it is not auditable. If you discover a discrepancy, you can never figure out how it happened. With the transaction tables, you see all the adds and deletes as separate items and you know what prompted them. That doesn't mean that you won't ever have a discrepancy in your cycle counts. It just means that it will almost certainly be the result of a physical problem rather than a programming one.
 

jdraw

Super Moderator
Staff member
Local time
Yesterday, 21:07
Joined
Jan 23, 2006
Messages
15,364
what is the considered to be the best database model for simple inventory tracking?
Further to what other members have advised, I don't know that there is a best. One that supports your business processes is what you want. The Northwind materials are OK for learning and concepts, but are not a business ready solution. As others have suggested there are several threads and articles accessible via Google/Bing to give you some ideas to help focus your requirement and thoughts.
There is a youtube video here from Software Matters that describes the design process. It isn't necessarily "best", but should give you a better appreciation of what "inventory/stock management" entails. This article by Allen Browne discusses Quantity on Hand is often-referenced.
There are several data models at Barry Williams' site.
Good luck with your project.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 01:07
Joined
Sep 12, 2006
Messages
15,614
In my opinion, the best simple inventory database is the old MS A2003 sample that I called Dharamsala Tea (the first product shown on screen is called Dharamsala tea). The only change I made is to show sales as negative quantities rather than positive, so that net stock movements can be quantified by a simple sum of all transactions.

See this thread where I uploaded the "Dharamsala Tea" database sample


I think this is the link to all the old A2003 samples, which MS removed after they released later versions.
 

Users who are viewing this thread

Top Bottom