涉及多个表的一对一关系 [英] One to one relationship involving multiple tables

问题描述

说

我有3张桌子.

User包含有关用户的基本信息.

User which contains basic info about the users.

SectionA包含有关用户的更多信息.

SectionA which contains more info about the user.

SectionB还包含有关用户的更多信息.

SectionB which also contains more info about the user.

每个用户只能有一个SectionA和SectionB数据.

There can only be one SectionA and SectionB data for each user.

我的想法是创建一个像这样的表设计:

My idea was to create a table design like this:

id  name   section_a_id   section_b_id
1   matt   1               1

问题是，section_a_id和section_b_id不能自动递增，因为它们不是主键.

Problem is, section_a_id and section_b_id cannot be auto incremented since they are not primary keys.

因此，我尝试了另一种方法，并决定User中的id主键应该是引用section_a_id和section_b_id`的外键.但是我无法这样做，因为mysql只允许引用一个表.

So I tried a different approach and decided that the id primary key in User should be a foreign key that refers to section_a_id and section_b_id`. But I'm unable to do so since mysql will only allow a reference to one table.

那我应该如何处理这种情况?

So how should I approach this situation?

推荐答案

如果是一对一的关系，将三个表合并为一个大表(带有适用于Section表的可空列)总是比较容易的.

If it's one to one relation, it will always be easier to combine the three tables into one big table, with nullable columns for Section tables.

对于这种方法，我可以看到一些积极的观点:

Some positive points I can see for this approach:

• 更容易进行插入，更新和删除操作.
• 使用较少的联接时可以更快地检索数据.
• 减少索引空间，因为您正在索引一个表而不是三个表的主键.

这篇关于涉及多个表的一对一关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！