Database Question - Data Model 1

[advpay]

Does anyone have a Data Model for receivables system that tracks and applies payments to invoices?<br><br>A simple receivable system has a foreign key from tblCashReceipts on CustomerID...what I need is to track and apply payments by invoices.<br><br>I know that I need to change my foreign key from tblCashReceipts on CustomerID to tblCashReceipts on tblInvoice on InvoiceNumber...as CustomerNumber would not be a field in tblCashReceipts.<br><br><br>tblCustomer<br>CustomerID/LastName/PrincipleBal/AgencyInt/ CurrentBal<br>&nbsp;&nbsp;990291&nbsp;&nbsp;/&nbsp;&nbsp;RICH&nbsp;&nbsp;&nbsp;/ $845.56 / $0.00&nbsp;&nbsp;&nbsp;/&nbsp;&nbsp;$825.18 <br><br>tblCashReceipts<br>CustomerID/InvoiceNum/TypeTrans/Date/Amount<br>&nbsp;&nbsp;&nbsp;990291 /9902918749279/DBJ/11-15-99/825.18<br><br>tblInvoice<br>CustomerID/BillAmt/BillDate/InvoiceNum <br>&nbsp;&nbsp;990291&nbsp;&nbsp;/845.56 /10-15-99/9902918749279 <br><br>tblFinDetail<br>CustomerID/TypeTrans/DescTrans/DateTrans/AmtTrans<br>&nbsp;990291/&nbsp;&nbsp;&nbsp;DBJ&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;/10/99Bill/10/15/99 / $845.56 <br>&nbsp;990291/&nbsp;&nbsp;&nbsp;CRJ&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;/ADJ COR&nbsp;&nbsp;/11/24/99 / $20.40<br><br>I would appreciate very much some help with this...thanks<br><br>&nbsp;<br>

 

[CharlesDFW]

Sorry, I lost you in all of that.<br><br>However, as best I can figure, it looks vaguely like a many-to-many join table might work with one table's record recording invoice, customerid, and payment amount.<br><br>Does this fit? Or am I completely off the wall?<br><br>

 

[elizabeth]

I'm pretty rusty on my Accounting but here's a few ideas...<br><br>1) on tblCustomer: Lose the principle balance, which belongs on the tblInvoice only, and lose the current balance, as calculated amounts should not be stored in tables, they should be recalced in each query or report&nbsp;&nbsp;I don't know what&nbsp;&nbsp;AgencyInt is. <br>2) on tblCash Receipts, create an ID (ReceiptsID? as a Primary Key (PK). Lose CustomerID, as it is also belongs only on tblInvoice. I don't know what TypeTrans is but you may need a lookup table for it.<br>3) tblInvoice looks good, I think.<br>4) Don't know what you're trying to achieve with this table. Changes other than cash reciepts? If that's all you don't need a separate table for Cash Reciepts and other changes, just use one table with a fileds that indicates the type (like trans type maybe?) <br><br>Here's how I'm seeing it:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br><br>tblCustomer: CustomerID(PK), LastName, other fields describing Customer.<br>tblInvoice: InvoiceNum(PK), CustomerID, etc as is.<br>tblTxn: TxnID(PK), InvoiceNum, Amt, Date, TxnType<br>tblTxnType: TxnTypeID(PK), TxnTypeDesc<br><br>In case the relationships aren't obvious,<br>tblCustomer 1:n tblTxn<br>tblCustomer 1: tblInvoices<br>tblInvoices 1:n tblTxn<br>tblTxnType 1:n tblTxn<br><br>

 

[advpay]

Thanks will try that...