Saturday, July 18, 2009

The inner logical relationship of inventory database

Typical inventory software has next tables:

  • Items (Products)
  • Customers. You can create several Jobs or Contracts for one Customer
  • Vendors. You can create several Contracts or Branches for one Vendor
  • Locations -is your storehouse or warehouse with shelfs or office room. You can add a sublocation using the separator ":"
  • Vendor Types
  • Customer Types
  • Job Types
  • Purchase Orders and POrder Detail for tracking ordered items. When you create a POrder the Inventory software increases "On Order" amount in the Item form . Each record in the table POrder has relation with record in the table Vendor.
  • Receiving and Receiving Detail for tracking got products. When you create any Item Receipt the inventory system increases "On Hand" amount in the Item form and changes balance of the vendor. Some records in the table Receiving have relation with the table Vendor.
  • Vendor Payments for tracking payments to your vendors for received products. When you create the Payment the system changes balance of the vendor. All records in this table have relation with the table Vendor.
  • Estimates and Estimate Detail for tracking estimates or quotes to your customers. Each record from the table Estimate connects to one record in the table Customer.
  • Sales Orders and Sales Order Detail will help to keep track of items that you want to allocate for customers. When you create Sales Order you increase allocated amount in the Item Form. Each record in the table Sales Orders has relation with one record in the table Customer.
  • Invoice and Invoice detail . If your customers don't pay you in full at the time you provide your service or product, or if they pay in advance, you need to track how much they owe you. You create invoice if you shipping your products to a customer.The inventory software will decrease the On Hand amount of your items and changes balance of the customer.
  • Customer Payments for tracking payments from your customers for sold products. When you create the Payment the system changes balance of the customer.
  • Sales receipt. You use Sales Receipt if your customers pay in full at the time they receive your service or product. When you create a Sales Receipt the inventory system decreases "On Hand" amount but doesn't change the customer balance .
  • Work Order. You can create inventory assembly or new product using work order. You enter name of the assembly and select components for this assembly. When your create a Work order the inventory system increases the "On Hand" amount of the assembly item and decreases amount of the components. Each work order record connects with one record in the table Receiving Detail and several records in the table Invoice Detail.
  • Transfers for tracking the moving of products between your storehouse or locations. You can see stocks of the item on the Locations if you click on the button "On Hand" on the Item Form. each record in the table Transfer has connection with several records in the Receiving Detail table and in the Invoice Detail table.
  • Adjust inventory for physical count of your inventory.

Additional tables using in the transactions:


  1. Payment Terms
  2. Payment Methods
  3. Customer Messages
  4. Tax Code List
  5. Ship Via List

Records from these tables are used in invoices,orders and receipts.

No comments:

Post a Comment