Michael, Personally I've always held sales/purchase ledger transactions in separate tables with a transaction type: INV - Invoice CRD - Credit Note CSH - Cash (Payment) AD- - Credit Adjustment AD+ - Debit Adjustment
I hold the transactions table as follows: PK - Primary_Key Date - Transaction Date ID - transaction ID (Invoice/Credit Note No. or user reference) Description - Transaction Description Value - Transaction Value (+ve if Debit and -ve if Credit) Link_PK - reference back to another transaction - Used to link Credit transactions back to the Debit that it pays off Come people like to hold the value field in two fields Debit)DR and (Credit)CR in order to simulate double entry book keeping but I prefer a single field and make the application change the sign of the amount to fit in with the transaction Type (Inv -> + and CRD -> -ve). As for actual invoices, I hold two tables. Header table, holds the PK, Document Id, Document Type, Delivery address, Invoice originating Address, Payment terms, Due date Detail Table holding one line per Document Item Stock Id, Description, Unit Price, Qty, Tax Code, Tax Amount, Discount, Analysis code etc with a FK link back to the Header table. You can use this design to enter Incoices, Cash, Credits etc etc and then the update produces a single ledger transaction (INV, CRS, CSH etc etc as per above) which is used for statements as well as aged debt reports. Obviously read the reverse signs for Purchase ledger. This method obviously keeps sales and purchase ledger completely separate which makes running a Nominal Ledger (for the US a General Ledger) impossible without an additional composit file, so if you need a General Ledger, so you can use the same Transaction table layout with the simple additioon of a "Source Ledger" code (P - Purchase, S - Sales, G - General) to indicate the origin of the transaction. Obviously the General Ledger will post directly into this file. In order to breakdown transactions within the General Ledger you can also hold an Analysis file which will break down each Leger transaction but I have found this unnecessary in the majority of cases. The standard Balance sheet, P&L report etc come directly from this composite transaction file but you need to be careful to set up both Sales and Purchase accounts, Debtors/Creditors control accounts as well as Share accounts etc etc to run a proper General Ledger so it becomes a little more complicated. Hope that this makes sense. Dave Crozier The secret to staying young is to live honestly, eat slowly, and to lie about your age -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of MB Software Solutions Sent: 30 October 2006 13:53 To: Profox Subject: Debits/credits database design I've got a couple small projects to do (hopefully) soon and they deal with customers invoices, payments, and adjustments. Asking those of you who've designed systems with these kinds of needs: do you make a table called "transactions" and just keep all of the data there, having a record identifier to show if it's a payment/debit/adjustment? If not, how have you designed tables to handle this data in the past? Apologies if not enough information there....it's a Monday! ;-) It's just discussion at this point, and nothing concrete. Consider this a "theoretical discussion" on design. tia! --Michael -- Michael J. Babcock, MCP MB Software Solutions, LLC http://mbsoftwaresolutions.com http://fabmate.com Work smarter, not harder, with MBSS custom software solutions!" [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
