什么是一个在线/离线多客户端的移动应用使用SQLite和SQL Azure的数据库作为中央存储的最佳主键的策略？ [英] What is the best primary key strategy for an online/offline multi-client mobile application with SQLite and Azure SQL database as the central store?

问题描述

什么主键的策略是最好的使用下面给出一个关系数据库模型？

What primary key strategy would be best to use for a relational database model given the following?

• 用户数万
• 在每个用户的多个客户端（手机，平板电脑，台式机）
• 在数以百万计每个表的行（不断增长）

SQL Azure的将是中央数据存储，将通过Web API暴露。该客户端将包括一个Web应用程序和一些本地应用程序包括iOS，安卓，苹果，Windows 8中，对等网络应用程序将需要一个永远在线的连接，不会有一个本地数据存储，但反而会检索和更新通过API - 想通过CRUD REST的API。

Azure SQL will be the central data store which will be exposed via Web API. The clients will include a web application and a number of native apps including iOS, Android, Mac, Windows 8, etc. The web application will require an "always on" connection and will not have a local data store but will instead retrieve and update via the api - think CRUD via RESTful API.

所有其它的客户端（电话，平板电脑，台式机）将有一个本地分贝（SQLite的）。第一次使用这种类型的客户端，用户必须进行身份验证和同步的。一旦通过验证并同步，这些客户端可以在离线模式（创建，删除和本地SQLite数据库更新记录）进行操作。这些变化将最终与Azure的后端同步。

All other clients (phone, tablet, desktop) will have a local db (SQLite). On first use of this type of client the user must authenticate and sync. Once authenticated and synced, these clients can operate in an offline mode (creating, deleting and updating records in the local SQLite db). These changes will eventually sync with the Azure backend.

数据库的分布式特性给我们留下了一个主键的问题和提出这个问题的原因。

The distributed nature of the databases leaves us with a primary key problem and the reason for asking this question.

下面是我们一直考虑迄今：

Here is what we have considered thus far:

GUID

每个客户端创建它自己的密钥。在同步，有一个非常小的机会，重复键，但我们也需要通过编写功能集成到每一个客户端更新使用新的密钥的所有关系来解释它。 GUID是很大的，当每个表的多个外键被认为是，存储可能随时间而成为一个问题。可能最大的问题是的GUID的随机性，这意味着他们不能（或者不应该）被用作聚集索引因碎片。这意味着我们需要创建一个聚集索引（也许是任意的）为每个表。

Each client creates it’s own keys. On sync, there is a very small chance for a duplicate key but we would need to account for it by writing functionality into each client to update all relationships with a new key. GUIDs are big and when multiple foreign keys per table are considered, storage may become an issue over time. Likely the biggest problem is the random nature of GUIDs which means that they can not (or should not) be used as the clustered index due to fragmentation. This means we would need to create a clustered index (perhaps arbitrary) for each table.

身份

每个客户端创建它自己的主键。在同步时，这些键被替换生成服务器密钥。这增加了额外的复杂性的同步过程，并强制每个客户端修理他们的钥匙，包括对相关表的所有外键。

Each client creates it’s own primary keys. On sync, these keys are replaced with server generated keys. This adds additional complexity to the syncing process and forces each client to "fix" their keys including all foreign keys on related tables.

综合

每个客户端分配上首次同步客户端ID。此客户端ID被用于与一个本地自动递增id作为一个复合主键为每个表。该组合键将是唯一的，所以应该在同步没有冲突，但它确实意味着大多数表将需要一个复合主键。性能和查询的复杂性是关注这里。

Each client is assigned a client id on first sync. This client id is used in conjunction with a local auto-incrementing id as a composite primary key for each table. This composite key will be unique so there should be no conflicts on sync but it does mean that most tables will require a composite primary key. Performance and query complexity is the concern here.

高住低训（合并复合）

像组合方式，每个客户端分配一个客户端ID（INT32）在第一个同步客户端ID被合并到一个唯一的本地ID（INT32）成一列，以使应用程序范围内惟一的ID（Int64的）。这将导致在同步过程中没有发生冲突。虽然有更多的为了这些键VS GUID的，因为每个客户端生成的ID是连续的，将会有数以万计的客户的ID，所以我们仍然在我们的聚集索引碎片运行的风险？

Like the composite approach, each client is assigned a client id (int32) on the first sync The client id is merged with a unique local id (int32) into a single column to make an application wide unique id (int64). This should result in no conflicts during sync. While there is more order to these keys vs GUIDs since the ids generated by each client are sequential, there will be thousands of unique client-ids, so do we still run the risk of fragmentation on our clustered index?

难道我们忽视的东西？是否有任何其他的方法值得研究？每种方法的利弊的讨论将是非常有帮助的。

Are we overlooking something? Are there any other approaches worth investigating? A discussion of the pros and cons of each approach would be quite helpful.

推荐答案

键（双关语意）一点要记住的是，只是有一个唯一的密钥对每个对象要存储在持久性存储。你如何处理这个对象的存储是完全取决于你，最多你如何访问该键的方法。每次列出的策略有自己的理由，为什么他们做他们做什么，但最终他们都存储这样它的所有属性可以同时保留在数据库中的同一个对象的引用来改变在数据库的某个对象的关键。

The key (pun intended) thing to remember is to simply have a unique key for each object you are storing on the persistent store. How you handle the storage of that object is completely up to you and up to the methodology of how you access that key. Each of the strategies you listed have their own reasons for why they do what they do but in the end they are storing a key for a certain object in the db so all of its attributes can be changed while retaining the same object reference in the database.

这篇关于什么是一个在线/离线多客户端的移动应用使用SQLite和SQL Azure的数据库作为中央存储的最佳主键的策略？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！