Monday, November 25, 2013

Junction table

From Wikipedia, the free encyclopedia
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 tablebridge table,join tablemap tableintersection tablelinking tablemany-to-many resolverlink tablepairing tablepivot tabletransition 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.
A visual depiction of the table schema described, with relationships indicated

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

Using junction tables[edit]

SELECT-statement on a junction table usually involves joining the main table with the junction table:
SELECT * FROM Users
JOIN UserPermissions USING (UserLogin);
This will return a list of all users and their permissions.
Inserting into a junction table involves two steps: first inserting into the main table (for example, a new User), then updating the junction table.
-- Creating a new User
INSERT INTO Users (UserLogin, UserPassword, UserName)
VALUES ('SomeUser', 'SecretPassword', 'UserName');
 
-- Creating a new Permission
INSERT INTO Permissions (PermissionKey, PermissionDescription)
VALUES ('TheKey', 'A key used for several permissions');
 
-- Finally, updating the junction
INSERT INTO UserPermissions (UserLogin, PermissionKey)
VALUES ('SomeUser', 'TheKey');
Using foreign keys, the database will automatically dereference the values of the UserPermissions table to their own table.

See also[edit]

No comments: