Hi All,

I would be tempted to think that an Invoice (once finalized, sent to the
client) should stay immutable. For example, I don't think that data like
customer address, item prices should change in past invoices to reflect
new data. Then would it be right to say that finalized invoices data
should be copies of the data at the time the invoice was finalized
rather than reference to other table data? I know it looks more like an
accounting question, but I guess some of you have already designed
Invoice system.

Thanks,
Dae

Dae.OS wrote:
Yes, this is one way to do it. I've done it this way (not for invoicing
but the same problem applied).

The other way is to disallow changes to data in a primary table after
dependent records in a child table are created. Instead, what appears
to be a change to the user must create a new record in the underlying
table(s).

For instance, there could be an account table with attributes that never
change (like creation date). Then another table for attributes that do
change (like billing address), as well as a foreign key to the primary
table. Then the invoice table reference both the account table and one
of the rows in the address table.

Regards,
Bill K.

Dae.OS wrote:
Yes. You don't want the recipient adress to change as the customer moves
around. Such a thing is a snapshot of a picece of data that you sent out
- a physical letter (or an email) with the information as it was at the
time.

You probably also have a customer id on record with each invoice too, so
joining with another up to date table to get the present address
shouldn't be a problem.

Regards,

Michael.
--
Which is more dangerous? TV guided missiles or TV guided families?
I am less likely to answer usenet postings by anonymous authors.
Visit my home page at http://michael.zedeler.dk/

"Dae.OS" <arsenault.daniel (AT) nxstream (DOT) net> wrote:



The rationale for invoices is that they are statements of fact as they
are at the time - if the customer moves, then simply change the
address in the customer table.

If for some reason you need to hold on to old, no-longer-true,
antiquated data, then that's fine too - you simply code that into your
application - more time, money, effort, disk space. It's up to you to
show that your business case merits the increase in expenditure.


Paul...


--

plinehan __at__ yahoo __dot__ __com__

XP Pro, SP 2,

Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.1.0;

When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.

Furthermore, as a courtesy to those who spend
time analysing and attempting to help, please
do not top post.

On Tue, 06 Jun 2006 22:36:37 +0100, Paul <paul (AT) see (DOT) my.sig.com> wrote:

[snip]

Antiquated?

"And what address did you send that invoice to?" "Ah, er."

Sincerely,

Gene Wirchenko

Yeah! That was my concern. Anyway, in an accounting point of view, I
don't think that Invoices should be allowed to change. Not only address
but item prices should also reflect the price paid, not the current
price for an Item.

I think storing the price directly (along with the item_id) within
InvoiceDetail is no big deal... doesn't take a lot of storage. But for
the address... I was thinking about Bill suggestion "to disallow
changes to data in a primary table after dependent records in a child
table are created". Since business address usualy don't change that
often, it might be simpler to create a trigger fonction that would
check if address_id appears in Invoice table and then make the
appropiate action; create a new address for the customer or simply
update the current address.