An associative entity is an entity type in a database that associates two or more other entity types. It is used to resolve many-to-many relationships between the other entity types. For example, a student-course association entity can be used to link students to the courses they are enrolled in.
One associative entity is a term used in relational and entity relationship theory. A relational database requires the implementation of a base relationship (or base table) to solve many-to-many relationships. A base relation that represents this type of entity is called, informally, a 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 join) table maps two or more tables together by referencing the primary keys (PK) of each data table. Indeed, it contains a series of foreign keys (FK), each in a many-to-one relationship from the join table to the individual data tables. The PK of the join table is usually made up of the FK columns themselves.
Associative tables are colloquially known under many names, including association table, bridge table, cross reference table, crosswalk, middle table, crossing table, join table, junction table, link table, link table, many-to-many resolver, map table, mapping table, pairing table, dynamic 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
An example of the practical use of a join table would be assigning permissions to users. There can be multiple users and each user can be assigned zero or more permissions. Individual permissions can 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) );
SELECT * FROM Users JOIN UserPermissions USING (UserLogin);
This will return a list of all users and their permissions.
inserting on a junction table involves several steps: first inserting into the main table(s) and 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 values from the UserPermissions table to its own tables.
- Hoffer, Jeffrey A.; Prescott, Mary B.; McFadden, Fred R. (2004). Modern database management (7th ed.). Prentice Hall. ISBN 978-0131453203.
- codd, EF (1970). “A relational data model for large shared databases”. ACM Communications. ACM. 13 (6): 377–387. It hurts:10.1145/362384.362685.
Source: Associative entity
Video about The Entity Relationship Model Uses The Associative Entity To Represent
ERD: Associative Entities
Question about The Entity Relationship Model Uses The Associative Entity To Represent
If you have any questions about The Entity Relationship Model Uses The Associative Entity To Represent, please let us know, all your questions or suggestions will help us improve in the following articles!
The article The Entity Relationship Model Uses The Associative Entity To Represent was compiled by me and my team from many sources. If you find the article The Entity Relationship Model Uses The Associative Entity To Represent helpful to you, please support the team Like or Share!
Rate Articles Associative entity
Rate: 4-5 stars
Search keywords The Entity Relationship Model Uses The Associative Entity To Represent
2. Junction Table
5. Composite Key
6. Join Table
7. Composite Attribute
8. Intersection Entity
9. Relational Database
10. Foreign Key