Jim Dettman
jimdettman at earthlink.net
Mon Jul 7 09:49:26 CDT 2003
Bill, Don't have anything to send, but I'll explain the design I've used over the years. Has always proved quite flexible. BTW, 'PK' is primary key, 'CK' Candidate key, and 'FK' foreign key. The design below uses the surrogate key approach for the most part. tblInv - Inventory master - One record per stocking item InvID - PK Description StockUOM - Stocking unit of measure PurUOM - Purchase unit of measure ConvFactor - (PurUOMQty * factor) = StockUOM Qty Weight Volume ShelfLife ClassCode - Used to group inventory items ABCCode - A, B, or C item - Used for cycle counting, inventory analysis, etc. that would be the basics. Costing can be a separate table or done here. Depends on what your using this for; if MFG then you'll want a separate table. If something simple, then a cost field in the inventory master may do it for you. If your doing MRP, then you would add things like: PlanningTimeFence DemandTimeFence DockToStockLeadTime FixedLeadTime VariableLeadTime EOQ - (Economic Order Qty) IssuePanSize etc. tblWarehouses - One Record per warehouse WarehouseID - PK Description Address1 Address2 City State Zip Telephone PrimaryContact (Might want to break contacts out into a seperate table) tblLocations - One record per location per warehouse LocID - PK - Autonumber WarehouseID - CK1A LocCode - CK1B - User friendly code for a location (ie. Aisle/Rack Number) Type - Stock or WIP Nettable - yes/no tblLots - One record per location per part. If doing full lot tracking, then a new record gets created for every transaction. If not, once a lot record exists for a part/location combination, it can be used over and over. LotID - PK - Autonumber LocID - FK InvID - FK DateCreated OrigQty CurQty ShelfLife Cost tblInvTrans - One record per part per transaction - Records all the in's and out's (movement of inventory) of parts. TranID - PK - Autonumber TranDate - Date/Time of transaction EntryDate - System Date/Time InvID - FK TranType - 'I'n or 'O'ut DocType - Balance Forward, Order, Cust Ship, Receipt, Vendor Return, Cycle Count, etc. DocRef - Order #, PO#, etc that this transaction was driven by. ReasonCode - Reason for trans (i.e. Normal, Key punch error, Paperwork error, etc). Qty - note may be negative or positive to allow for reversals of transactions. LotID ProgID - Identifier that indicates program that generated this transaction. If the physical inventory table did not exist (tblWarehouses, tblLocations , and tblLots) then you would also use this to generate QOH. The sum of all transactions (based on Trantype and qty) would be quantity on hand. When purging, the sum of deleted records is brought forward with a Balance Forward transaction. DocType can be anything you want to identify the type of transactions that occur. DocRef is simply a reference, not an actual linking field. and last if your doing MRP: tblInvMRP - One record per part per commit/replenishment TranID - PK - Autonumber InvID - FK TranType - 'C'ommitment or 'R'eplenishment TranDate - Date/Time Commitment/Replenishment will occur DocType - Order, PO, or MFG Order. DocRef - Order #, PO#, etc that this transaction was driven by. ProgID - Identifier that indicates program that generated this transaction. WarehouseID Qty There is quite a bit here. Look it over and let me know if you have any questions. Jim Dettman President, Online Computer Services of WNY, Inc. (315) 699-3443 jimdettman at earthlink.net -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Bill Morrill Sent: Sunday, July 06, 2003 5:29 PM To: AccessD at databaseadvisors.com Subject: [AccessD] Inventory Database 7-6-03 Anyone know of a sample inventory/warehouse database or a good article about constructing such a database? Nothing fancy - just some basics and reminders. Thanks in advance, Bill _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com