[UPHPU] Invoices & Billing

[Daniel C.]

So I figure that if the only person on the list who thinks that my
idea is a good one is me, it's probably a good sign that it's not a
good idea.  So here's what I'm thinking now.  I'd appreciate it if you
could look over it and give me your thoughts.

The problem: come up with a way to store information about invoices
that is tidy (in all senses of the word) and does not change the old
invoices as rates change in the future.

The tables I'm envisioning using:

invoices: Has FKs to the customer, the invoice amount, date
information, and a few other bits of info.

invoice_items: Has a many-to-one relationship with invoices and a FK
to a list of all possible line items, as well as the rate that the
customer was charged for that item.

I figure I'll have one line in invoices for each invoice, and store
all of the information about what they were actually billed for in
invoice_items.  invoice_items will store the amount they were billed
for that item so that if the charge for it changes in the future,
we'll still have an accurate record of what they were charged at that
point in the past.

I figure the alternate way to do that would be to have a table with a
FK to fees, an dollar amount, and two date fields that shows when that
fee started and stopped being charged at that rate.  Then when we go
back to put together an invoice we select the fees on that invoice,
and pull out of the table I just mentioned where the invoice date is
between the start and stop dates, inclusive.

Any thoughts on which way is better?  The second way would duplicate
less data but it seems awkward.  Also feel free to suggest other
things I missed, ask questions, etc.

Dan