[UPHPU] Invoices & Billing

[Jonathan Duncan]

On Thu, 16 Nov 2006, Daniel C. wrote:

> 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.
>

You are dealing with two types of databases in one, really.  You have a 
dynamic database that is always being changed and updated (users, 
products, etct).  Then you have a historical database that will contain 
information that should never change (invoices).  In this instance, the 
best thing to do is to duplicate data, because really you are dealing with 
two separate databases.  Therefore, make your historical invoice tables 
mirror (to the extent you want historical data) the other tables instead 
of using foreign keys.  Make sense?

Jonathan