I've been following the convention of adding created and modified columns to most of my database tables. I also have been leaving the modified column as null on record creation and only setting a value on actual modification.
The other alternative is to set the modified date to be equal to created date on record creation.
I've been doing it the former way but I recent ran into one con which is seriously making me think of switching. I needed to set a database cache dependency to find out if any existing data has been changed or new data added. Instead of being able to do the following:
SELECT MAX(modified) FROM customer
I have to do this:
SELECT GREATEST(MAX(created), MAX(modified)) FROM customer
The negative being that it's a more complicated query and slower. Another thing is in file systems I believe they usually use the second convention of setting modified date = created date on creation.
What are the pros and cons of the different methods? That is, what are the issues to consider?
I believe given the apparent trade-offs I'm going to go with modified = created strategy. In addition, I was curious how other web databases handled this and I noticed drupal seems to follow the convention of modified = created also.