数据库设计中哪个表为主表、哪个表为子表
我正在快速学习数据库设计的细节（一周前这对我来说还是新的），但我遇到了一些看起来并不明显的问题，所以我希望得到一些澄清。
我正确的问题是关于外键的。作为我设计的一部分，我有一个Company表。最初，我将地址信息直接包含在表中，但是，由于我希望实现 3NF，所以我将地址信息分解到它自己的表中，地址。为了保持数据完整性，我在Company中创建了一行名为“addressId”的INT，并且Address表有一个相应的addressId作为其主键。
我有点困惑（或者我想确保我做得正确）是确定哪个表应该是主（引用）表，哪个应该是子（引用）表。当我最初设置它时，我将地址表设置为主表，将公司表设置为子表。但是，我现在认为这是错误的，因为每个公司应该只有一个地址，并且如果删除公司行，我希望也删除相应的地址（级联删除）。
我的做法可能完全错误，所以我希望有任何关于在使用外键时如何最好地考虑表之间关系的良好经验规则。谢谢！
I am quickly learning the ins and outs of database design (something that, as of a week ago, was new to me), but I am running across some questions that don't seem immediately obvious, so I was hoping to get some clarification.
The question I have right is about foreign keys. As part of my design, I have a Company table. Originally, I had included address information directly within the table, but, as I was hoping to achieve 3NF, I broke out the address information into its own table, Address. In order to maintain data integrity, I created a row in Company called "addressId" as an INT and the Address table has a corresponding addressId as its primary key.
What I'm a little bit confused about (or what I want to make sure I'm doing correctly) is determining which table should be the master (referenced) table and which should be the child (referencing) table. When I originally set this up, I made the Address table the master and the Company the child. However, I now believe this is wrong due to the fact that there should be only one address per Company and, if a Company row is deleted, I would want the corresponding Address to be removed as well (CASCADE deletion).
I may be approaching this completely wrong, so I would appreciate any good rules of thumb on how to best think about the relationship between tables when using foreign keys. Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
将其视为有或有很多关系。
公司肯定有一个地址（在您的示例中），因此它应该是父表，并且地址表应该引用公司表。另一方面，如果许多不同的公司共享相同的地址，则情况可能相反。所以这也取决于您的需求（您尝试建模的逻辑）。
Think of it as a has or has many relationship.
A company definitely has an address (in your example) so it should be the parent table and the address table should reference the company table. If, on the other hand, many different companies shared the same address, it could be the other way round. So it also depends on your needs (the logic you are trying to model).
如果一家公司只有一个地址，我要么将公司信息保留在公司表中，要么在地址表中保留一个 CompanyId 列，但无论如何，这似乎没有太多用处。如果数据确实与公司相关并且没有在其他地方使用，那么将数据放在那里仍然是3NF。
如果您想要说“帐单地址”和“送货地址”，那么拥有一个与作为身份列的 AddressId 和引用公司的 CompanyId 列分开的地址表会更有意义桌子。
然而，给你一个更普遍的规则，“大师”才是数据的真正“大师”。在本例中，主记录是一家公司，因此应引用其 ID。在拥有地址之前，您需要拥有一家公司。
If a company is to have one, and only one address I would either leave the company information in the Company table, OR have a CompanyId column in the Address table, but regardless there doesn't seem to be much utility in that. If the data is truly related to the Company and not used elsewhere, it is still 3NF to have the data there.
If you wanted to have say a "billing Address" and a "Shipping Address" it would make a lot more sense to have an address table that is separate with an AddressId that is an identity column and a CompanyId column that is referenced to the Company table.
However to give you a more general rule, the "Master" is the true "master" of the data. In this case, the master record is a company, therefore its id should be referenced. You need to have a company, before you can have an address.
如果您希望每次删除公司时都删除该地址，这意味着该地址直接依赖于该公司，并且将该地址保留在公司表中并不违反
3NF。如果地址属性与公司无关，可以将其放入地址表中，使地址管理逻辑上更加一致。
比如说，您可以将地址拆分为
国家/地区/城镇/街道部分，如果公司所在国家/地区的一部分获得独立或其他什么，您只需更改国家/地区即可更改地址分离区域的字段。但是，这意味着您对实体中的地址感兴趣，而不是对属性感兴趣，并且您不应该再级联删除它们。
更新：
在范式定义中，“dependent”一词的意思是“依赖于我的模型”
比如说，公司的地址是
美国纽约州纽约市华尔街。如果在您的模型中，
华尔街取决于纽约，而纽约又取决于NY，而NY又取决于美国< /code>，然后将其保留在单个表中会违反3NF。但是，如果在您的模型中：
Wall Street, New York, CA, USA是一个有效的地址（这意味着您不会对此提出错误）地址）这绝不是一个有效的情况您仅更新公司地址，因为您正在对其他一些公司做同样的事情（这意味着处理街道重命名或合并区域或进行其他地理更新等操作不属于您的一部分正常业务规则的一部分）
，那么包含地址的表位于
3NF< /代码>。从您每次删除公司时希望删除地址的情况来看，我判断您不会跟踪地址依赖性，因此，您可以将地址保留在公司表中。
If you want to delete the address each time you delete a company, this means that the address is directly dependent on the company and keeping the address in the company table does not violate the
3NF.If the address attributes were dependent on something other than the company, you could put them into address table to make the address management more logically consistent.
Say, you could split the address into
country / region / town / streetparts, and if a part of the company's country gained independence or something, you could change the address merely by changing thecountryfield of the breakaway regions.However, this means that you are interested in addresses as in entities, not attributes, and you should not cascade delete them anymore.
Update:
In the normal forms definitions, the word "dependent" means "dependent in my model"
Say, the company's address is
Wall Street, New York, NY, USA.If in your model
Wall Streetdepends onNew Yorkwhich depends onNYwhich depends onUSA, then keeping it in a single table would violate the3NF.However, if in your model:
Wall Street, New York, CA, USAis a valid address (which means you are not going to raise an error on this address)It is never a valid situation that you update the company's address only because you are doing the same to some other companies (this means that something like handling the renaming of the streets or merging the regions or doing the other geographical updates is not a part of your normal business rules)
, then the table with the addresses is in
3NF.From your wish to delete an address each time your are deleting a company I judge that you are not going to track the address dependencies, and, hence, you can keep the address in the companies table.
你这样做不正确。地址表中应该有公司 ID，而不是公司表中的地址 ID。这是因为这种关系实际上是一对多的，一个公司，多个可能的地址（公司通常有多个地址）。这使得 Company 成为父表。
You are not doing this correctly. You should have company Id in the address table not addressid in the company table. This is because the relationship is really one-to-many, one company, more than one possible address (companies often have multiple addresses). That makes company the parent table.