Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2002
    Posts
    39

    Inventory and Point of Sales

    What would be the best method to link an inventory table and a products table?

    When submitting a transaction would the same tables be used to SALE or/and PURCHASE FROM A SUPPLIER? I want to be able to maintain inventory and it automatically update when a product is sold to a customer or a product has been purchased from a supplier?

    Thanks for any guidance...

    JE

  2. #2
    Join Date
    Mar 2009
    Posts
    10
    wow... no one replied. i hope you're not still waiting for your answer.

    i have the exact same question though. what's the best way to achieve this.

  3. #3
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    The products table is used to describe ONLY the parts themselves - not the inventory, not the vendor, not the price (but, it MIGHT contain the last purchased price.)

    A separate inventory table can be used to hold a reference to the parts table, the location, and the quantity on hand, at that location.

    Often, a third table is used to store the inventory Transactions. i.e. Qty of ITEM A moved from InventoryLocation1 to InvemntoryLocation2 On ThisDate for ThisReason. When you move inventory, you decrement the FROM location and Increment the TO location. These table adjustments should always be contained within a single transaction block.

    In order to adjust inventory down after a physical inventory count, you would move items from the 'real' inventory location to a logical "missing" location. Or, if the on-hand inventory count increased at a location when you took a physical inventory, you would move the increased quantity from a logical "poof" (because items magically appear) inventory location to the real location.

    This site may have some models that wouldn't be too far away from your needs.
    Last edited by loquin; 03-25-09 at 15:35.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  4. #4
    Join Date
    Mar 2009
    Posts
    10
    Yes thank you for your reply.

    It's starting to make a little sense now. Let me mess with it and see what questions come up.

    Thanks...

  5. #5
    Join Date
    Mar 2009
    Posts
    10
    Attached is a pic of what I got. Am I going the right way??

    I sort of have a few QtyLocation as INT columns in my Inventory table. Each tool can have a qty in each location. However this is probably not easily scalable.


    Thanks for the help.
    Attached Thumbnails Attached Thumbnails ToolDBjpeg.JPG  

  6. #6
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Well, maybe.

    It may not be appropriate to have the vendor ID in the tools table. For instance, suppose you have two tools which are functionally identical, except for the manufacturer. 24 inch Aluminum Pipe Wrench, for instance. One is made by Rigid, the other by Sears. Do you really want to have two records? What could be done is to have a many-to-many relationship between the tools, and the vendors. (using an intersection table)

    So
    Tools: ToolID*, ToolDescr, CategoryID

    VendorTools: ToolID*, VendorID*, VendorPartNum

    Vendor: VendorID*, VendorName/Address/Phone/...
    (The * indicates the Primary Key of the table)

    I would probably look at the inventory table a bit more, too.

    One suggestion would be to add a location table, where the locations could include "Scrap", "Shop", "ToolRoom", or an Employee ID. (if in the shop, the transaction record transaction type would define the reason - repair, sharpen, adjust, etc.) Having an anticipated return date field would also be useful, too. When you check out a tool that you expect back by 2:00 PM, it would be helpful to be able to let the manager know this, if he wants the info for scheduling.

    Then, in the inventory table, you would have the quantity at which location.

    Suppose you check out a pipe wrench to Joe. You would decrement the Toolbin inventory record for pipewrench by 1, Increment Joe's inventory record for Pipewrench by 1, and update the transaction record with the date that the transaction took place. Then, the pipe wrench gets broken and needs to be repaired. Joe returns the wrench to the toolroom, and a transaction record gets generated which decrements HIS inventory of the wrench, and increments the toolroom record - with a transaction code of Return. Then, you issue a repair transaction which decrements the toolroom inventory count, and increments the shop inventory count, sets the transaction code to Repair. And, since the shop told you that they would have it fixed by next Thursday, you store that day as the expected return date.


    One additional point. You might also want to have the ability to keep track of individual tools. Hammers, chisels, and the like, you probably don't need to keep track of individual items. But, when an item is very expensive - a portable welder, or a backhoe, for instance, these tools need to be handled somewhat differently. This is known as serialized tracking, where each item gets its OWN ID (Serial) number. In this case, you would want to include a serialized flag field (Y/N, True/False) in the item table. If a part is marked as serialized, then its inventory quantity can only be zero or one. And, your application needs to verify this for each item during transactions.

    On a side note, there's a third 'class' of item inventory management that, while probably wouldn't be needed in your case, often comes up, and that is lot controlled inventory. For lot controlled inventory, where you may not need to track the items individually, but you DO need to track the lot or batch number of the parts, you add another flag field in the item table. This Lot Control flag is used to let inventory control system know that lot control is required, and an additional Lot Number field is needed in the inventory table. And, you could have multiple, otherwise identical records with different lot numbers. The LotNumber field can't be part of the primary key of the table, but, it should be part of a unique key definition.

    Lot controlled inventory is very common in the automotive and aerospace industry, as these manufacturers must have a means of tracing those autos or planes which were built with a given lot number of component parts, for recall purposes.
    Last edited by loquin; 03-26-09 at 18:59.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  7. #7
    Join Date
    Mar 2009
    Posts
    10
    Thank you for the lengthly reply. You are gentleman.

    You are correct about the vendor tool intersection table. Then I could also put the vendor price in that table and be able to compare prices between vendors. Right?

    So I guess I'm on the right track. I'll post another picture when I update. I'm learning a lot at the same time I'm doing this so I thank you fro all your help.

  8. #8
    Join Date
    Mar 2009
    Posts
    4

    hi ...........

    hi
    how are u
    i am also problem in inventory management system data flow diagram
    can u help to correct it

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •