Sql - 间接外键 [英] Sql - Indirect Foreign Key
问题描述
- 有没有这个名字？
- 这是个好习惯吗？
- 任何性能注意事项？
我有一个通用表结构用于存储关系。
最近我重构了一些东西来使用这个通用结构，而不是直接的Fk列，但现在我不知道这是否真的是最好的主意。
原始模式：
+ ------------- + + --------------------- + + ------------ ---------- +
|书| |注意| | MetaParent |
| ------------------ | | --------------------- | | ---------------------- |
| Id | | Id | | Id |
| NoteId | | MetaParentId：（Null）| | MetaTableId |
| + ------- + + ---- + KeyValue |
| | | | | |
| | | | | |
| | | | | |
| | | | | |
| | | | | |
+ ------------------ + + --------------------- + + - -------------------- +
新模式
+ ------------------ + + ------------ --------- + + ---------------------- +
|书| |注意| | MetaParent |
| ------------------ | | --------------------- | | ---------------------- |
| Id | | Id | | Id |
| | | MetaParentId：（Null）| | MetaTableId |
| + + + ---- + KeyValue |
| | | | | |
| | | | | |
| | | | | |
| | | | | |
| | | | | |
+ ------------------ + + --------------------- + + - -------------------- +
所以基本上而不是在Book之间有直接的Fk关系注意我们通过MetaParent表使用MetaTableId / KeyValue列进行间接关系。
目前，MetaParent表有大约500k条记录，事情可以接受。但是我们每天晚上都会重建索引。
我担心的是，Book和Note之间的关系并不明显。你必须知道一个存在并使用MetaParent表。
还有性能，我不知道在什么时候我们遇到与MetaTableId / KeyValue的连接运行太慢的问题。
您应该始终通过使用normal来强制引用完整性FOREIGN KEY。
简而言之，FOREIGN KEYs具有以下优点：
- 他们是声明式，自我记录和显而易见。
-
- 他们是正确的。
- 他们很快。
- 他们支持级联参考动作（例如ON DELETE CASCADE）。
- DBMS知道数据是相关的，允许它找到更好的查询计划在某些情况下。
- 如果您使用ORM工具，它可以自动生成对象之间的引用。
<他们已经在DBMS中实现了。
这里是在应用程序代码中执行引用完整性的相应缺点
- 您正在复制已经完成的作品。
- 这是必须的，可能埋在您的应用程序源代码深处，并且难以维护。
- 具有错误的单个客户端应用程序可能会破坏引用完整性（并损坏数据）。
- 您可能在应用程序代码中不正确地执行它们。它从一开始看起来很简单，但在并发环境中，很容易引入竞争条件。
- 即使您已经正确实现了它们，您可能会使用某种形式的锁定来避免竞争条件，这可能比DBMS内置的特殊优化的FK更慢/更小的可扩展性
- 你必须自己实现级联。
- DBMS不知道数据是相关的，这可能会产生次优的查询计划。
- 您可能需要在您选择的ORM工具中进行更多的手动工作。
有没有这个名字？
不是我了解。我听说过一个术语通用FK被使用，但这可能不是普遍的。
$ b
否（见上文）
任何性能注意事项？ / p>
是（见上文）。
I have some questions about database design.
- Is there a name for this?
- Is it good practice?
- 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:
- They are already implemented within the DBMS.
- They are declarative, self-documenting and "obvious".
- They cannot be bypassed (unless explicitly disabled or dropped).
- They are correct.
- They are fast.
- They support cascading referential actions (such as ON DELETE CASCADE).
- The DBMS knows the data is related, allowing it to find a better query plan in some cases .
- 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:
- You are duplicating the work that has already been done.
- It's imperative, probably "buried" deep in your application source code, and harder to maintain.
- A single client application that has a bug can break the referential integrity (and corrupt the data).
- 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.
- 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.
- You have to implement cascading yourself.
- The DBMS doesn't know the data is related, which may produce sub-optimal query plan.
- 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屋！
