SQL中的多对多关系？(Many

SQL中的多对多关系？(Many-to-Many-to-Many Relation in SQL?)

我是SQL的新手，我正在尝试建立一些关系并遇到以下问题。

我有3个表 - 人 - 电话号码 - PhoneNumberCategories

我认为人和电话号码是不言自明的，而我用来表示电话号码是用于办公室，家庭，移动电话，传真机等的电话号码。

现在我制作了第4张桌子，以建立这种多对多的关系。 该表有4列：

ID 是PersonID PhoneNumberID PhoneNumberCategoryID

我在这里建立了3个关系。 除ID列之外的所有列都有一个。 它似乎有效，但它只是给我一种奇怪的感觉。 有人可以告诉我，如果我是在怀特赛道上，或者我是否对此错了。

我想要的原因如下。 当然，我需要将一个人与电话号码联系起来。 但是，我可能有一个电话号码是一般办公室（这将是行'OfficeGeneral'）号码，因此我链接到我，因为我在那里工作。 我也有一个直接的办公室（这将是'OfficeDirect'行）号码。 这当然也与我有关。 然而，一般数字与办公室中的所有人都是“OfficeGeneral”。 除了接待员，这里它将被链接为'OfficeDirect'。 这就是我提出这种多对多关系的原因。 我在网上找不到太多关于它的内容。 这就足以怀疑这是否是正确的方法。 无论如何，这只是一个例子。 我想确保我很灵活，并且可以捕获尽可能多的例外情况。 我相信一旦数据库正在使用，人们就会遇到我没有预料到的情况。 人们擅长多年来我学到的东西。

对以下评论的回应澄清：

人可以有超过1个PhoneNumber。 PhoneNumber可以有超过1个人。 具有PhoneNumber的人可以拥有多于1个PhoneNumberCategory（即用于电话和传真的私人电话。） 可以将几个人链接到具有不同PhoneNumberCategories的相同PhoneNumber。 （即OfficeMain对我来说是接待员的OfficDirect。）

期待收到大家的来信。

I am quite new to SQL and I am trying to make a few relations and come across the following.

I have 3 tables - Persons - PhoneNumbers - PhoneNumberCategories

Persons and PhoneNumbers is self explanatory I think and PhoneNumberCategories I use to indicate whether a phonenumber is for the office, home, mobile, fax etc.

Now I made a 4th table to make this many-to-many-to-many relation. This table has 4 columns:

ID PersonID PhoneNumberID PhoneNumberCategoryID

And I have set-up 3 relations here. One for all columns except the ID Column. It seems to work, but it just gives me a strange feeling. Can someone tell me if I am on the wright track or if I am going all wrong about this.

The reason I want this is as follows. Of course I need to link a person to a phone number. However, I may have a phone number that is the general office (This would be the row 'OfficeGeneral') number, and therefore linked to me because I work there. I also have a direct office (And this would be the row 'OfficeDirect') number. This is of course also linked to me. The general number however, is linked to all people in the office as 'OfficeGeneral'. Except for the receptionist, here it would be linked as 'OfficeDirect'. And this is the reason I came up with this many-to-many-to-many relation. I can not find much about it on the web. And that is reason enough to doubt if this is the correct way to go about it. Anyway, this is just an example. I would like to make sure that I am flexible and can catch as many exceptions as possible. I am sure once the database is in use that people will come with situations which I have not anticipated. People are good at that I have learned over the years.

Clarification in Response to Comment Below:

Person can have more than 1 PhoneNumber. PhoneNumber can have more than 1 Person. Person with PhoneNumber can have more than 1 PhoneNumberCategory (i.e. Private Phone used for both Phone and Fax.) Several people can be linked to the same PhoneNumber with different PhoneNumberCategories. (i.e. OfficeMain for me is OfficDirect for receptionist.)

Looking forward to hear from you all.

最满意答案

你的模型很好看。 或许你的描述不是。

Person和PhoneNumber有很多关系。 反过来，这种关系与PhoneNumberCategory关系是1-many，除非给定的电话号码可以在多个类别中。

我希望Person / PhoneNumber在此表中被声明为唯一，因此一个人只能使用一次特定的电话号码。 但是，这可能不是您查看结构的方式。

Your model looks fine. Your description, perhaps, is not.

Person and PhoneNumber have a many to many relationship. This relationship, in turn, is 1-many with PhoneNumberCategory -- unless a given phone number could be in more than one category.

I would expect that Person/PhoneNumber would be declared unique in this table, so a person could only use a particular phone number once. However, that may not be how you are viewing the structure.