Sanity Check

[Dave 14867]

Hello,

I am trying to develop a new Inventory Tracking Database and would like input from other who have already done it, maintain one, etc before I get to far. I am not that experienced in database development, a newbie would be the best description.

I want to populate the tables in the attached design with known data and when complete I want to be able to tell where a part is used, how many etc.

Also when Parts are ordered, what was the PO that they were ordered on, qty, etc.

When a unit is complete, I want to be able to run a query (I believe that is the way to do it) to deduct the required parts to build 1 Unit from the Quantity Available.

I would appreciate it is some of you folks could take a look at what I have so far and give me input as to whether or not I am approaching this correctly or not and any other suggestions you may have to assist and keep me from going to far astray.

Thanks

Dave

 

[The_Doc_Man]

Don't forget to search this forum for key topic "Inventory." It has come up dozens of times with many detailed threads that, while not necessarily of immediate value, might at least give you some ideas.

 

[plog]

Big picture--I don't think you have a clear idea of where part information goes in relation to your other data. It's in too many spots.

Scattershot of specifics that I see wrong:

Vendors - too many fields. See no reason for part number data in this table (ID, Qty, price, etc.). A Vendor table should simple list vendor data, not part information.

Purchase Orders - PartNum shouldn't be in this table. You should have a Line Item table (which I think might be Transactions) that details what parts are part of a PO.

Transactions - UnitSerialNumber seems to be information related to the part, not the transaction. Shouldn't this field be in the PArts table? Also, TransactionDate sems like it would just be PODate, no need for 2 fields for the same data in two different tables.

Part Numbers - QtyAvailable shouldn't be a field. That sounds like something you should be able to calculated from your transactions table. No need to store data you can discern from other data.

Where Used - Not certain of the purpose of this table. See SOP_ prefixed fields, but no SOP table.

 

[Dave 14867]

Plog,

The UnitSerialNumber field is where I was going to store the serial Number of the final unit produced. I then want to be able to run a query that will take all of the parts required for that Unit and subtract that number from each line item as required from the Part Number Table, that's where I am storing the qty on hand.

I see what your saying about the Vendor table having redundant info, I will correct that.

I also see that PartNum shouldn't be in the Purchase Order table, That too will be corrected.

The Where Used table I was going to populate with various SOP numbers (Procedures), and steps in the SOP's so that I could then have a query that would tell me each SOP and Step that calls for a particular Part. Is there another way to do that?

Part Number table - I have the QtyAvailable in there and was going to adjust that by a calculation based on receipt of parts ordered, and reduction of parts used when a unit is complete, does that make sense?

Thanks for the input, I want to avoid making mistakes while doing this and taking advantage of others knowledge that already have and have a better understanding of all that is needed. I know what I want it to do, just not sure how to design it.

I'll make the changes you recommend and repost the results if you could check back soon.



Thanks

Dave

 

[jdraw]

Dave,

Watch this youtube video for ideas.