多租户主键的最佳方法 [英] Best approach for multi-tenant primary keys

问题描述

我有一个供多个客户端使用的数据库.我真的不希望代理增量键值在客户端之间流血.我希望编号从1开始，并且要针对客户.

I have a database used by several clients. I don't really want surrogate incremental key values to bleed between clients. I want the numbering to start from 1 and be client specific.

我将使用由两部分组成的tenant_id组合键以及增量ID.

I'll use a two-part composite key of the tenant_id as well as an incremental id.

为每个租户创建增量密钥的最佳方法是什么?

What is the best way to create an incremental key per tenant?

我正在使用SQL Server Azure.我担心锁定表，重复键等.通常，我会将主键设置为IDENTITY并继续.

I am using SQL Server Azure. I'm concerned about locking tables, duplicate keys, etc. I'd typically set the primary key to IDENTITY and move on.

谢谢

推荐答案

您是否打算将来使用SQL Azure联合身份验证?如果是这样，则当前版本的SQL Azure联合身份不支持将IDENTITY用作聚集索引的一部分.参见其他选择可以使用guid作为SQL Azure(联合会)中表的聚集索引.

Are you planning on using SQL Azure Federations in the future? If so, the current version of SQL Azure Federations does not support the use of IDENTITY as part of a clustered index. See this What alternatives exist to using guid as clustered index on tables in SQL Azure (Federations) for more details.

如果您尚未查看联盟，则可能需要将其检出，因为它提供了一种有趣的方式来分摊数据库以及在数据库内隔离租户.

If you haven't looked at Federations yet, you might want to check it out as it provides an interesting way to both shard the database and for tenant isolation within the database.

根据最终目标，使用联合身份验证，您可能可以将GUID用作表上的主要聚集索引，也可以在表上使用递增的INT IDENTITY字段.可以向最终用户显示此INT IDENTITY字段.如果您要联合使用TenantID，则每个"Tenant表"实际上都会变成一个孤岛(至少据我所知)，因此在该表中的字段上使用IDENTITY实际上将是不断增加的自动生成的值，该值在给定的Tenant内递增

Depending upon your end goal, using Federations you might be able to use a GUID as the primary clustered index on the table and also use an incremental INT IDENTITY field on the table. This INT IDENTITY field could be shown to end-users. If you are federating on the TenantID each "Tenant table" effectively becomes a silo (as I understand it at least) so the use of IDENTITY on a field within that table would effectively be an ever increasing auto generated value which increments within a given Tenant.

当\如果将数据合并在一起(合并来自多个租户的数据)，您将在此INT IDENTITY字段上发生冲突(因此，只要不支持IDENTITY作为联合身份验证的主键，为什么?)将该字段用作整个系统中的唯一标识符，就可以了.

When \ if data is merged together (combining data from multiple Tenants) you would wind up with collisions on this INT IDENTITY field (hence why IDENTITY isn't supported as a primary key in federations) but as long as you aren't using this field as a unique identifier within the system at large you should be ok.

这篇关于多租户主键的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！