Is there any best way to implement version control for database content?

I'm currently writing a posting website that needs to have a version control on posts. I just don't know how I should implement this, in term of database and technique, in order to save and control the post.

Is there anyone experienced with this who 开发者_StackOverflowcan help me?

I've seen that Wordpress does version control only in 1 table, which is POST. I also suggest doing the same since it's trouble to write into 2 tables with the same amount of data and fields.

---

I know that stackoverflow stores deltas between versions. What I have seen others do is set up another table like the first one but with an author and a version or timestamp on it. You can push records over to the other table using a database trigger so you don't have to worry too much about making the change at the application level.

If you would like to use only one table then I would suggest adding the author, timestamp and a iscurrent flag. The flag isn't really needed since you can select the max version number but it will make your queries much easier. Set the flag only when the row is the highest version number. You can still use a trigger to populate the rows but watch out or you might end up in a loop of update triggers.

---

I would create two tables, one is "live version" table and the other is an "archive" table. When a new version is created, move the existing live version to the archive table (with appropriate timestamps and author notes) and add the new live version to the live table.

The archive table would have the same schema as the live table except that it would also have additional columns that would hold metadata about the versioning that you are supporting (version number, notes, timestamps, etc.).

---

Take this with a huge grain of salt, but, you could have a parent id that is joined to the primary key on the same table along with a bool that indicates whether its the current version. It's the method I used for a CMS system a while back... You might want a common id for a revision history (so that getting all historic entries for an item is non recursive). You could do this by including the first version's id with the all the subsequent versions so you could get the whole lot easily.

my .02

---

Sounds like you just want a record version number on the row. It's a number that identifies the latest version. Every time you update the data you actually insert a new row and bump the record version number. When you query to get the data, you just query to get the row with the max record version number. Triggers can be used to generate the record version number so you don't have to worry about generating the number when inserting.

If you want to go full-blown version control, you need some sort of status field on the row as well that tells you if this version is reverted/deleted/approved or not. When you get the latest, you select the the row with the max revision control number that has the appropriate status.

If you just want to save the history of the posts and not actually have revision control you can just use the record version number technique.

---

See also Implementing Version Control of DB Objects.