What is the best way yo keep version on database?

I need to develop a web application. A web shop that sell different product in different package in different region ...

The admin need to modify thoses packages while end users wont be able to see his modifications. end user will be able to see those modifications only when the admin will publish the change.

my question is how can i design the database(or the webapp) so the admin will be able to see a different version (working version) of the application, while the end user will see another version (release version) 开发者_运维技巧?

i was thinking of adding a field 'version_id' on each table. Any idea? advice? link?

I'd store the pre-publish versions in a separate table and then update the "live" table when the admin publishes it.

This way you can keep the number of rows down in the table that's often read (the live table) which will cut down on the overhead - if it's a web shop the live table will be being read whenever someone visits that page.

If you're using MySQL you could also optimise the two tables so that the live version is using MyISAM which is (loosely speaking) better for heavy read operations and have the admin pre-publish table as an InnoDB table; InnoDB is (again loosely speaking) better performing on heavy write operations. That pre-publish table will only be accessed by very few (or even one) users at a time (the admin/s) but it may be updated frequently before publish as they realise they've misspelt something and change it.

If you want to incorporate a full version history you definitely want a separate table as you're going to end up with many more pre-publish versions in the table than live ones.