Hello All:

   My application collects loan payments on loans held by auto dealerships.
I am beefing it up to allow them to add non-interest bearing other charges
such as if the car breaks down and the dealership does the repair and
finances it for the customer as a courtesy. These additional loands are
called sidenotes.

   My application has a header table and a payment table (child table).

    I display all active loans in a grid on tab one (header table) and show
payment history on tab 2 (payment table/child table), a summary on tab 3,
etc

   Header Table: The header table contains loan information such as amount
financed, a link to the customer name file, etc. Maybe 500-200 active
records at absolute maximum

   Payment Table: The payment table contains all payments. Payments could
be principle, interest, a side payment, or a late fee or a combination of
any or all of those categories. A customer would make their principle and
interest payment and then maybe a late fee or a side note payment with it as
well. Maybe 50*number of header records at a maximum. Small files

The structure Im thinking about using a structure as listed below. Does
anybody have any thoughts on this ???

Any thoughts on the fact that I have seperate columns for each category of
payment in the payment table ??? (Theoretically I could have multiple
records once for each type of payment made - but this seems over technical
and complex and would require me to regroup multiple parts of each payment
for display).

   My basic approach is to have 2 columns in the payment table:

       1 column for additional side note amounts loaned to the customer
       1 column for payments by the customer on those side notes

   In the header table - I'd have 2 corresponding columns which added up to
that detail

       1 column for total sidenote loan amounts originated
       1 column for total sidenote loan amounts collected
       (There would be other columns as needed such as SideNotePmtAmount,
NextSideNotePmtDueDate, etc)


   My logic for organizing the data like that is as follows:

   1) This is essentially the way I will display the data in most reports
   2) Speed - this allows the quickest presentation of the data

   Some other approaches might be to
   1) Have a seperate table for the sidenote loan amounts originated
       a) This is more theoretically correct but it results in having to
group data together for presentation which creates more code and slows down
master/detail displays of data (or at least I think it does) (detail becomes
a join of 2 tables)
       b) I have not tested this approach
   2) I could store the loan origination and loan payments in 1 column in
the payment table (but show the originated amounts as a negative payment)
       a) This seems confusing and awkward - and sort of crazy

Thank you for your time and consideration.


Neil Huhta

PS I am using DBISAM V1/Delphi 5 but will be upgrading to ElevateDB in the
near future.

Huhtaman


Reading I can't see where the info about what the sidenote was for is stored?

Roy Lambert

Roy,

   Thanks for the reply. I kept it simple for illustration purposes.
However, there would be a description column that might say "Engine Repair"
or "New Transmission".

    To fully normalize this, I should use an invoice type table and a
payment type table. That would allow for the most flexibility in there are
additional types of transactions that get added to the application.

   I have another question. If I create 2 tables - how can I merge the
"invoices" and the "payments" together ???

  For example consider the following

amount financed $ 1,000.00

payment table

CustNo   Date  Number  Amount   Principle    Interest    Sidenote    Descr
49           04/15/07  1       80.00       60.00          20.00
0.00       CashPmt
49           04/22/07  2      80.00        63.00          17.00
0.00       CashPmt
49           04/28/07  3      100.00      64.00          16.00         20.00
CashPmt
49           05/04/07  4       100.00    65.00           15.00         20.00
CashPmt

invoice table

CustNo   Date              Amount  Descr
49          04/20/07          120.00   Engine Repair Loan


How do I do a query to get a result set like the following ???


CustNo   Date  Number  Amount   Principle    Interest    Sidenote
SideloteLoan    Descr
49           04/15/07  1       80.00       60.00          20.00
0.00                             CashPmt
49           04/20/07  0         0.00         0.00            0.00
0.00        120.00          Engine Repair Loan
49           04/22/07  2      80.00        63.00          17.00
0.00                              CashPmt
49           04/28/07  3      100.00      64.00          16.00         20.00
CashPmt
49           05/04/07  4       100.00    65.00           15.00         20.00
CashPmt


Thank you


Neil Huhta







"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:8B513735-A3A3-4428-B9FA-D96F662B974B@news.elevatesoft.com...
> Huhtaman
>
>
> Reading I can't see where the info about what the sidenote was for is
> stored?
>
> Roy Lambert
>

Neil

Use a UNION

Something like

SELECT CustNo,
      Date,
      Number,
      Amount,
      Principle,
      Interest,
      Sidenote,
      SideNoteLoan,
      Descr
FROM Payments
UNION ALL
SELECT CustNo,
      Date,
      Number,
      Amount,
      0 AS Principle, /* these defaults are my guess! */
      0 AS Interest,
      '' AS Sidenote,
      '' AS SideNoteLoan,
      Descr
FROM Invoices
ORDER BY CustNo, Date, Number




--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz
+
Joan and Jeff Cook
The Cooks Oasis
www.cookislandsoasis.com