CODEWITHSUNDEEP
sqldatabasedatabase-designschemadatabase-schema
Alex
Alex

Reputation: 828

How should I deal with copies of data in a database?

What should I do if a user has a few hundred records in the database, and would like to make a draft where they can take all the current data and make some changes and save this as a draft potentially for good, keeping the two copies?

Should I duplicate all the data in the same table and mark it as a draft?

or only duplicate the changes? and then use the "non-draft" data if no changes exist?

The user should be able to make their changes and then still go back to the live and make changes there, not affecting the draft?

Upvotes: 1

Views: 159

Answers (2)

ryan1234
ryan1234

Reputation: 7275

Just simply introduce a version field in the tables that would be affected.

Content management systems (CMS) do this already. You can create a blog post for example, and it has version 1. Then a change is made and that gets version 2 and on and on.

You will obviously end up storing quite a bit more data. A nice benefit though is that you can easily write queries to load a version (or a snapshot) of data.

As a convention you could always make the highest version number the "active" version.

Upvotes: 2

Yonabart
Yonabart

Reputation: 68

You can either use BEGIN TRANS, COMMIT and ROLLBACK statements or you can create a stored procedure / piece of code that means that any amendments the user makes are put into temporary tables until they are ready to be put into production.

If you are making a raft of changes it is best to use temporary tables as using COMMIT etc can result in locks on the live data for other uses.

This article might help if the above means nothing to you: http://www.sqlteam.com/article/temporary-tables

EDIT - You could create new tables (ie NOT temporary, but full fledged sql tables) "on the fly" and name them something meaningful. For instance, the users intials, followed by original table name, followed by a timestamp.

You can then programtically create, amend and delete these tables over long periods of time as well as compare against Live tables. You would need to keep track of how many tables are being created in case your database grows to vast sizes.

The only major headache then is putting the changes back into the live data. For instance, if someone takes a cut of data into a new table and then 3 weeks later decides to send it into live after making changes. In this instance there is a likelihood of the live data having changed anyway and possibly superseding the changes the user will submit.

You can get around this with some creative coding though. There are many ways to tackle this, so if you get stuck at the next step you might want to start a new question. Hopefully this at least gives you some inspiration though.

Upvotes: 0

Related Questions