实时和草稿数据的数据库模型 [英] Database model for Live and Draft Data

问题描述

我一直在考虑在数据库中持有Live数据集和一组草案的最佳方法。现场版本显示在网站上，草稿版本一直工作，直到它准备好上线。该模型是关系型的，由许多表组成。

我目前的方法是拥有2个数据库，一个用于草稿，一个用于实时。
当您将数据提升到生存时，SQL只需将db中的数据复制到实时数据库。

这很好，但有点慢看起来很乱并且经常SQL必须考虑两组表。

另一种方式是将Live指示器作为每个表的键的一部分，因此我可以同时保存和草稿在同一个表 - 并更新指标，使数据生存。
- 我并不是真的出售这个想法。

• DBMS是Sybase。

如果有任何其他建议，我将不胜感激。

UM

解决方案

有一个表

  DataTable 
 DataKey 
 DataMode 
数据... 
  

PK是DataKey + DataMode

检查约束：DataMode IN（'L'，'D'） - 活动或草稿

您首先插入DataMode ='D'--draft < br>
当它生效时，使用INSERT SELECT将其复制到同一个表中，设置DataMode ='L'

始终编辑DataMode ='D'，并将其推送到DataMode ='L'。

当您要在模式中显示数据传递时，请使用WHERE DataKey = @ x AND DataMode = @ Mode

我不知道你会如何处理删除（草稿删除，然后推送它）？你可能想添加一个状态列：

  DataStatus  - 'A'ctive'D'eleted 
  

一个表允许所有sql的共同使用为L或D筏工作。

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.

解决方案

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.

这篇关于实时和草稿数据的数据库模型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！