CODEWITHSUNDEEP
sqldatabasedatabase-design
Peter Olson
Peter Olson

Reputation: 143037

How can you reference multiple rows in another table?

Let's suppose I have a table of roles, for example,

Roles
-----
ID  Name
0   Salesman
1   Client
2   Manager

Let's also suppose that these roles are not mutually exclusive: that is, a person can be a salesman, a client, and a manager all at the same time.

If I have a Person table with a Role field, how am I able to reference multiple rows in the Roles table?

Upvotes: 1

Views: 7675

Answers (3)

Carsten Gehling
Carsten Gehling

Reputation: 1256

For this to work, you will need to make a "n-m relation". You need an extra table, eg. called "person_role", that contains foreign keys to both the other tables:

Person <===> PersonRole <===> Role

So you will have 3 tables:

Person
------------
ID
Name
etc.

Role
---------
ID
Name

PersonRole
------------
PersonID
RoleID

You also should make PersonID and RoleID into an unique composite key, to avoid any duplicates

/ Carsten

Upvotes: 1

Marco
Marco

Reputation: 57603

You have to create another table having (at least) two columns: 

PersonId, RoleId

So you can insert (e.g.)

1, 0
1, 2
2, 1

and your Person with id=1 will be a Salesman and a Manager, while Person with id=2 will be a Client.

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

You introduce a new table that references both of the original tables:

CREATE TABLE PersonRoles (
    PersonID int not null,
    RoleID int not null,
    constraint PK_PersonRoles PRIMARY KEY (PersonID,RoleID),
    constraint FK_PersonRoles_Persons FOREIGN KEY (PersonID) references Person (ID),
    constraint FK_PersonRoles_Roles FOREIGN KEY (RoleID) references Role (ID)
)

Assuming that the multiplicity is m:n, the above is correct. I made the assumption (not listed in your question) that more than one person can be e.g. a Salesman.

Upvotes: 6

Related Questions