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.
我目前的方法是拥有2个数据库,一个用于草稿,一个用于实时。当您将数据提升为生存时,SQL只会将草稿数据库中的数据复制到实时数据库。
This is fine, but a little slow and seems messy. And frequently SQL has to consider both sets of tables.
这很好,但有点慢,看起来很乱。经常SQL必须考虑两组表。
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.
另一种方法是将Live指标作为每个表的键的一部分,因此我可以在同一个表中保存实时和草稿 - 并更新指标以使数据生效。 - 我并没有真正卖掉这个想法。
- The DBMS is Sybase.
DBMS是Sybase。
I would be grateful if anyone has any other suggestions.
如果有人有任何其他建议,我将不胜感激。
U.M.
4 个解决方案
#1
have one table
有一张桌子
DataTable
DataKey
DataMode
Data...
PK is DataKey+DataMode
check constraint: DataMode IN ('L','D') --live or draft
PK是DataKey + DataMode检查约束:DataMode IN('L','D') - 生活或草稿
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.
首先使用DataMode ='D'插入--draft在它上线时,使用INSERT SELECT将其复制到同一个表中,设置DataMode ='L'始终编辑DataMode ='D',然后将其推送到DataMode = 'L'完成后。
when you want to show the data pass in the mode, use WHERE DataKey=@x AND DataMode=@Mode
如果要在模式下显示数据传递,请使用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.
在一个表中执行它允许所有sql的共同使用适用于“L”ive或“D”raft。
#2
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.
如果要交换整个表,并且可以(非常)简短地断开连接,请重命名表。可能比您描述的选项破坏性更小,也可以允许同时访问。
#3
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.
我认为这是有效约会的主要候选人。为每个表添加开始和结束日期。如果当前日期在开始日期和结束日期之间,或者在开始日期和结束之后为NULL,则记录为活动日期。其他任何内容都不会显示为实时,您可以使用草稿数据。
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.
如果数据是连续的并且要保持先前值的历史,则这将允许多组草稿数据。或者,如果数据卷存在问题,请使用在低DB工作负载时运行的作业,以删除enddate小于当前日期的任何记录。
#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.
将密钥设置为已经使用的密钥,以及默认为N的布尔is_live列。当您要发布时,只需将该列更新为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.
完成这项工作将迫使您查看所有查询,以便在不应该看到它们时过滤掉草稿,但是将节省您必须维护具有相同格式的所有2个并行表。
#1
have one table
有一张桌子
DataTable
DataKey
DataMode
Data...
PK is DataKey+DataMode
check constraint: DataMode IN ('L','D') --live or draft
PK是DataKey + DataMode检查约束:DataMode IN('L','D') - 生活或草稿
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.
首先使用DataMode ='D'插入--draft在它上线时,使用INSERT SELECT将其复制到同一个表中,设置DataMode ='L'始终编辑DataMode ='D',然后将其推送到DataMode = 'L'完成后。
when you want to show the data pass in the mode, use WHERE DataKey=@x AND DataMode=@Mode
如果要在模式下显示数据传递,请使用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.
在一个表中执行它允许所有sql的共同使用适用于“L”ive或“D”raft。
#2
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.
如果要交换整个表,并且可以(非常)简短地断开连接,请重命名表。可能比您描述的选项破坏性更小,也可以允许同时访问。
#3
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.
我认为这是有效约会的主要候选人。为每个表添加开始和结束日期。如果当前日期在开始日期和结束日期之间,或者在开始日期和结束之后为NULL,则记录为活动日期。其他任何内容都不会显示为实时,您可以使用草稿数据。
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.
如果数据是连续的并且要保持先前值的历史,则这将允许多组草稿数据。或者,如果数据卷存在问题,请使用在低DB工作负载时运行的作业,以删除enddate小于当前日期的任何记录。
#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.
将密钥设置为已经使用的密钥,以及默认为N的布尔is_live列。当您要发布时,只需将该列更新为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.
完成这项工作将迫使您查看所有查询,以便在不应该看到它们时过滤掉草稿,但是将节省您必须维护具有相同格式的所有2个并行表。