I'm developing a Content Management System (CMS) and I would like to include a "Save Changes" button. I mean: a user performs several changes in the database, but those changes are still not available. Just when the user presses the "Save Changes" button, those changes are "visible" for the website.

I have thinking in "SQL transactions". But I have understood that "SQL transaction" were designed to be used in a short space of time. And in this particular case the user may keep the transaction open for some time (one hour, two hours...).

How can deal with this kind of issues? I'm using PHP and MySQL?

  • 1
    I posted an answer and deleted it since I have questions first. Are you storing a history of changes for each change in the database? Like for updating an article, title, etc? Can you expand on your current design a little bit. A transaction can definitely be used to mark the changes as the most current version. You'd essentially have tables with all the drafts/titles/etc and then saving just marks the current state as the current one that should be publicly visible. – Sirisian Oct 26 '15 at 23:50
  • 1
    Typically the data you're talking about exists either on the client side or in the client session, and you only save to the database when they hit save. If you want to persist their unsaved session information after they logout it's better to have a separate "client scratch table" rather than try to have pending changes in the live table. – Móż Oct 27 '15 at 0:16
  • Are you asking how to present the delay to the user? – Kevin Krumwiede Oct 27 '15 at 4:48
  • Hi @Sirisian, the CMS acts directly against the database. It doesn't store a history of changes. – Cequiel Oct 27 '15 at 16:19
  • "when the user presses the "Save Changes" button, those changes are "visible" for the website." This button you want to include should be titled 'Publish' or 'Publish Changes'. There's no long running transaction in what you describe. – Only You Oct 30 '15 at 7:24

If you're storing a history of changes (drafts) with a flag to mark the publicly viewable state then your submit changes button can just set all the state on the page to be the publicly viewable state. You can use a transaction for this since it would serialize all the changes. This is especially useful if you implement a system for rolling back such state changes to previous configurations.

A more verbose explanation would be that if you had tables like:

articles(id, url)
article_titles(id, article, text, timestamp)
article_body(id, article, text, timestamp)
article_history(id, article, title, body, timestamp)

Then when the title is changed it just inserts a new record. When the body is changed or saved periodically it just inserts a new body record. Now when you hit publish it inserts into article_history and when displaying publicly to the user the renderer grabs the most recent article_history record and the corresponding title and body.

(Note, this is just an example to try to relate to what you described. I'm not saying this is a correct design for a CMS or good practice).


There are lots of content management systems and I have only used a couple, but I think what content management systems frequently do is maintain two databases or filesystems, one for you to edit content and the other for what the users see. I know the content management system I used to use a lot (Vignette, now OpenText) did that. There are two separate parts, Management/Preview and Delivery. Management is where you do your editing, and when you want to let users see the content, you publish to Delivery. Publishing involves copying from the Management database to the Delivery database.

This means that you could save changes many times before publishing, or never publish at all. For the content admins, it is much easier to use a system that lets you save and come back to your work later and publish when you are ready.


Think of SQL transactions as groups of SQL queries ran together in one connection to maintain database consistency, eg. when removing data, remove also any linked entries from relation tables to keep the database clean. If a problem occurs, you can use ROLLBACK to revert the changes in current SQL transaction. PostgreSQL can take care of consistency by itself and can do a lot more with larger projects.

You can log a history of changes or you can add another column to the table (text _draft). Visitors would still see original text, but while you would be logged as admin, you could check for any data in this column and display this instead of original text. By opening the two regularly and in anonymous window you could easily see the changes.


One of the approaches is versioning. You'd design your content table with version and active column. Every time you auto-save you bump the version but don't mark row as active. Once you hit Save you will bump version and publish the content. Gives you complete history at the cost of big tables. I think WordPress uses the same approach.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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