Associative entity: Difference between revisions
No edit summary |
Edited table name "User" to corresponding picture |
||
Line 13: | Line 13: | ||
<source lang="sql"> |
<source lang="sql"> |
||
CREATE TABLE |
CREATE TABLE Users ( |
||
UserLogin varchar(50) PRIMARY KEY, |
UserLogin varchar(50) PRIMARY KEY, |
||
UserPassword varchar(50) NOT NULL, |
UserPassword varchar(50) NOT NULL, |
||
Line 37: | Line 37: | ||
A [[Select (SQL)|SELECT]]-statement on a junction table usually involves [[Join (SQL)|joining]] the main table with the junction table: |
A [[Select (SQL)|SELECT]]-statement on a junction table usually involves [[Join (SQL)|joining]] the main table with the junction table: |
||
<source lang="sql"> |
<source lang="sql"> |
||
SELECT * FROM |
SELECT * FROM Users |
||
JOIN UserPermission USING (UserLogin); |
JOIN UserPermission USING (UserLogin); |
||
</source> |
</source> |
||
Line 47: | Line 47: | ||
<source lang="sql"> |
<source lang="sql"> |
||
-- Creating a new User |
-- Creating a new User |
||
INSERT INTO |
INSERT INTO Users (UserLogin, UserPassword, UserName) |
||
VALUES ('SomeUser', 'SecretPassword', 'UserName'); |
VALUES ('SomeUser', 'SecretPassword', 'UserName'); |
||
Revision as of 08:39, 9 April 2017
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. This kind of base relation is called an associative table.
![](http://upload.wikimedia.org/wikipedia/commons/1/14/Associate_Entity.png)
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.
![Concept of a mapping table](http://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 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 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 in Laravel - not to be confused with pivot table (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 Permission (
PermissionKey varchar(50) PRIMARY KEY,
PermissionDescription varchar(500) NOT NULL
)
-- This is the junction table.
CREATE TABLE UserPermission (
UserLogin varchar(50) REFERENCES User (UserLogin),
PermissionKey varchar(50) REFERENCES Permission (PermissionKey),
PRIMARY KEY (UserLogin, PermissionKey)
)
![A visual depiction of the table schema described, with relationships indicated](http://upload.wikimedia.org/wikipedia/commons/thumb/a/a3/Junction_Table.svg/498px-Junction_Table.svg.png)
A SELECT-statement on a junction table usually involves joining the main table with the junction table:
SELECT * FROM Users
JOIN UserPermission 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 Permission (PermissionKey, PermissionDescription)
VALUES ('TheKey', 'A key used for several permissions');
-- Finally, updating the junction
INSERT INTO UserPermission (UserLogin, PermissionKey)
VALUES ('SomeUser', 'TheKey');
Using foreign keys, the database will automatically dereference the values of the UserPermission table to their own tables.
See also
References
- Modern Database Management - 7th Edition - Jeffrey A. Hoffer, Mary B. Prescott, Fred R. McFadden
- Codd, E.F. (1970). “A Relational Model of Data for Large Shared Data Banks ”. Communications of the ACM 13 (6): 377–387.