CODEWITHSUNDEEP
sqlsql-servermany-to-manyrelationship
Joey Joystick
Joey Joystick

Reputation: 145

Many-to-Many-to-Many Relation in SQL?

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:

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:

Looking forward to hear from you all.

Upvotes: 0

Views: 204

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

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.

Upvotes: 1

Related Questions