An associative entity is a term used in
relational and
entity–relationship theory. A relational database requires the implementation of a base relation (or base table) to resolve
many-to-many relationships. A base relation representing this kind of entity is called, informally, an associative table.
As mentioned above, associative entities are implemented in a database structure using associative tables, which are tables that can contain references to columns from the same or different database tables within the same database.
![mapping_table_concept](https://upload.wikimedia.org/wikipedia/commons/d/d7/Mapping_table_concept.png)
An associative (or junction) table maps two or more tables together by referencing the
primary keys In relational database management systems, a unique key is a candidate key that is not the primary key of the relation. All the candidate keys of a relation can uniquely identify the records of the relation, but only one of them is used as the pri ...
(PK) of each data table. In effect, it contains a number of
foreign keys
''Foreign Keys'' is the second album released in 1985, 11th album overall, by musician Jandek, and his eleventh overall. This is the first Jandek album featuring a full band, and is without acoustic numbers. It is also an album split between trac ...
(FK), each in a many-to-one relationship from the junction table to the individual data tables. The PK of the associative table is typically composed of the FK columns themselves.
Associative tables are colloquially known under many names, including association table, bridge table, cross-reference table, crosswalk, intermediary table, intersection table, join table, junction table, link table, linking table, many-to-many resolver, map table, mapping table, pairing table, pivot table (as used incorrectly in
Laravel
Laravel is a free and open-source PHP web framework, created by Taylor Otwell and intended for the development of web applications following the model–view–controller (MVC) architectural pattern and based on Symfony. Some of the features ...
- not to be confused with the correct use of
pivot table in spreadsheets), or transition table.
Using associative tables
An example of the practical use of an associative table would be to assign permissions to users. There can be multiple users, and each user can be assigned zero or more permissions. Individual permissions may be granted to one or more users.
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)
);
![Junction Table](https://upload.wikimedia.org/wikipedia/commons/a/a3/Junction_Table.svg)
A
SELECT-statement on a junction table usually involves
joining Join may refer to:
* Join (law), to include additional counts or additional defendants on an indictment
*In mathematics:
** Join (mathematics), a least upper bound of sets orders in lattice theory
** Join (topology), an operation combining two topo ...
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
*
Many-to-many (data model)
In systems analysis, a many-to-many relationship is a type of cardinality that refers to the relationship between two entities, say, A and B, where A may contain a parent instance for which there are many children in B and vice versa.
For exa ...
*
Relational database
A relational database is a (most commonly digital) database based on the relational model of data, as proposed by E. F. Codd in 1970. A system used to maintain relational databases is a relational database management system (RDBMS). Many relatio ...
*
Unique key In relational database management systems, a unique key is a candidate key that is not the primary key of the relation. All the candidate keys of a relation can uniquely identify the records of the relation, but only one of them is used as the prima ...
References
*
*{{cite journal , last=Codd , first=E. F. , authorlink=Edgar F. Codd , year=1970 , title=A Relational Model of Data for Large Shared Data Banks , journal=Communications of the ACM , publisher=ACM , doi=10.1145/362384.362685 , volume=13 , issue=6 , pages=377–387
Entity–relationship model
Diagrams