Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2009
    Posts
    12

    Unanswered: Calculate or Save values in a table

    Hi all,

    I'm working on a business project which includes a stock keeping function. Since stock depends on the transactions made in the past and a starting level, I can always calculate the stock level. As I understand, that is required to Normailze the db.

    However, after a while the number of transactions will get bigger and bigger and it will take more time to calculate stock. The number of transactions will be in the order of 1000's per year.

    My question: How "bad" is it to save stock after a transaction? Will the risk of non-Normalization outweigh the time to calculate?

    I would very much appreciate your thoughts on this.

    Jens

  2. #2
    Join Date
    Mar 2007
    Posts
    277
    I think this link should help here.
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  3. #3
    Join Date
    Dec 2004
    Location
    Connecticut
    Posts
    85
    Storing data that can be easily calculated is what normalization is meant to avoid.

    I've worked with ERP systems (that's what stock keeping becomes when the company grows up) for 20 years and every one keeps track of the quantity on hand by storing the current quantity.

    At some point you have to store the quantity on hand - if only to determine the starting point. And at some point in the future, you're going to verify the quantity on hand and start over again (you'll conduct an "Annual Inventory" so you can figure how honest your customers and employees are). Why not use that same field to keep the current quantity? Instead of saying "we had 500 on January 1" and calculate a balance from there, you say "we now have 357." When you perform a transaction, you add or subtract from the quantity on hand, keeping the balance current.

    Once you get started, you'll find that quantity-on-hand is used as a basis for so many other business calculations. Do you have enough stock to fill the orders you have? How much is your inventory worth?

    When you have 10000 SKU (stock keeping units) with thousands of transactions per year, answering these simple questions will be impossible.

  4. #4
    Join Date
    Apr 2009
    Posts
    12
    Thank you both for your ( different, but useful ) answers and time.

    Jens.

Posting Permissions

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