sql - 具有服务器和本地副本的记录的数据库设计

Question

我正在构建一个 iOS 应用程序，它可以处理(创建、编辑)与服务器数据库同步的记录(在 sqlite 中)。如果应用程序中的记录已从服务器下载，然后在本地修改，我正在制作副本，因为我希望能够恢复到服务器版本。因此，对于给定的记录 ID，我有时可以有两个副本(服务器、本地)。我正在寻求有关设计数据库布局的帮助。

最初，我使用了两个表 - 一个用于存储服务器记录(通过同步到达)，另一个用于存储本地修改/本地创建(尚未同步)的记录。我发现这种方法很麻烦，因为 (a) 我需要进行聚合搜索(选择优先考虑本地修改副本的记录)，(b) 我需要将数据从一个表移动到另一个表，这听起来不像一个很好的做法，并且 (c) 模式非常复杂(数百列)并且保持两个表模式同步很困难。

然后我将所有内容合并到一个表中，添加一个状态列(服务器/本地)。这看起来不错，直到我意识到过滤重复记录(那些同时存在服务器和本地副本的记录)是多么复杂。计数、搜索、选择所需的 10 行复杂查询(由于 sqlite 的限制)- 请参阅我的其他问题 here和 here .

我现在正在考虑将所有内容保存在一个表中，但放弃状态列，并创建一个单独的表来跟踪状态，每条记录一行，如下所示:

数据:

id    recordID  name                                 col2   col3   ...
1     1001      Server record, not changed locally   xxxx   xxxx   ...
2     1002      Server record changed locally        xxxx   xxxx   ...
3     1002      Server record changed locally        xxxx   yyyy   ...
4     1003      Record created locally               xxxx   xxxx   ...
5     1004      Server record changed locally        xxxx   xxxx   ...
6     1004      Server record changed locally        xxxx   yyyy   ...

状态跟踪:

id    recordID  server  local
1     1001      1
2     1002      2       3
3     1003              4
4     1004      5       6

聚合上述信息以显示将意味着显示本地记录(如果有)或服务器记录 - 在这种情况下，数据行 1、3、4 和 6。在这种情况下，我的查询会更简单(只是加入案例)。

这是最好的方法，还是我应该使用更好的设计？

Answer 1

我会去掉本地/服务器状态字段并引入时间戳/版本字段，一旦双方之一更改记录，该字段将设置为当前日期。因此，您始终知道哪一行是最新记录(具有最高时间戳的行)。为了让您恢复到旧版本的功能，我会考虑提供后退/前进一个版本记录的选项。如果用户选择要还原到一个旧版本，我会保存一个具有当前时间戳的版本的新副本。

您可以通过在您的协议(protocol)上传输全部或部分(未知时间戳)记录来实现简单的同步。考虑在客户端和服务器端转储给定表的时间戳列表，比较增量列表并为另一端准备插入语句。 Et voila，同步完成。您唯一需要始终考虑的是您不更新行，而是创建一个具有新时间戳的新版本。

这几乎是数据仓库系统中的最佳实践。 (无更新理念)

如果您发现每条记录有大量版本，如果有 N 个较新的版本，您可能会定期丢弃最旧的记录。