How to mark posts as edited?

Question

I would like to have questions marked as "Edited", but I dont know what the best way to do this would be.

Users post a question, people answer/comment on the question, and if necessary the user edits/updates the question (just like SO). I would like to note that the user edited the question, but I'm not sure of the best way to do this.

I was going to add a last_edited column in the table (because thats all thats really important to me), but I'm not sure if I should just split the edit times (and whatever else) into another table and record everytime the question gets edited.

EDIT: UIf I were to use a timestamp, what time would be used? Is there any way to insert a unix timestmap on update?

Answer 1

This depends on whether anyone (users or admins) ever cares about history of edits.

If they do, definitely split into another table

Answer 2

If all what you want is a mark Edited or not, you can just add a Timestamp column in the same table, populated to NULL by default, then populated by last update timestamp.

On the page if its populated then display its value, otherwise don't display the Edited icon/symbol.

Answer 3

If you only care about when the most recent update occurred, add a timestamp column in the same table.

If you'd like to keep track of every edit that's occurred, create a separate table that contains question_id and the timestamp.

Answer 4

Depending on how you're processing your date/time data, you can either store the unix time stamp (fieldname = unix_timestamp(now())), or as a regular mysql datetime field (fieldname=now()).

If you're going to do data sorting/filtering based on dates/times in mysql, then use the native datetime type so you can take direct advantage of all of mysql's date&time processing functions. If you're going to do most of the processing in your application, a unix timestamp tends to be more portable than a formatted date/time string.

As for the table structure, the simplest way to track edits is a pair of fields (lastedited, and lasteditor). If you want to keep a full list of editors and times, then you'll need a seperate table. If you're keeping track of the changes, then the versioning/trakcing information can go into the same table.