如何建立两个表之间的多对一关系 [英] How to establish a many to one relationship between 2 tables for a field

问题描述

我正在建立一个项目的数据库模式，我有点卡住在这个问题上。我有2个表：

USER表：

  ID名称联系人ID 
 1 Arun 2 
 2 Barath 3 
 3 Charan 2 
 4 Dinesh 1 
  

联系表：

  ID姓名电话邮件
 1 Mahesh 1234 [e-mail] 
 2 Suresh 54321 [e-mail] 
 3 Jayesh 9876 [e-mail] 
 4 Ganesh 98754 [e-mail] 
  

USER中的每个用户都将在CONTACT中有一个联系人。
如果用户有一个联系人，那么我可以在USER中的Contact_ID上使用一个外键关系，并建立它们之间的关系。

但是如果用户在USER表中在CONTACT表中有多个联系人？我不知道如何建立它们之间的关系。

例如：
在用户Charan中有一个联系人，联系人2，但是如果有的话还有一个，联系4？
在这种情况下，我该如何建立多对一的关系？

解决方案

看起来你已经掌握了向后。您的CONTACT表应该有一个对USER表的外键引用，而不是包含对CONTACT的外键引用的USER。例如：

USER表

 编号名称
 1 Arun 
 2 Barath 
 3 Charan 
 4 Dinesh 
  

联系表

  ID姓名电话邮件用户名
 1 Mahesh 1234 [e-mail] 1 
 2 Suresh 54321 [e-mail] 2 
 3 Jayesh 9876 [e-mail] 1 
 4 Ganesh 98754 [e-mail] 3 
  

当然，我只是使用假的数据为新的USER_ID列。正如你所看到的，Id 1（Arun）的用户在CONTACT表中有多个联系人（ID 1和3）。

I am building an database schema for a project and I am bit stuck on this issue. I have 2 tables:

USER table:

Id  Name    Contact_ID
1   Arun    2
2   Barath  3
3   Charan  2
4   Dinesh  1

CONTACT table:

ID  Name    Phone   Mail
1   Mahesh  1234    [e-mail]
2   Suresh  54321   [e-mail]
3   Jayesh  9876    [e-mail]
4   Ganesh  98754   [e-mail]

Each of the users in USER will have a contact in CONTACT. If a user has a single contact then I can use a foreign key relationship on Contact_ID in USER and build the relationship between them.

But what if an user in the USER table has multiple contacts in the CONTACT table? I am not sure how to build the relationship between them.

Eg: In USER for user Charan there is one contact, contact 2, but what if there is one more, contact 4? In that case how can I build the many to one relationship?

解决方案

It looks like you've got it backwards. Your CONTACT table should have a foreign key reference to USER table, rather than USER containing a foreign key reference to CONTACT. For example:

USER table

Id  Name 
1   Arun  
2   Barath 
3   Charan 
4   Dinesh 

CONTACT table

ID  Name    Phone   Mail                USER_ID
1   Mahesh  1234    [e-mail]    1
2   Suresh  54321   [e-mail]    2
3   Jayesh  9876    [e-mail]     1
4   Ganesh  98754   [e-mail]   3

Of course, I just used fake data for the new USER_ID column. As you can see, USER with Id 1 (Arun) has multiple contacts (IDs 1 and 3) in the CONTACT table.

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