Results 1 to 2 of 2
  1. #1
    Join Date
    May 2005

    Question Unanswered: How to design a table for Opening Balance

    Dear all

    I'm interested in designing a software which deals the stock transactions. I need to design a report to show the "Debit" , "Credit" and " Opening Balance" entries. How I handle in the table?


  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    is this openignbalance a one off data takeon or a regular process?
    if its a one off then treat it as a delivery to get the system up and running

    if its a regular requirement then you need to consider how to handle it. what Ive done int he past is have a transaction table and a transaction type table

    EffectonStock (+1 or -1), if +1 it adds to stock, if -1 subtracts from stock

    TX Code 'fk to code in TransactionTypes

    apply adjustments using an appropriate transaction type to give your reconciliation to future stock takes
    use a 'special code' which resets your transaction 'clock'...
    ie find the most recent Stock take "transaction", and sum movememnts since that point in time. this approach is not as neat as going back tot he dawn of the system time but it can make significant performance improvements

    archive of transaction in some way (whether thats through physical movement to another table, or setting a value that means don't count these rows in future stock transaction calculations. this is probably the more elegant and more in lace with accountancy principles. effectively you treat last periods closing balance as a 'delivery' into this period. if someone want s to query the history they can do so because the all the data is there, but the most recent data is marked as to be included in the stock. there is no significant implication perfomance wise on this approach especially if the marker is indexed
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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