Associative entity: Difference between revisions
Preceding edits: Merged content from Junction table to here. See Talk:Associative_entity#Merge_proposal |
add {redirect} |
||
(33 intermediate revisions by 23 users not shown) | |||
Line 1: | Line 1: | ||
{{Short description|Term in relational and entity–relationship theory}} |
|||
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 (data model)|many-to-many relationships]].<ref>{{cite book | title=An Introduction to Database Systems | publisher=Addison-Wesley | author=Date, CJ | year=1995 | location=Reading, Massachusetts | pages=95 | isbn=020154329X}}</ref> This new table is called an '''associative entity'''. [[Image:Associate_Entity.png|frame|An associative entity (using [[Peter Chen|Chen]] notation)]] |
|||
{{redirect|Crosswalk table|multi-database relations|Schema crosswalk}} |
|||
{{no footnotes|date=May 2018}} |
|||
An '''associative entity''' is a term used in [[relational model| relational]] and [[entity–relationship model|entity–relationship]] theory. A relational database requires the implementation of a base relation (or [[base table]]) to resolve [[Many-to-many (data model)|many-to-many relationships]]. A base relation representing this kind of entity is called, informally, an '''associative table'''. [[Image:Associate_Entity.png|frame|An associative entity (using [[Peter Chen|Chen]] notation)]] |
|||
⚫ | |||
⚫ | |||
In most database systems, only one-to-one or one-to-many relationships between data tables can be created directly, usually by utilizing [[Foreign key|foreign keys]]. The Foreign Key (FK) usually is a [[Primary Key]] (PK) of another table and thus a unique constraint. |
|||
[[File:mapping_table_concept.png|right|Concept of a mapping table]] |
[[File:mapping_table_concept.png|right|Concept of a mapping table]] |
||
An associative (or junction) table maps two or more tables together by referencing the [[primary key|primary keys]] (PK) of each data table. In effect, it contains a number of [[foreign key|foreign keys]] (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]]—not to be confused with the correct use of [[pivot table | pivot table in spreadsheets]]), or '''transition table'''. |
|||
==Using |
==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. |
|||
< |
<syntaxhighlight lang="sql"> |
||
CREATE TABLE Users ( |
CREATE TABLE Users ( |
||
UserLogin varchar(50) PRIMARY KEY, |
UserLogin varchar(50) PRIMARY KEY, |
||
UserPassword varchar(50) NOT NULL, |
UserPassword varchar(50) NOT NULL, |
||
UserName varchar(50) NOT NULL |
UserName varchar(50) NOT NULL |
||
) |
); |
||
CREATE TABLE Permissions ( |
CREATE TABLE Permissions ( |
||
PermissionKey varchar(50) PRIMARY KEY, |
PermissionKey varchar(50) PRIMARY KEY, |
||
PermissionDescription varchar(500) NOT NULL |
PermissionDescription varchar(500) NOT NULL |
||
) |
); |
||
-- This is the junction table. |
-- This is the junction table. |
||
Line 31: | Line 33: | ||
PermissionKey varchar(50) REFERENCES Permissions (PermissionKey), |
PermissionKey varchar(50) REFERENCES Permissions (PermissionKey), |
||
PRIMARY KEY (UserLogin, PermissionKey) |
PRIMARY KEY (UserLogin, PermissionKey) |
||
) |
); |
||
</syntaxhighlight> |
|||
</source> |
|||
[[File:Junction Table.svg|center|A visual depiction of the table schema described, with relationships indicated]] |
[[File:Junction Table.svg|center|A visual depiction of the table schema described, with relationships indicated]] |
||
Line 38: | Line 40: | ||
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: |
||
< |
<syntaxhighlight lang="sql"> |
||
SELECT * FROM Users |
SELECT * FROM Users |
||
JOIN UserPermissions USING (UserLogin); |
JOIN UserPermissions USING (UserLogin); |
||
</syntaxhighlight> |
|||
</source> |
|||
This will return a list of all users and their permissions. |
This will return a list of all users and their permissions. |
||
Line 47: | Line 49: | ||
[[Insert (SQL)|Inserting]] into a junction table involves multiple steps: first inserting into the main table(s), then updating the junction table. |
[[Insert (SQL)|Inserting]] into a junction table involves multiple steps: first inserting into the main table(s), then updating the junction table. |
||
< |
<syntaxhighlight lang="sql"> |
||
-- Creating a new User |
-- Creating a new User |
||
INSERT INTO Users (UserLogin, UserPassword, UserName) |
INSERT INTO Users (UserLogin, UserPassword, UserName) |
||
Line 59: | Line 61: | ||
INSERT INTO UserPermissions (UserLogin, PermissionKey) |
INSERT INTO UserPermissions (UserLogin, PermissionKey) |
||
VALUES ('SomeUser', 'TheKey'); |
VALUES ('SomeUser', 'TheKey'); |
||
</syntaxhighlight> |
|||
</source> |
|||
Using foreign keys, the database will automatically dereference the values of the UserPermissions table to their own tables. |
Using foreign keys, the database will automatically dereference the values of the UserPermissions table to their own tables. |
||
==See also== |
==See also== |
||
* [[Many-to-many (data model)]] |
* [[Many-to-many (data model)]] |
||
* [[ |
* [[Relational database]] |
||
* [[Unique key]] |
* [[Unique key]] |
||
==References== |
==References== |
||
{{Reflist}} |
{{Reflist}} |
||
*{{cite book |last1=Hoffer |first1=Jeffrey A. |last2=Prescott |first2=Mary B. |last3=McFadden |first3=Fred R. |year=2004 |title=Modern Database Management |edition=7th |publisher=Prentice Hall |isbn=978-0131453203}} |
|||
* Modern Database Management - 7th Edition - Jeffrey A. Hoffer, Mary B. Prescott, Fred R. McFadden |
|||
* Codd |
*{{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|doi-access=free }} |
||
[[Category:Entity–relationship model]] |
[[Category:Entity–relationship model]] |
Latest revision as of 21:24, 23 July 2024
This article includes a list of references, related reading, or external links, but its sources remain unclear because it lacks inline citations. (May 2018) |
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.
An associative (or junction) table maps two or more tables together by referencing the primary keys (PK) of each data table. In effect, it contains a number of foreign keys (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—not to be confused with the correct use of pivot table in spreadsheets), or transition table.
Using associative tables
[edit]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)
);
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
[edit]References
[edit]- Hoffer, Jeffrey A.; Prescott, Mary B.; McFadden, Fred R. (2004). Modern Database Management (7th ed.). Prentice Hall. ISBN 978-0131453203.
- Codd, E. F. (1970). "A Relational Model of Data for Large Shared Data Banks". Communications of the ACM. 13 (6). ACM: 377–387. doi:10.1145/362384.362685.