Version Tracking with mysql

Question

I have a database with books.

One book has one author, a publisher. Some prices, identifier and descriptions.

I want to track changes made to a single product. One way is to save the product over time AND id as the primary key.

Are there other ways?

Are there database systems (I just used mysql) who can track changes automatically?

Hello...

Answer 1

What you are asking for is mostly covered by the Change Data Capture (CDC) Design Patterns and Slow Sizing Concept (SDC) .

Read Wikipedia articles on these topics as they provide a good look at the bird on this subject.

Answer 2

One approach is to have 2 separate tables, for example. books and book_versions with the same set of fields (author, publisher, description, etc.).

Whenever your application inserts or updates to books , you insert the corresponding entry into book_versions . This means that the books table contains the latest version of the record, and book_versions contains the latest and all historical versions. If you are only interested in the latest version, most of the time you can simply select from books by ID and only get a story when you need it. This is the approach used by acts_as_versioned for Ruby on Rails.

Answer 3

You can use a trigger (if they have mysql, I think it is) to catch the "update" event and enter a bunch of relevant information in the "log" table.

Databases have transaction logs, but this is probably not useful for you, since I do not think this can be requested trivially.

Answer 4

A simple solution is to include the modified date as a field in your product table.

Update stored procedures to always pull out a product with a product identifier with the latest effective date.

This will allow you to have a separate stored procedure that lists all versions of the product.

Answer 5

I suggest adding a change table to your system. This table is only written and contains the columns date, subject, predicate, object, where the subject is the author / main modification, the predicate is the nature of the change (create, update, delete), and the object is the thing - change. Potentially, you can still divide the object into id, attribute, value, where id is the identifier of the book, attribute is the name of the line of the attribute to be changed, and value is the value of old (as new is in the corresponding table).

Answer 6

Any of the solutions proposed above will work; it really depends on your workload and the size of the dataset.

If you have a lot of records, and you just need a historical archive for reference, you can also think about moving the "old / earlier" versions from the database and instead store them on disk in some sort of linked list format (for example, insert the version containing the address of the previous version, and therefore forming a linked list) and just keep the pointer to the latest version in the database.

There are pros and cons with this approach, but one plus - you can keep your database small and just read old versions from disk. Your old versions should be immutable, so you won’t need to rely on transactional / concurrency support from the database. If your “current / current” dataset is, say, 100G, and your past versions are 900G, then you can put the database on RAID in 100G and put the past versions on cheaper storage and copy several (they are atomic, so when replicating no concurrency problems).

Answer 7

You might be interested in the concept of temporary databases used to describe things that change over time. There is a freely available book on temporary databases that describes this concept in every detail, but for something more than on earth, you can read Patterns for things that change over time by Martin Fowler, my favorite author.