1

I was speaking to some friends about 'newer' ways of storing data in MySQL, and they were talking more about duplicating/editing a MySQL record rather than modifying the one and only.

Example:

ID | NAME | EMAIL          | DATE_UPDATED         | STATUS
1    John   jon@email.com    2018-01-01 05:00:00    PUBLISHED

So when an UPDATE command comes through, instead of editing record 1, it duplicates the record into something like this.

ID | NAME | EMAIL          | DATE_UPDATED         | STATUS
1    John   jon@email.com    2018-01-01 05:00:00    PUBLISHED
1.1  John   jon@email.com    2018-01-01 05:00:00    REVISION

This might not be exact, but similar concept. I am not sure the exact terminology on something like this, but I believe it has become a standard in most apps nowadays.

I am looking for some direction in best handling/creating something like this. MySQL triggers? PHP duplicate records etc. Along with how to handle the SELECT statement now that things have changed.

River
  • 8,585
  • 14
  • 54
  • 67
Justin
  • 2,502
  • 7
  • 42
  • 77
  • 1
    This may help: https://stackoverflow.com/questions/12563706/is-there-a-mysql-option-feature-to-track-history-of-changes-to-records – steadweb Jan 02 '18 at 20:29
  • Have two separate tables. One for history and one for current value. On update use trigger to insert existing value to history table and update current one. History table can even be in other database (like AWS DynamoDb) or even some logs file. – Justinas Jan 02 '18 at 20:29
  • That will be duplicating data.. bad idea. Better: have a log table where you log the changes. (who changed what from which to which and when did he so) – Honk der Hase Jan 02 '18 at 20:29
  • If I was doing this for my own project, I would have separate tables, one for the log and one for the current and most up-to-date version. – FluxCoder Jan 02 '18 at 20:41
  • @steadweb I like what they say here. Thanks for the resource. Thanks to everyone else on posting. – Justin Jan 02 '18 at 21:37

1 Answers1

2

Like most "best way to do...." questions, the answer here is "it depends"...

While going into all the details of why this way or that, some of the best answers come from "What do you understand and feel comfortable with for future maintenance?"

One table:

Some projects benefit from the one table method as they are based more on a 'transactionalized' set of data (for instance, processing payments, etc. - you need to know the full tracking of everything that went on).

To gather the latest value, simply do a MySQL query with an 'order by' and perhaps include a 'limit 1'.

Two tables (or more):

I'm often more a fan of the multi-table system as we often have one table that contains many columns of variable 'profile' data (userId, fname, lname, dob, etc.) and another that contains other 'current' info (credits, etc.)

These tables can be 3-30 columns and get to be quite a mess if trying to keep just one table (finding the most current data for any particular set of 'columns') as the transactions may be spread out over days.

However, we still keep a transactional table to track the changes (by whom, when, etc.) Usually this table is simple

transID affectedID byID action timestamp

What we have learned, though, it to use a millisecond timestamp on any table that is likely to get many requests quickly (like tracking various back-end processes that happen in PHP) so you can sort by the time. If you use regular timestamps you will get seconds and there can be a lot of data in that one second!

Apps-n-Add-Ons
  • 2,026
  • 1
  • 17
  • 28