为什么在SQL中有1：m关系的连接表 [英] Why to have a join table for 1:m relation in SQL

问题描述

在以下数据库中的第一个1：m和第二个1：m关系之间具有联结表的好处是什么？

alt text http://dl.getdropbox.com/u/175564 /db/db-simple.png

这本书 Joe Celko在SQL for Smarties中的树和层次结构说，原因是在1：m中有独特的关系。例如，以下表格分别限制用户两次提出完全相同的问题，并分别给出完全相同的答案两次。

The book Joe Celko's trees and hierarchies in SQL for Smarties says that the reason is to have unique relations in 1:m's. For instance, the following tables resrict users to ask the exactly same question twice and to give exactly the same answer twice, respectively.

第一个1：m关系

users-questions
===============
user_id REFERENCES users( user_id )
question_id REFERENCES questions ( question_id )
PK( user_id, question_id)           // User is not allowed to ask same question twice 

第二个1：m关系

questions-answers
=================
question_id REFERENCES questions( question_id)
answer_id REFERENCES answers( aswer_id )
PK( question_id, answer_id )       //  Question is not allowed to have to same answers

这个关于唯一性的好处不能说服我让我的代码更具挑战性。
我不明白为什么我应该限制在db中使用相同ID的问题或答案的可能性，因为我也许可以使用PHP禁止。

This benefit about uniqueness does not convince me to make my code more challenging. I cannot understand why I should restrict the possibility of having questions or answers with the same ID in the db, since I can perhaps use PHP to forbid that.

推荐答案

它通常由于数据的重复。

Its usually due to duplication of data.

至于你的推理，是的，你可以在业务层，如果你犯了一个错误，它可能会打破大量的代码。你的问题是你的数据模型可能只有几个表。幸运的你。当你的数据模型增长，如果你不能理解的结构，你必须把所有的逻辑来维护非规范化的表在你的GUI层，你可以很容易遇到问题。请注意，很难在SQL数据库的GUI上使用线程安全，而不使用会破坏性能的锁定。

As for your reasoning, yes you can enforce this in the business layer, but if you make a mistake, it could break a significant amount of code. The issue you have is your data model may have only a few tables. Lucky you. When your data model grows, if you can't make sense of the structure and you have to put all the logic to maintain denormalised tables in your GUI layer you could very easily run into problems. Note that it is hard to make things threadsafe on a GUI for your SQL Database without using locking which will destroy your performance.

DBMS在处理这些问题时非常好。您可以保持数据模型清洁，并使用索引为您提供所需的速度。您的目标应该是首先获得它，并且只有当您可以看到明确需要这样做（表现等）时，才可以对表进行反规范化。

DBMS are very very good at dealing with these problems. You can keep your data model clean and use indexing to provide you with the speed you need. Your goal should be to get it right first, and only denormalise your tables when you can see a clear need to do so (for performance etc.)

相信或不相信，有很多情况下，已规范化的数据使您的生活更轻松，而不是更难，当它涉及到您的应用程序。例如，如果你有一个大表有问题和答案，你必须编写代码，以检查它是否是唯一的。如果你有一个主键的表，你只需写入

Believe it or not, there are many situations where having normalised data makes your life easier, not harder when it comes to your application. For instance, if you have one big table with questions and answers, you have to write code to check if it is unique. If you have a table with a primary key, you simply write

insert into table (col1, col2) values (@id, @value) --NOTE: You would probably 
--make the id column an autonumber so you dont have to worry about this

如果你有一个非唯一的值，或者如果你放置在没有问题的答案，数据库将阻止你插入。所有你需要做的是检查插入是否工作，没有更多。你认为哪一个代码更少？

The database will prevent you from inserting if you have a non unique value there OR if you are placing in an answer with no question. All you need to do is check whether the insertion worked, nothing more. Which one do you think is less code?

这篇关于为什么在SQL中有1：m关系的连接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！