Mysql – Help with updating and archiving data

archiveMySQLPHPsnapshot

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.

Best Answer

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