Junction Table

In database management systems following the relational model, a junction table is a database table that contains common fields from two or more other database tables within the same database. It is on the many side of a one-to-many relationship with each of the other tables. Junction tables are known under many names, among them cross-reference table, bridge table, join table, map table, intersection table, linking table, many-to-many resolver, link table, pairing table, pivot table, transition table, or association table.

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) )