Database model for Live and Draft Data

Question

I have been deliberating over the best way to hold a 'Live' set of data and a draft set of data in the database. The live version is displayed on a website and the draft version is worked on until until it is ready to go live. The model is relational and consists of many tables.

My current method is to have 2 databases, one for draft and one for live. When you promote the data to live, SQL just copys the data from the draft db to the live db.

This is fine, but a little slow and seems messy. And frequently SQL has to consider both sets of tables.

Another way would be to make a Live indicator as part of the key of each table, so I can hold both live and draft in the same table - and update the indicator to make the data live. - I am not really sold on this idea.

• The DBMS is Sybase.

I would be grateful if anyone has any other suggestions.

U.M.

Answer 1

have one table

DataTable
    DataKey
    DataMode
    Data...

PK is DataKey+DataMode
check constraint: DataMode IN ('L','D') --live or draft

You first insert with DataMode='D' --draft
when it goes live, copy it to the same table with a INSERT SELECT, set DataMode='L'
always edit the DataMode='D', and push it to the DataMode='L' when done with it.

when you want to show the data pass in the mode, use WHERE DataKey=@x AND DataMode=@Mode

I'm not sure how you'll handle deletes (draft delete then push that to live?) you may want to add a status column:

    DataStatus  --'A'ctive 'D'eleted

doing it in one table allows for common use of all sql to work for either "L"ive or "D'raft.

Answer 2

I think this is a prime candidate for effective dating. Add a start and end date to each table. If the current date is between the start and end dates or after the start date and end is NULL the record is live. Anything else is not displayed as live, allowing you to have your Draft data.

This would allow more than one set of draft data if the data is sequential and a history of previous values to be maintained. Or if data volumes is an issue, have a job that runs at times of low DB workload to delete any record with enddate less then current date.

Answer 3

Two ideas, depending on circumstances.

If some data remains current when a Draft version rolls in, and you just want to make new data effective all at once, you can have a Version Number for each data element, and a configuration option for the current active Version Number. Then write the logic so that the records selected have Version Numbers <= Current Version Number (ignoring higher Version Numbers). This would be low disruption, and perhaps handle what you mean by needing both versions available.

If you want to swap entire tables, and you can disconnect things (very) briefly, rename the tables. Probably less disruptive than the options you describe, and also could allow for simultaneous access.

Answer 4

Make the key whatever you are already using, and a boolean is_live column with default to N. When you want to publish, just update that column to Y.

Having this work will force you to review all queries so that they filter out the drafts when they should not be seen, but will save you of having to maintain 2 parallel tables with same formats and all that.