I have an database with books.
One book has one Author, Publisher. Some Prices, ID's and Descriptions.
I want to keep track of changes made to one product. One way is to save the product with time AND id as primary key.
Are there other ways?
Are there database systems (i've only used mysql) who can keep track of changes automaticly?
greetings...
You can use a trigger (if mysql has them, I think it does) to catch the 'update' event and enter a bunch of relevant information into a 'log' table.
Databases do have transaction logs, but probably it's not useful for you as I don't think it can be trivially queried.
A simple solution is to include a modified-date as a field in your product table.
Update your stored procedures to always pull the product with product-ID with the latest effective-date.
This would allow you to have a separate stored procedure that lists all versions of a product.
One approach is to have 2 separate tables e.g. books and book_versions with the same set of fields (author, publisher, description etc.).
Whenever your application does an insert or update into books you insert a corresponding record in 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're only interested in the latest version the majority of the time you can just select from books by ID and only retrieve the history when you need it. This is the approach used by the acts_as_versioned plugin for Ruby on Rails.
I propose to add a changelog table into your system. This table is only ever written to, and it has the columns date, subject, predicate, object, where subject is the author/principal making the change, predicate is the nature of the change (create, update, delete), and object is the thing being change. Potentially, you can split object still into id, attribute, value, where id is the book id, attribute is the string name of the attribute being changed, and value is the old value (as the new one is in the proper table).
What you are asking for is mostly covered by the "Change Data Capture" (CDC) design patterns and "Slowly changing dimension" (SDC) concept.
Read the Wikipedia articles on these subjects, as they provide a good birds-eye view on this topic.
Any of the suggested solutions above will work; it really depends on your workload and data set size.
If you have a lot of records and you just want historical archive for reference, you may also consider moving "old/earlier" versions off the database and instead, store them on disk in some kind of linked list format (e.g. insert a version that contains address of the previous version, hence forming a linked list), and just keep a pointer to the latest version in DB.
There are pluses and minuses with this approach, but one plus is you can keep your DB small, and just read off older versions from disk. Your older versions should be immutable so you won't need to rely on transactions/concurrency support from the DB. If your "current/up-to-date" data set is, say 100G, and your past versions is 900G, then you can put a database on RAID on the 100G and put the past versions on cheaper storage, and copy it a few times (they are atomic, so no concurrency issues when replicating).
You might be interested in the concept of temporal databases used to describe things that change in time. There is a freely available book on temporal databases that describes this concept in every detail, but for something more down to earth you could read Patterns for things that change with time by Martin Fowler, my favorite programming author.