同步桌面应用程序的数据库设计

发布于 2024-08-07 18:51:37 字数 338 浏览 6 评论 0原文

我正在构建一个可以在多台笔记本电脑上运行的桌面应用程序。每当用户回到办公室并再次访问时,它都需要同步到中央数据库。

我要克服的最大问题是如何设计数据库以便它可以轻松地与中央数据库服务器同步。主要障碍之一是试图确定如何处理密钥,以便它们不会在将使用的多个笔记本电脑数据库中重复。

例如,假设笔记本电脑 1 输入一个名为“客户 A”的新客户 - 使用唯一 ID,可能会为其分配客户 ID 20。笔记本电脑 2 输入“客户 C” - 它还可以为该客户分配 ID 20 。当需要同步时,客户 A 和客户 A 都需要进行同步。 C 最终会在服务器上具有重复的 ID。

有没有人使用过类似的应用程序,并且有一个优雅的解决方案?

I'm building a desktop application that will run on multiple laptops. It will need to sync up to a central database whenever the user is back in the office and has access again.

My biggest problem to overcome is how to design the database so that it is easily synced with the central database server. One of the major hurdles is trying to determine how to handle keys such that they don't duplicate across the multiple laptop databases that'll be in use.

For example, say Laptop 1 enters a new customer called "Customer A" - using a unique id, it might be assigned a customer ID of 20. Laptop 2 enters "Customer C" - it could also assign the ID of 20 to that customer. When it comes time to sync, both customer A & C would end up on the server with a duplicate ID.

Has anyone worked with an app similar to this that has an elegant solution?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

め可乐爱微笑 2024-08-14 18:51:37

替代方案是:

  • ID 范围,这些方案很难实施和强制执行。它们非常难以管理,很难提供新的站点/范围,并且更难以应对退役的站点/范围。
  • 复合键,(siteId,EntityId)比范围更好,但需要预先设计,并且可能会导致聚合多个/所有站点(中央DW)的查询出现问题,因为所有索引中最左边的键(siteId)是未指定。
  • GUID。理论上它们是完美的,因为它们保证了唯一性(它们在理论上可能会发生冲突,我还没有看到真正的指导冲突)。实际上,由于大小(16 字节)和碎片,它们是非常糟糕的聚集键选择。尽管如此,它们比复合键方法更容易正确。

Alternatives are:

  • ranges of ids, which are difficult to implement and to enforce. They are very hard to manage, is difficult to provision an new site/range and is even more difficult to cope with retired sites/ranges.
  • composite keys, (siteId, EntityId) are better than ranges but require upfront design and may cause problems on queries that aggregate multiple/all sites (central DWs) because the leftmost key in all indexes (siteId) is not specified.
  • GUIDs. In theory they are perfect as they guarantee uniqueness (they may conflict in theory, I'm yet to see a true guid conflict). In practice they are very poor clustered key choices because of the size (16 bytes) and because of the fragmentation. Still, they are much easier to get right than the composite key approach.
你丑哭了我 2024-08-14 18:51:37

使用Guids(又名uniqueidentifier)作为主键,所有复制问题都将消失。自动增量int PK 的支持者几乎总是大大夸大了使用 Guid 的惩罚。它们需要的额外大小(16 个字节与 int 的 4 个字节)是如此微不足道,以至于我很惊讶它竟然出现在这个主题的讨论中。在 Guid PK 上 JOIN 的查询运行速度会比在 int PK 上 JOIN 的查询运行得慢,但这不是一个速度慢一个数量级(即 10 倍)。您的结果可能会有所不同,但我发现 Guid JOIN 查询的执行时间大约要长 40%(在您记住摩尔定律之前,这似乎是一个很大的损失)。

使用 Guid 进行 PK 还可以让您在插入相关数据时不必做一些非常棘手的事情。您不必插入子记录,然后在插入父行之前检索刚刚插入的行的 ID,您只需使用 Guid.NewGuid() 在客户端创建所有 ID。

我之前曾使用过复制系统,使用 int PK 的指定范围来解决此问题,但我永远不会再这样做。

Use Guids (aka uniqueidentifier) for your primary keys, and all of your problems with replication will go away. The penalties for using Guids are almost always drastically overstated by proponents of auto-increment int PKs. The extra size they require (16 bytes vs. 4 bytes for an int) is so trivial that I'm amazed it ever comes up in discussions on this subject. Queries that JOIN on Guid PKs will run slower than queries that JOIN on int PKs, but it is not an order-of-magnitude (i.e. 10X) slower. Your results may vary, but I've found that Guid JOIN queries take around 40% longer to execute (which may seem like a large penalty until you remember Moore's law).

Using Guids for PKs also gets you out of doing really hacky stuff when you're inserting related data. Instead of having to insert the child records and then retrieve the IDs for the just-inserted rows before inserting the parent rows, you just create all the IDs client-side with Guid.NewGuid().

I've worked with replicated systems before that used assigned ranges with int PKs to solve this problem, but I will never ever do that again.

做个ˇ局外人 2024-08-14 18:51:37

我不知道这是一个“优雅”的解决方案来解决这个非常不优雅的问题。 Remus 有很好的想法,也建议你阅读一些有关复制的内容。

您还最好设计一个重复数据删除流程,因为可以肯定的是,代表 A 将在客户 A 中进行更新,而代表 B 也将在客户 A 中进行更新,并且由于它们来自具有不同主键的不同来源,因此它们将是不同的记录。

I don't know that the is an "elegant" solution to this very inelegant problem. Remus has good thoughts, also suggest you do some reading on replication.

You had also better design a de-dup process because sure as anything, rep A is going to upt in customer A and rep b is going to put in customer A as well and because they came from differnt sources with differnt primary keys, they will be different records.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文