GUID / UUID这种情况？还是复合键？或其他 [英] GUID / UUID for this scenario? Or compound key? Or other

问题描述

我目前正在处理一些系统，在某些情况下需要在白天在本地数据库上运行，然后在夜间复制到中央服务器。它不能都从一个中央数据库运行，因为本地站点定期与其脱离联系。中央服务器上的数据仅用于总部的查看和报告，因此无需反向复制回站点。

I'm currently working on a system that in some cases will need to run on a local database during the day, and then replicated to a central server during the night. It cannot all run from one central database as the local sites are out of contact with it periodically. The data at the central server is for viewing and reporting only at head office, so nothing needs to be reverse replicated back to the site.

每个站点都被赋予一个基于文本的唯一密钥（人为生成）。但是，将数据库设计中的每个表格引用到站点密钥的想法并不具有吸引力。

Each "site" is given a text based unique key (human generated). However, the thought of making every table in the database design reference the site key is not appealing.

这是一个非常简单的模式版本的示例，没有担心远程复制（这对大多数客户端都可以正常工作）： -

Here's an example of a very cut back version of the schema without worrying about remote replication (which will work fine for the majority of clients) : -

（我将只显示区域表的历史记录表，以保持简短）：

(I'll only show the history table for the Area table, to keep things short) :

[Site]
SiteKey [PK] (Gauranteed 100% unique across all sites text based key)

[User]
SiteKey [FK -> Site]
UserID [PK]

[Area]
SiteKey [FK -> Site]
AreaID [PK]
Description
UpdatedDtm
UpdatedUserID [FK -> User]

[AreaHistory]
Site [FK -> Site]
AreaID [FK -> Area]
Description
UpdatedDtm
UpdatedUserID [FK -> User]
AuditedDtm

[Location]
AreaID [FK -> Area]
LocationID [PK]
Description
UpdatedDtm
UpdatedUserID [FK -> User]

[Sensor]
LocationID [PK / FK -> Location]
SensorNo [PK]
UpdatedDtm
UpdatedUserID [FK -> User]

[Reading]
LocationID [PK / FK -> Sensor]
SensorNo [PK / FK -> Sensor]
ReadingDtm [PK]

哪个是好的，直到我来合并数据库与数据库在中央服务器。我显然会在位置表中发生冲突，因为我正在将数据与其他站点上生成的ID进行混合。

Which is fine, until I come to "merge" the database with the database at the central server. I'm obviously going to get clashes in the Location table because I'm mixing data with ID's generated at other sites.

我想到的第一个方法是这样做：

The first way I thought around this problem was to do this:

gs short）：

gs short) :

[Location]
SiteKey [FK -> Location, FK -> User] ** ADDED THIS
AreaID [FK -> Area]
LocationID [PK]
Description
UpdatedDtm
UpdatedUserID [FK -> User]

[Sensor]
SiteKey [FK -> Location, FK -> User] ** ADDED THIS
LocationID [PK / FK -> Location]
SensorNo [PK]
UpdatedDtm
UpdatedUserID [FK -> User]

[Reading]
SiteKey [FK -> Sensor] ** ADDED THIS
LocationID [PK / FK -> Sensor]
SensorNo [PK / FK -> Sensor]
ReadingDtm [PK]

基本上，每个表都获得一个SiteKey，使每一行都独一无二

Basically, every table gets a SiteKey making each row unique to the site.

另一种方法是（在某些地方使用UUID）： -

An alternative is this (using UUIDs in some places) : -

[User]
SiteKey [FK -> Site]
UserUUID [PK]

[Area]
SiteKey [FK -> Site]
AreaUUID [PK]
Description
UpdatedDtm
UpdatedUserUUID [FK -> User]

[AreaHistory]
Site [FK -> Site]
AreaUUID [FK -> Area]
Description
UpdatedDtm
UpdatedUserUUID [FK -> User]
AuditedDtm

[Location]
AreaUUID [FK -> Area]
LocationUUID [PK]
Description
UpdatedDtm
UpdatedUserUUID [FK -> User]

[Sensor]
LocationUUID [PK / FK -> Location]
SensorNo [PK]
UpdatedDtm
UpdatedUserUUID [FK -> User]

[Reading]
LocationUUID [PK / FK -> Sensor]
SensorNo [PK / FK -> Sensor]
ReadingDtm [PK]

记住这是削减，但它说明了问题

Remember this is cut down, but it illustrates the problem.

有没有其他可能缺少的选择？我想到ID重新映射，但似乎引入了更糟糕的恶梦。

Are there any alternatives I may be missing? I thought about ID remapping but that seemed to introduce new even worse nightmares.

这个令人讨厌的部分是使用这种场景的情况很少，休息对于许多站点的一个数据库很满意。然而，客户想要这种情况是最大的; - ）

The annoying part of this is that it's a very small number of cases that use this scenario, the rest are happy with one database serving many sites. However, the client wanting this scenario is the biggest ;-)

我以为也许我可以使用UUID为他们，但这意味着创建异常，所以也可以使用它们（如果需要的话）如果我要使用它们。

I thought maybe I could use UUID just for them, but that means creating exceptions everywhere so may as well just use them throughout (where neccessary) if I'm going to use them.

如果有任何区别，我使用PostgreSQL。

If it makes any difference, I'm using PostgreSQL.

PS，此讨论可能与这种情况有关。我只是想知道是否有另一种方式考虑我有一个很好的gauranteed唯一的SiteKey。

PS, this discussion may be relevant to this case. I just keep wondering if there's another way considering I have a nice gauranteed unique SiteKey.

推荐答案

我或多或少来结论是，在这种情况下，使用UUID的IMHO有点快速修复，甚至可能是一个黑客。我决定对我来说，在这种情况下，使用复合键是更干净的。使用UUID，我也可以使用预先登录的每个ID的SiteKey。

I've more or less come to the conclusion that IMHO, for this scenario, using UUIDs is a bit of a "quick fix", maybe even a bit of a hack. I've decided that for me, in this case, using a composite key is cleaner. Using a UUID, I may as well of just used preprended the SiteKey to each ID.

这篇关于GUID / UUID这种情况？还是复合键？或其他的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！