0

I'm building a MySQL database and a frontend. One function of the database application is to make an invoice. I'm looking for a way to archive all of the data that went onto the invoice so that if some data changes, the invoiced data is still available.

For example: On table customers,

|---------+-------------------+-------------|
|  name   | email             | phone       |
|---------+-------------------+-------------|
|  George | george@domain.com | 3179094235  |
|---------+-------------------+-------------|

Suppose I make an invoice for George in year 2017. In 2018, George changes his phone number. I want my database to have a current phone number for George. Now in 2018, my customers table looks like

|---------+-------------------+-------------|
|  name   | email             | phone       |
|---------+-------------------+-------------|
|  George | george@domain.com | 2024123987  |
|---------+-------------------+-------------|

but I want the data that my invoice used to still be used in 2018 when I generate the invoice document again.

In the real world, much more data will be put onto the invoice. How can I make it so that the invoice that I generate in 2017 will use all of the same data as the one I generated in 2018, even if I've updated the data in my database? Invoice is currently generated from data from multiple tables: parts, labor, customer info, etc. Can I archive all of the invoice data to a table, and always generate invoices from the archive table? Snapshots? Transactions? I'm new to databases, so I don't know if I'm looking for a MySQL feature or a database design technique, or something else.

0

You have two things. You need two tables to handle such.

  • A record of Invoices. Once an invoice is issued, the row(s) that describe must not be modified. There can be many rows for George, assuming he is issued many invoices.

  • Current information about George, etc. This is updated as needed; there is one row for George.

It may be that, when issuing a new Invoice, you need to reach into the other table to update that info. So, simply plan on always issuing two statements, preferably in the same transaction:

INSERT INTO Invoices ...;
UPDATE Current ...;   -- No harm if nothing actually changes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.