Associative entity

An associative entity is an element of the entity–relationship model. The database relational model requires use of a base ("named") relation to implement many-to-many relationships.[1] This new table is called an associative entity.

Associative entities are implemented using junction tables, a database table that contains common fields from two or more other database tables within the same database.

In most database systems, only one-to-one or one-to-many relationships between data tables can be created directly, usually by utilizing foreign keys. The Foreign Key (FK) usually is a Primary Key (PK) of another table and thus a unique constraint.

A junction table maps two or more tables together by referencing the primary keys of each data table. In effect, it contains a number of foreign keys, each in a many-to-one relationship from the junction table to the individual data tables. The primary key of the junction table is typically composed of the FK columns themselves, but may employ its own PK as well..[2]

Junction tables are known under many names, including mapping table, associative table, cross-reference table, bridge table, join table, intermediary table, intersection table, intersection entity, linking table, many-to-many resolver, link table, pairing table, transition table, crosswalk, associative entity or association table.

Using junction tables

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. Individual permissions may be granted to more than one user. 

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

A 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 multiple steps: first inserting into the main table(s), 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 tables.

See also

References

  1. ↑ Date, CJ (1995). An Introduction to Database Systems. Reading, Massachusetts: Addison-Wesley. p. 95. ISBN 020154329X.
  2. ↑ Date, CJ (1995). An Introduction to Database Systems. Reading, Massachusetts: Addison-Wesley. p. 359. ISBN 020154329X.
This article is issued from Wikipedia - version of the Saturday, January 16, 2016. The text is available under the Creative Commons Attribution/Share Alike but additional terms may apply for the media files.