MySQL: tracking row creation and row change events with timestamps
7. November 2009
Keine Kommentare
The case for separate Creation and Change properties
Sometimes when modeling a database entity – a table – you might wish to automatically keep track of when the tuple – the row – was created. In theory this information is rather technical in nature, being synthetically produced by the coder to aid in housekeeping tasks such as:
- Knowing if you are trying to store the latest version of a row in concurrent write operations. Optimistic concurrency control, in which you always assume you are writing the latest version – unless the current timestamp is newer – is useful here. Actually known as “backward oriented”.
- Knowing which of several rows was written to the database first. Usually – if you use an AUTO_INCREMENT integer for your primary key, you can use that to know which row predates another row. If you are using a natural key for your primary key then a column “creation_time” or “created_at” could be useful. Perhaps you want to compare Dubletten and use the creation time to help judge which of the doubles to discard.
KategorienWeb Tips, webdeveloper
