Reputation: 143037
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
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
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
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