Design question for payment/charges record.

[EzLogic]

I am having a hard time coming up with the ideal table structure for invoice history.

Currently i have:

invoice C 10
charge N 10,2
payment N 10,2
pdate D
EmpID C 10 (and other fields for tracking purposes)

Now

When a user creates an invoice the table will look like this:

Invoice Charge Payment PDate EmpID
---------------------------------------------
1000 1200.00 0.00 02/18/2004 JOHN
1001 500.00 0.00 02/20/2004 MIKE

Now, the user wishes to make a payment from the payment screen for invoice 1000 and only make 500.00 Payment

Thus:

Invoice Charge Payment PDate EmpID
---------------------------------------------
1000 1200.00 0.00 02/18/2004 JOHN
1001 500.00 0.00 02/20/2004 MIKE
1000 0.00 500.00 02/20/2004 MIKE

And So on...

But, my problem is:
It is hard for me, using this desgin, to figure out which invoices have a balance and so on. I have a small routin that does it, and it works OK. But, we use both system. The one I made along with QuickBooks. Andy many times, quickbooks pays invoices and my system pays other invoices. Even though we enter the same invoice number in both system and have the same amount and same dates.

How do you go about designing a table for such activity. To figure out Invoice balance, aging, and such...

Maybe I am thinking too hard, or maybe I am being very shallow. I don't konw.

your feedbck is greatly appreciated.




Ali Koumaiha
TeknoSoft Inc
Farmington Hills, Michigan

 

[jimoo]

How about something like this?

1. An invoice table
2. A payment table that contains SEQ numbers.

Invoice CompanyID DOI Amount Desc
0001 111111111 12/30/2003 1000.00 Consulting
0002 111111111 01/15/2004 500.00 Consulting
0003 222222222 02/01/2004 2000.00 Consulting


Invoice SEQ DOP Amount
0001 001 01/14/2004 500.00
0001 002 02/10/2004 300.00
0002 001 02/09/2004 500.00


Now you can run a SQL or create a view that will show the invoice amounts, and sum of the total payment amounts to show how much has been paid.

Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[EzLogic]

Jimoo, what is the importance of the SEQ field?

Ali Koumaiha
TeknoSoft Inc
Farmington Hills, Michigan

 

[jimoo]

I usually add a seq field because I want all records to have a primary key in case they need to link to another table.

In this instance, the invoice+seq will make up the primary key.

The other reason I add it is because it makes it easier to ensure the data appears in the correct order for reports, etc.

ORDER BY invoice, SEQ




Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[SnyAc]

As an alternative to a seq field for data ordering I usually add a date and a time field and used those in the ordering to ensure the proper sequencing of the records for reports.

Andy Snyder
SnyAc Software Services

http://www.snysoft.com

 

[jimoo]

TeknoSDS

Regardless how you setup the key in the child table, I recommend having a parent/child relationship in this situation. I think you will find it more flexible to those partial payments.



Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[EzLogic]

Thanks guys for your suggestions. I will try it and will let you know how it works.

Ali Koumaiha
TeknoSoft Inc
Farmington Hills, Michigan