In all DBMS (database management systems) there are several types of relationships between tables. Among them are one-to-one, one-to-many, many-to-one relationships (some tend to identify these two types into one) and many-to-many relationships. An example of the latter, its explanation and application in various DBMSs, such as Access or SQL, will be considered in this article.
Definition
A many-to-many relationship is defined as matching any instance of one of the entities of all instances of the other. In other words, each field from the first (second) table is associated with all fields from the second (first).
The presented scheme clearly reflects the essence of this relationship.
When can many-to-many relationships be used?
An example of a many-to-many relationship is a student group and a group of teachers. Each student learns from several professors at once, who, in turn, lecture to several students. The picture shows the difference between one-to-many and many-to-many relationships.
Many-to-many relationships are often necessary when compiling large-scale databases, small illustrative examples are usually used only for educational purposes, but in real practice it turns out that the more entities in the database and the more relationships between them, the more likely it is to turn to many-to-many relationship.
How to make many-to-many relationships?
Examples of the relationship under consideration will be added in the course of the article, however, it is important not only to understand what it is, but also how it can be implemented. The details of this process directly depend on the DBMS chosen for operation, while the principle remains the same for everyone.
Microsoft Access
Microsoft's office software product has been known in the software market for quite some time. It comes with a Worfd text editor, an Excel spreadsheet processor, and others in the office line. You can purchase Access (read as "access", the literal translation is "access") and separately from its "colleagues." It is recommended to buy, of course, licensed software, but it’s no secret to anyone how many pirated repacks can be found on the Web, in the form of ordinary files or distribution of torrents. Microsoft Access is available even in a portable build. It, which does not require installation and special PC skills, is best for choosing if the software will not be used for a long time and often.
It’s clear from the context that Microsoft Access is a database management system. And one of the most popular. It is relational, which means it is based on a logical data model that, in the course of its work, turns to set theory and first-order logic. The many-to-many relationship in Access (examples will be given during the explanation) is implemented very, very simple. Consider her.
There are two tables ..
In order not to come up with anything new, let us take the one already mentioned in order to clarify the many-to-many relationship, an example about students. It is necessary to create the table "Students" and the table "Teachers". Both the first and second of them have primary keys. To join instances of these two entities, one more table is also required, the fields of which are the keys of the first and second tables.
If we consider a different example: suppose football players and teams (given that at least one of the players played for different teams, and each team has eleven players), the essence of building the connection will not change. Three tables will also be needed. Of these, “Footballers” and “Teams” as the main ones, and one intermediate.
Data schema
Relations between tables in the Microsoft DBMS are implemented using the Data Schema tab. All necessary entities are added to the panel that appears (in our case, all three tables). The many-to-many relationship will be created using two one-to-many relationships between the main ones (“Students” and “Teachers”) and the intermediate table. For this, it is necessary to interconnect the corresponding primary keys.
The illustration above shows what the Relathionships tab looks like. The number of tables added to the panel is unlimited. The location is fully user adjustable.
SQL
Designing databases on SQL is a more complicated task than on Access. If the Microsoft product is fully adapted for the office environment, it has a huge and, with each release and update, everything is extensible, functional, but at the same time convenient for the simple user interface, then SQL is a separate non-procedural programming language that can be used on different platforms You can work with databases. Known software for this task: Oracle MySQL and DB2 (popular, but not one of a kind). Despite the fact that each of them has its own subtleties and nuances, the SQL language "unites them". Having learned to work with at least one of them, it will be much easier to deal with the other.
Creating, filling and directly acting on an existing database in SQL is necessary through special codes or scripts. However, those who have already reached the section "Many-to-many communications", an example of which in this programming language will be provided below, should know at least the basic commands and principles of using the SQL language.
Many-to-many relationship
A long introduction could somewhat confuse and “let the fog go,” but in reality the principle of implementing communication remains the same. In order for the many-to-many relationship type to be put into practice, not only in Access, but also in SQL, it is initially necessary to create two base tables and one intermediate table. The situation is similar with the keys: the main entities have main fields, each of which is written to the link table. Which means that the many-to-many SQL connection is not fundamentally different from "Access".
Communication implementation
To implement many-to-many relationships in SQL scripts, foreign keys (FOREIGN KEY) are used similar to the source keys in the main tables. They are recorded along with all fields when they are created and / or edited.
Many-to-many communication role
In general, relations between entities in databases are used for the integrity of the information stored in them. Only a well-designed database with all the necessary connections guarantees storage safety, ease of use and is a structure that is resistant to external influences and changes. Usually, if the database contains data about the whole organization, company or firm, it contains many entities with different instances.
And this means that when drawing up a data scheme (in "Access") or writing scripts (in "Oracle" or "DBT"), at least one many-to-many relationship will be present. An example of SQl, often used in teaching the course "Database Organization" - King Database.
King Database
This training database provides information about King Corporation. Among the tables:
- company employees - contains the employee code, his last name, first name and middle initial (focus on foreign names), also the code of the chief and position held by the employee, the date he entered the company, his salary and the stipulated commission, department code;
- departments of the corporation - among the fields of the table there is a code and the name of the department, as well as the code for its placement;
- Departments' location, which involves entering information on the location code and city name;
- Positions in the company - a small table with two fields of the position code and its official name;
- Buying companies - fields: customer’s code and name, address, city and state, postal code and region code, phone number, manager’s code serving the buyer, credit for the buyer and comments (notes and notes);
- sales contracts containing the code and date of the contract, the buyer's code, delivery date and the total amount of the contract;
- sales acts - the act code and the contract code, which includes the act, product code, its price, quantity purchased and total purchase price;
- goods - product code and name;
- prices - product code, price declared on it, lowest possible price, date of establishment and date of price cancellation.
Small tables that have no more than two or three fields are associated with a maximum of one table in a one-to-one or one-to-many relationship.
Large-scale tables, such as “company employees”, “buying firms”, “sales contracts” and “sales acts” are associated with several entities at once, and some with the help of “intermediaries” with a many-to-many relationship. The table "firms-buyers" itself is an intermediary, as such, because it has many fields borrowed from other tables and which are foreign keys. In addition, the scale and interconnection of the King Corporation database is such that all relationships are inextricably correlated with each other and affect one another. The destruction of at least one of them will entail the destruction of the integrity of the entire database.
Important Nuances
When implementing many-to-many communications, regardless of which DBMS is used, it is very important to correctly identify the keys by which the relationship will be compiled. An improperly implemented connection will not fulfill its main purpose, namely, ensuring the integrity of the table, and as a result, instead of the expected comfort, the user will, on the contrary, inconvenience and additional problems, especially when filling out the tables and editing the data in them.