UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Inventory Db - Correct Table Structure, Access 2016    
 
   
wheeledgoat
post Jul 28 2019, 08:49 AM
Post#1



Posts: 67
Joined: 18-December 18



Howdy folks,

I wanted to check in with you kind and helpful humans to ensure I start this off on the right foot. I've been googling around and think that I want to set up like:

tblItems (itemID, itemDescription, itemSize, itemLotNum, itemExpiration)
tblTransaction (trxID, itemID(FK), transTypeID(FK), trxDate, trxAmount, trxNote, CustomerID(FK))
tblTransType (transTypeID, received, used, waste, expire, transfer, manualAdj)
tblCustomers, etc...

On this board I've read many times where someone was admonished for storing a value that could be calculated, citing violation of 1NF... but I can't help but wonder - in 2022, is it optimal design that the db will have to pour through all transaction entries from 2019 forward to calculate current inventory? Something makes me want to create a "verified inventory" value that would be used.

Vital is the need to run detailed reports to review all previous transactions, though, which is somewhat incongruous with using a verified/reconciled inventory count and only calculating transactions since then.... or is it??

Any thoughts on the proper structure would be enormously appreciated!!


--------------------
"If you don't make mistakes, you're not working on hard enough problems." -Frank Wilczek

"Success is getting what you want. Happiness is wanting what you get." -B.R. Hayden

Virtue is sufficient for happiness, therefore a sage is immune to misfortune.
Go to the top of the page
 
Jeff B.
post Jul 28 2019, 09:02 AM
Post#2


UtterAccess VIP
Posts: 10,302
Joined: 30-April 10
From: Pacific NorthWet


You did say any …

I may not be understanding the fields/columns in the TransType table. Given the ?fieldnames? you listed, it would appear that in your situation, one given TransType could be both, for example, "waste" and "used". What data type are those fields?

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
orange999
post Jul 28 2019, 09:34 AM
Post#3



Posts: 1,972
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


WheeledGoat,

You may get some insight from this material by Allen Browne re Inventory.

--------------------
Good luck with your project!
Go to the top of the page
 
GroverParkGeorge
post Jul 28 2019, 09:34 AM
Post#4


UA Admin
Posts: 35,881
Joined: 20-June 02
From: Newcastle, WA


You have landed on one of the issues where the absolute prohibition of storing calculated values may not be the optimal approach.

In other words, you may want to create a "reporting" table with calculated inventory values to support that function. The problem, of course, is that once you have stored a calculated value, you've broken the chain of responsibility for the accuracy of that value. If, for example, a correction is made to an inventory item for six months ago, then all of the calculated values in your "reporting" table have to be updated to reflect that correction, or everything in that six month period fades into gray mist where you're making decisions based on flawed calculations.

I have a recent blog post about the dangers of allowing calculated values into your Relational Database Application. The point I would make is that the risks of using bad data for decision-making may outweigh any performance gains you get by storing calculated values. As is always the case, therefore, you have to decide what matters in your situation.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
wheeledgoat
post Jul 28 2019, 09:50 AM
Post#5



Posts: 67
Joined: 18-December 18



QUOTE (Jeff B)
I may not be understanding the fields/columns in the TransType table. Given the ?fieldnames? you listed, it would appear that in your situation, one given TransType could be both, for example, "waste" and "used". What data type are those fields?


The TransType would be what happened to the inventory. While "waste" and "used" would indeed both have the same result (negative) their difference would potentially make all the difference when reconciling inventory and looking for discrepancies. I found one post (sorry, I read so many, don't remember where I read it) where the suggestion was made to make all "negative" transaction types have an ID less than 50 and all "positive" types greater than 50. I think I can appreciate the value of that in simpler sorting and query statements.

--------------------
"If you don't make mistakes, you're not working on hard enough problems." -Frank Wilczek

"Success is getting what you want. Happiness is wanting what you get." -B.R. Hayden

Virtue is sufficient for happiness, therefore a sage is immune to misfortune.
Go to the top of the page
 
wheeledgoat
post Jul 28 2019, 09:53 AM
Post#6



Posts: 67
Joined: 18-December 18



QUOTE (Orange999)
You may get some insight from this material by Allen Browne re Inventory.


Of course! How could I have not thought to google "inventory" and "allen browne" together. Much appreciated - I'll doubtlessly find some guidance there. Thanks my friend!

--------------------
"If you don't make mistakes, you're not working on hard enough problems." -Frank Wilczek

"Success is getting what you want. Happiness is wanting what you get." -B.R. Hayden

Virtue is sufficient for happiness, therefore a sage is immune to misfortune.
Go to the top of the page
 
wheeledgoat
post Jul 28 2019, 10:20 AM
Post#7



Posts: 67
Joined: 18-December 18



GPG, thanks for the link (and writing that up). A point made most excellently. I heed your warnings loud and clear.

The system that we're losing (and thus needing to replace) includes "quantity before" and "quantity after" in many of the reports it runs. I don't have a peek behind the curtain on that db, though, so I have no idea of its structure. In your experience, does including that information in the transaction table help? I know it means storing even more calculable values which digs the hole deeper... but that information has an undeniable utility. But then as soon as you fix an error from last month, all before/after stored values are worthless - or worse than worthless: wrong!

There's no way I'm the first dude to be faced with this. There's no sort of standard on how to approach this db inventory debacle?

Now that I'm thinking about this, I'm curious on how the current (professionally designed, I assume) system will act on those before/after fields with a change on a prior date. I'm going to go find out...

--------------------
"If you don't make mistakes, you're not working on hard enough problems." -Frank Wilczek

"Success is getting what you want. Happiness is wanting what you get." -B.R. Hayden

Virtue is sufficient for happiness, therefore a sage is immune to misfortune.
Go to the top of the page
 
GroverParkGeorge
post Jul 28 2019, 12:45 PM
Post#8


UA Admin
Posts: 35,881
Joined: 20-June 02
From: Newcastle, WA


"There's no sort of standard on how to approach this db inventory debacle?"

Well, yes, yes there is. Calculate as needed and don't store the calculated values.

As we acknowledged, sometimes performance concerns can make a reporting version of your data desirable. See Data Warehousing, for example.




--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
jleach
post Jul 28 2019, 06:17 PM
Post#9


UtterAccess Editor
Posts: 10,145
Joined: 7-December 09
From: St Augustine, FL


I'll toss my pennies in as well. I've done a number of inventory systems (in fact, my first application had one for the manufacturing company I was running at the time, and I pored over these same questions then). Calculated on the fly is the way to go, skeptical as it may seem.

--------------------
Go to the top of the page
 
nvogel
post Jul 29 2019, 12:37 AM
Post#10



Posts: 1,011
Joined: 26-January 14
From: London, UK


QUOTE
There's no sort of standard on how to approach this db inventory debacle?

Of course. Most people would use a standard stock control and/or accounting system package from a software vendor or open source. Nowadays there isn't much reason to re-invent the wheel yourself.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th October 2019 - 11:57 PM