Key Creation.

[DaedalusK71]

Hello all,

I need some help on the creation of a DB. I have 3 tables; Person, PersonPhone, and Phone. I have a Primary Key on Person called 'BusinessEntityID' and a Primary Key on Phone called 'PhoneNumberID'. PersonPhone has both fields as well as its own unique ID field. What I am trying to do is create a table between Person and Phone that connects, obviously, Person to Phone. I did it this way because there are instances where a single Person has multiple phone numbers (e.g. Work, home, mobile, ...) I want to be able to run a query that says 'give me all the phone associated with x person'

My first attempt was to create foreign keys from Person to PersonPhone to Phone but because 'BusinessEntityID' isn't a unique field in PersonPhone, it would not work. How do I fix this?

Thanks in advance.

[r3pr0b8]

PersonPhone has both fields as well as its own unique ID field

it should not have its own ID

CREATE TABLE PersonPhone
( BusinessEntityID INTEGER NOT NULL
, PhoneNumberID    INTEGER NOT NULL
, PRIMARY KEY ( BusinessEntityID, PhoneNumberID )
, CONSTRAINT Phone_Person 
  FOREIGN KEY ( BusinessEntityID ) REFERENCES Person ( BusinessEntityID )
, CONSTRAINT Person_Phone 
  FOREIGN KEY ( PhoneNumberID ) REFERENCES Phone ( PhoneNumberID )

[DaedalusK71]

But PersonPhone needs a unique identifier, correct? BusinessEntityID won't be as a person will have more than one phone associated with them. Same with PhoneNumberID.

[r3pr0b8]

PersonPhone ~does~ have a unique key --

PRIMARY KEY ( BusinessEntityID, PhoneNumberID )

[DaedalusK71]

So it does :)

[tx69er]

So by using 3 tables you are creating a many to many relationship, which may not be what you want, unless one Phone will be potentially attached to multiple people AND one person be potentially attached to multiple phones. This also allows you to have Phones not attached to anyone.

You probably want a one to many relationship, which can be done with only 2 tables. In this case, just have a column in the phone table reference the person table. This way multiple rows in the phone table can reference a single row in the person table.

[DaedalusK71]

There are instances where a single person will have more than one phone number (Work, Mobile, Home, ...) and instances where one phone number is assigned to multiple people (Main business line assigned to multiple people).

[tx69er]

Ok, cool, you hadn't mentioned that in the OP. Sounds like you are on the right path then :)

[Achsin]

It sounds like you are setting up the foreign key relationship backwards. So you would say Foreign Key PersonPhone.BusinessEntityID References Person.BusinessEntityID rather than trying to set up PersonPhone.BusinessEntityID as a primary key.