Sql - 间接外键 [英] Sql - Indirect Foreign Key

问题描述

我有一些关于数据库设计的问题.

1. 有这个名字吗?
2. 这是好的做法吗?
3. 是否有任何性能方面的考虑?

我有一个用于存储关系的通用表结构.

最近我重构了一些东西以使用这种通用结构而不是直接的 Fk 列，但现在我不确定这是否真的是最好的主意.

原始架构:

<前>+------------------+ +---------------------+ +----------------------+|预订 ||注意 ||元母 ||-------------------||---------------------||----------------------||身份证 ||身份证 ||身份证 ||NoteId ||MetaParentId:(空) ||元表 ID ||+-------+ +----+ KeyValue |||||||||||||||||||||||||||||||+------------------+ +---------------------+ +----------------------+

新架构

<前>+------------------+ +---------------------+ +----------------------+|预订 ||注意 ||元母 ||-------------------||---------------------||----------------------||身份证 ||身份证 ||身份证 ||||MetaParentId:(空) ||元表 ID ||+ + +----+ 键值 |||||||||||||||||||||||||||||||+------------------+ +---------------------+ +----------------------+

所以基本上，Book 和 Note 之间没有直接的 Fk 关系，而是通过使用 MetaTableId/KeyValue 列的 MetaParent 表建立间接关系.

目前 MetaParent 表有大约 50 万条记录，并且运行正常.但是我们每晚都会在其上重建索引.

我担心的是现在 Book 和 Note 之间的关系并不明显.您必须知道存在一个并使用 MetaParent 表.

还有性能，我不确定在什么时候我们会遇到针对 MetaTableId/KeyValue 的联接运行太慢的问题.似乎您向该表中添加的内容越多，查询速度就会越慢.

解决方案

您应该始终使用正常"外键来强制执行参照完整性.

简而言之，FOREIGN KEYs 有以下优点:

1. 它们已在 DBMS 中实现.
2. 它们是声明性、自我记录和显而易见的".
3. 无法绕过它们(除非明确禁用或删除).
4. 他们是对的.
5. 他们很快.
6. 它们支持级联引用操作(例如 ON DELETE CASCADE).
7. DBMS 知道数据是相关的，因此可以在某些情况中找到更好的查询计划.
8. 如果您使用的是 ORM 工具，它可以自动在对象之间生成引用.

以下是在应用程序代码中实施参照完整性的相应缺点:

1. 您正在复制已经完成的工作.
2. 它势在必行，可能深埋"在您的应用程序源代码中，而且更难维护.
3. 存在错误的单个客户端应用程序可能会破坏参照完整性(并损坏数据).
4. 您可能会在应用程序代码中错误地实现它们.从一开始看起来很简单，但在并发环境中，很容易引入竞争条件.
5. 即使您正确实施了它们，您也可能使用了某种形式的锁定来避免竞争条件，这可能比 DBMS 中内置的专门优化的 FK 更慢/可扩展性更差.
6. 您必须自己实现级联.
7. DBMS 不知道数据是相关的，这可能会产生次优的查询计划.
8. 您可能需要在您选择的 ORM 工具中执行更多手动工作.

<小时><块引用>

有这个名字吗?

我不知道.我听说有人使用了通用 FK"一词，但这可能并不普遍.

<块引用>

这是好的做法吗?

否(见上文).

<块引用>

有任何性能方面的考虑吗?

是(见上文).

I have some questions about database design.

1. Is there a name for this?
2. Is it good practice?
3. Any performance considerations?

I have a generic table structure used to store relationships.

Recently I refactored some things to use this generic structure instead of direct Fk columns, but now I'm not sure if that was really the best idea.

Original Schema:

 +------------------+       +---------------------+    +----------------------+
 | Book             |       | Note                |    | MetaParent           |
 |------------------|       |---------------------|    |----------------------|
 | Id               |       | Id                  |    | Id                   |
 | NoteId           |       | MetaParentId:(Null) |    | MetaTableId          |
 |                  +-------+                     +----+ KeyValue             |
 |                  |       |                     |    |                      |
 |                  |       |                     |    |                      |
 |                  |       |                     |    |                      |
 |                  |       |                     |    |                      |
 |                  |       |                     |    |                      |
 +------------------+       +---------------------+    +----------------------+

New Schema

 +------------------+       +---------------------+    +----------------------+
 | Book             |       | Note                |    | MetaParent           |
 |------------------|       |---------------------|    |----------------------|
 | Id               |       | Id                  |    | Id                   |
 |                  |       | MetaParentId:(Null) |    | MetaTableId          |
 |                  +       +                     +----+ KeyValue             |
 |                  |       |                     |    |                      |
 |                  |       |                     |    |                      |
 |                  |       |                     |    |                      |
 |                  |       |                     |    |                      |
 |                  |       |                     |    |                      |
 +------------------+       +---------------------+    +----------------------+

So basically instead of having a direct Fk relationship between Book and Note we have an indirect relationship via the MetaParent table using the MetaTableId/KeyValue columns.

Currently the MetaParent table has about 500k records and things are running acceptably. But we do rebuild the indexes on it every night.

My concerns are that now the relationship between Book and Note isn't obvious. You have to know one exists and to use the MetaParent table.

Also performance, I'm not sure at what point we'd run into issues with joins against MetaTableId/KeyValue running too slow. It seems the more you add to this table the slower queries would get.

解决方案

You should always enforce referential integrity by using "normal" FOREIGN KEYs.

In a nutshell, FOREIGN KEYs have the following advantages:

1. They are already implemented within the DBMS.
2. They are declarative, self-documenting and "obvious".
3. They cannot be bypassed (unless explicitly disabled or dropped).
4. They are correct.
5. They are fast.
6. They support cascading referential actions (such as ON DELETE CASCADE).
7. The DBMS knows the data is related, allowing it to find a better query plan in some cases .
8. If you are using an ORM tool, it can automatically generate references between objects.

And here are the corresponding disadvantages of enforcing referential integrity in the application code:

1. You are duplicating the work that has already been done.
2. It's imperative, probably "buried" deep in your application source code, and harder to maintain.
3. A single client application that has a bug can break the referential integrity (and corrupt the data).
4. You are likely to implement them incorrectly in your application code. It looks simple from the outset, but in a concurrent environment, it is easy to introduce race conditions.
5. Even if you have implemented them correctly, you probably used some form of locking to avoid race conditions, which is likely to be slower / less scalable than specially optimized FKs built into the DBMS.
6. You have to implement cascading yourself.
7. The DBMS doesn't know the data is related, which may produce sub-optimal query plan.
8. You may need to do more manual work in your ORM tool of choice.

---

Is there a name for this?

Not that I know of. I heard a term "generic FKs" being used, but that's probably not universal.

Is it good practice?

No (see above).

Any performance considerations?

Yes (see above).

这篇关于Sql - 间接外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！