Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

UPDATE

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.

share|improve this question
    
Please explain why exactly you have to use do the second query instead of the first one now that you are using the modified = created scheme. –  user61852 Nov 7 '12 at 16:48
    
I'm not currently using the modified = created scheme. I'm using modified = null, but I'm thinking about switching. –  User Nov 7 '12 at 16:49
    
It's not clear why aren't both queries interchangeable. –  user61852 Nov 7 '12 at 16:51
    
SELECT MAX(modified) FROM customer WHERE modified <> created; –  user61852 Nov 7 '12 at 16:53
    
Using the default null scheme if I add a new customer the result of your query will still be the same. For caching purposes, I need the results of the query to change if a new customer is added –  User Nov 7 '12 at 16:55

3 Answers 3

up vote 6 down vote accepted

With modified = created if you want the latest modifications with never edited ones included you can rely on the modified column. However if the modified column is initialized with null you have to do a COALESCE(modified, created) which would perform worse.

With modified = created if you want modifications with never edited ones excluded you simply where modified != created, and with modified initialized null you have to do a where modified IS NOT NULL which would have a fairly similar performance, though slightly better and increasing performance with more records having a null modified column.

These are really the only differences, both give the same abilities to filter and aggregate data, you just have to use slightly different techniques for each. I prefer initializing modified = created to avoid the coalesces. Though initializing with nulls may depending on your database system save disk space, especially if edits are uncommon to the point that you'll have mostly nulls. Also if you have mostly nulls, then the performance of the where modified IS NOT NULL will be a good bit better than the where modified != created due to the smaller set meeting the condition.

Edit: Also, if you are interested in data based on modifications frequently enough that you would put an index on this column (pretty uncommon scenario but I don't know your use case), the index where modified = created would have different performance characteristics than initializing it with null, and any coalesces would with significant enough null's lose any benefit from the index because the coalesces will push the query off the index.

share|improve this answer
    
Doubt it'd save space, most date types are fixed width. –  MaximR Nov 7 '12 at 21:33
    
@MaximR Database systems may vary in this. If I recall MSSQL will use up 1 more bit for nullable fields on every record which is a part of a bitmask dictating whether the field is actually null or not, but then save the disk space of the actually null value by not writing the value to disk unless there is one. ...that is if I recall correctly.. –  Jimmy Hoffa Nov 7 '12 at 21:38
    
what a pain it must be when they go from null to not null in that case. shuffling all the content of the page around... –  MaximR Nov 7 '12 at 21:42
1  
    
@MaximR That probably depends on your page fill which shouldn't ever be too high in transactional data anyway, but I also may be remembering completely incorrectly about how that plays out, and I don't know any other DB systems well enough to say whether or not it would save disk space in any others. –  Jimmy Hoffa Nov 7 '12 at 21:48

I think you've summed them up already.

The big pro for initially having the last modified date equal to the created date are that your code is a lot simpler. Simpler code is good as it's easier to maintain.

The only con I can think of right now is that if you need to check to see if the record has been modified your code will have to compare two dates rather than just checking if the modified date is null. Not really a big downside.

share|improve this answer

Depending on what you use the dates for, another strategy to consider is to add version numbers. Then you can keep previous versions around for auditing and this can help to resolve conflicts when two clients read the data simultaneously and then both submit modifications (although you can get there with modification dates as well)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.