Set modified date = created date or null on record creation?
- by User
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?
