在数据库表中实现唯一性(Enforcing Uniqueness Across Database Tables)

在数据库表中实现唯一性(Enforcing Uniqueness Across Database Tables)

我正在为具有以下要求的现有系统开发多租户数据库：

租户拥有一对多实体。 实体可能只属于一个租户。 实体具有一个全局唯一标识符。 实体具有一对多唯一别名。 实体具有其他非唯一属性。 对于任何给定的租户，别名可能只存在一次。

为了帮助说明，以下实体都是有效的：

|-------------------------------------| | Tenant | Entity ID | Entity Alias | |-------------------------------------| | tenant-A | entity-1 | entity-a | | tenant-A | entity-1 | entity-b | | tenant-A | entity-1 | entity-c | | tenant-B | entity-2 | entity-a | |-------------------------------------|

选项1

如上所述，这违反了“别名对于任何给定的租户可能只存在一次”。

选项2

虽然感觉有点沉重，但这种方法可以工作，拖动租户和已经唯一的实体ID。

选项3

这种方法可行，但不强制要求“实体可能只属于一个租户”。 应用程序代码可以在必要时防止这种情况发生，但理想情况下，我甚至根本不在数据库中允许这种可能性。 例如，此方法允许以下无效实体，其中单个实体在其他租户中具有别名：

|-------------------------------------| | Tenant | Entity ID | Entity Alias | |-------------------------------------| | tenant-A | entity-1 | entity-a | | tenant-B | entity-1 | entity-b | |-------------------------------------|

选项4

这种方法将允许看似合适的“选项1”设计，但使用Oracle物化视图有效地连接表并允许在它们之间应用唯一性约束。 这种方法似乎有效，虽然我对物化视图没有任何过去的经验，所以我可能需要做一些进一步的测试，以确保我不会忽视任何其他问题。 已知的缺点是这是特定于Oracle的，物化视图确实有一些固有的缺点，例如需要额外的磁盘空间，并且可能会对插入/更新的性能产生一些影响，因为视图也需要更新。

选项5

选项5将使用“选项1”或“选项3”以及一些触发器来强制执行缺失的要求。 如果可能的话，我宁愿不使用触发器，因为它们也可能有点沉重，可能是特定于DBMS的，并且不太可能是管理需求的最有效方法。

概要

从表面上看，这似乎不应该是一个独特或难以解决的问题，但没有一个选项感觉非常合适。 在这一点上，我倾向于选项2或者选项3 ，但我还不相信。 有一个简单的选择，或者我可以忽略的解决方案吗？

I'm developing a multi-tenant database for an existing system that has the following requirements:

A tenant has one-to-many entities. An entity may belong to only one tenant. An entity has one globally unique identifier. An entity has one-to-many unique aliases. An entity has other non-unique properties. An alias may only exist once for any given tenant.

To help illustrate, the following entities are all valid:

|-------------------------------------| | Tenant | Entity ID | Entity Alias | |-------------------------------------| | tenant-A | entity-1 | entity-a | | tenant-A | entity-1 | entity-b | | tenant-A | entity-1 | entity-c | | tenant-B | entity-2 | entity-a | |-------------------------------------|

Option 1

As mentioned above, this violates "An alias may only exist once for any given tenant."

Option 2

This approach works, although it feels a bit heavy, dragging the tenant along with the already-unique entity id.

Option 3

This approach may be feasible but does not enforce the requirement that "An entity may belong to only one tenant." The application code can prevent this occurrence if necessary, but ideally, I'd rather not even allow for this possibility in the database at all. For example, this approach allows the following invalid entities, where a single entity has aliases in other tenants:

|-------------------------------------| | Tenant | Entity ID | Entity Alias | |-------------------------------------| | tenant-A | entity-1 | entity-a | | tenant-B | entity-1 | entity-b | |-------------------------------------|

Option 4

This approach will allow for the seemingly appropriate design of "Option 1", but uses Oracle materialized views to effectively join the tables and allow uniqueness constraints to be applied between them. This approach seems to work, although I don't have any past experience with materialized views, so I may need to do some further testing to ensure that I'm not overlooking any other problems. The known downsides are that this is Oracle-specific and materialized views do have some inherent downsides such as requiring additional disk space and presumably having some effect on the performance of inserts/updates since the view would also need to be updated.

Option 5

Option 5 would be to use either "Option 1" or "Option 3" along with some triggers to enforce the missing requirements. I'd prefer not to use triggers if possible as these can also be a bit heavy, may be DBMS-specific, and are unlikely to be the most efficient way to manage the requirements.

Summary

On the surface, this doesn't seem like it should be a unique or difficult problem to solve, yet none of the options feels quite appropriate. At this point, I'm leaning toward Option 2 or maybe Option 3, but I'm not convinced yet. Is there a straightforward option or perhaps a solution that I'm overlooking?

最满意答案

我们最终推进了选项2 ，以便TENANT_ID包含在ENTITY和ENTITY_ALIAS 。 这似乎是最安全的方法，满足数据模型的所有要求，并且几乎没有缺点。 此外，这种方法提供了最佳基础，如果合适的话，我们可以在此基础上轻松迁移到任何其他方法。

We ended up moving forward with Option 2, such that the TENANT_ID was included in both the ENTITY and ENTITY_ALIAS. This seemed to be the safest approach, meeting all requirements of the data model, and having few drawbacks. Further, this approach provided the best foundation from which we can likely easily migrate to any of the other approaches in the future if appropriate.