Junction table

From Wikipedia, the free encyclopedia

A junction table, sometimes also known as a "Bridge Table", "Map Table", or "Link Table", is a table that contains common fields from two tables. It is on the many side of a one-to-many relationship with the other two tables.

Junction tables are employed when dealing with many-to-many relationships in a database. A practical use of a junction table would be to assign permissions to users. There can be multiple users, and each user can be assigned 0 or more permissions.

CREATE TABLE Users
(
UserLogin varchar(50) PRIMARY KEY,
UserPassword varchar(50) NOT NULL,
UserName varchar(50) NOT NULL
)
 
 
CREATE TABLE Permissions
(
PermissionKey varchar(50) PRIMARY KEY,
PermissionDescription varchar(500) NOT NULL
)
 
 
--This is the junction table.
CREATE TABLE UserPermissions
(
UserLogin varchar(50) REFERENCES Users (UserLogin),
PermissionKey varchar(50) REFERENCES Permissions (PermissionKey),
PRIMARY KEY (UserLogin, PermissionKey)
)