Junction table

From Wikipedia, the free encyclopedia

A junction 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.

[edit] See also

Database, Foreign key, Primary key

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),
PermissionKey varchar(50)
)

UserPermissions.UserLogin is a foreign key to Users.UserLogin and UserPermissions.PermissionKey is a foreign key to Permissions.PermissionKey.