Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2006
    Posts
    2

    inventory and job costs?

    I want to create a small database is for our non-profit's small print shop for tracking inventory and job costs, and I hope to later release it as open source. However, I cannot figure out a good schema.

    It seems too simple to have just these two tables to describe the cost of a job:

    INVENTORY_ITEMS
    * inventory_id
    * cost_per_unit
    * quantity
    * description

    JOB_ITEMS
    * job_item_id
    * inventory_id
    * quantity

    I see two problems with this approach:
    1. If later we buy more paper at a different cost and therefore update INVENTORY_ITEMS, then price of historical jobs becomes inaccurate.
    2. I'm not sure how to handle non-inventory job expenses. For example, say we outsource folding for $100 or pay post office $50 for postage?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    This will be trickier than it looks...

    You will need to move more to an "invoice" based system than inventory. Whereas the same item CAN come in twice at different cost, you need to track invoices to get accurate pricing. What gets funny is when you need to pull from two different invoices of the same item to accomplish one print job because you ran out of quantity...

    That's assuming you require a total cost per job instead of total operating costs to date.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Oct 2005
    Posts
    178
    Quote Originally Posted by ahz
    I want to create a small database is for our non-profit's small print shop for tracking inventory and job costs, and I hope to later release it as open source. However, I cannot figure out a good schema.

    It seems too simple to have just these two tables to describe the cost of a job:

    INVENTORY_ITEMS
    * inventory_id
    * cost_per_unit
    * quantity
    * description

    JOB_ITEMS
    * job_item_id
    * inventory_id
    * quantity

    I see two problems with this approach:
    1. If later we buy more paper at a different cost and therefore update INVENTORY_ITEMS, then price of historical jobs becomes inaccurate.
    2. I'm not sure how to handle non-inventory job expenses. For example, say we outsource folding for $100 or pay post office $50 for postage?
    YOu need to create another table and call it maybe transaction and on these variable fields allow changes for actual expense or actual charges.
    If you have written a front end interface for your database, use radio button to allow user to select actual charges and edit the field to enter value or select from inventory cost will populate from the inventory table..

  4. #4
    Join Date
    Jan 2006
    Posts
    2
    (Thank you, both Teddy and Fred.)

    Quote Originally Posted by Teddy
    You will need to move more to an "invoice" based system than inventory. Whereas the same item CAN come in twice at different cost, you need to track invoices to get accurate pricing. What gets funny is when you need to pull from two different invoices of the same item to accomplish one print job because you ran out of quantity...

    That's assuming you require a total cost per job instead of total operating costs to date.
    Yes, it is becoming trickier than I thought, and yes, we do want a per job cost.

    Any suggestions on how to implement what you described? Is there an elegant, easy way to pull the price from the correct invoice? My guess so far is that the database front-end calculates the total lifetime quantity used of (for example) blue paper. Then, the front-end subtracts the quantity from invoices until it finds the oldest invoice with remaining quantity.

    For each inventory item per invoice, should I "cache" the available quantity? For example:

    INVOICE
    * invoice_id
    * date

    INVOICE_ITEMS (links a vendor invoice with inventory)
    * invoice_item_id
    * invoice_id
    * inventory_id
    * units_purchased
    * units_available (cached/calculated value)
    * price_per_unit

    INVENTORY_ITEMS
    * inventory_id
    * description

    JOB_ITEMS (items in each job)
    * job_item_id
    * job_id
    * invoice_id (changed from inventory_id)
    * quantity

    JOBS
    * job_id
    * date

Posting Permissions

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