Many-to-many relationships: an example in Access, in SQL. How to make a many-to-many relationship?

In all DBMSs (database management systems), there are several types of relationships between tables. Among them, a one-to-one, one-to-many, many-to-one relationship (some tend to identify these two types in one) and many-to-many relationships. An example of the latter, its explanation and application in various DBMS, such as Access or SQL, will be discussed in this article.

Definition

A many-to-many relationship is defined as matching any of the instances 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 diagram clearly shows the essence of this relationship.

When can many-to-many communication 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 who, in turn, give lectures to several students. The picture shows the difference between one-to-many and many-to-many.

Many-to-many relationships are often necessary for large-scale databases, small demonstrative examples are usually used only for educational purposes, in real practice it turns out that the more entities in the database and more interrelationships between them - the more likely the frequent access to Many-to-many relationship.

How to make a many-to-many relationship?

Examples of this relationship will be added in the course of the article, but it is important not only to understand what it is, but also how it can be realized. Details of this process directly depend on the database chosen for operation, while the principle remains one for all.

Microsoft Access

Office software from Microsoft is known in the software market for quite some time. It comes with a Worfd text editor, an Excel spreadsheet processor and the rest of the "office" line. You can access Access (read as "access", literal translation - "access") and separately from its "colleagues." It is recommended to buy, of course, licensed software, but it's not a secret for anyone how many pirate repacks you can find on the web, in the form of regular files or distributions of torrents. "Microsoft Access" is available even in a portable assembly. It, which does not require installation and special skills of working with a PC, is best suited for choice if the software will not be used for a long time and often.

From the context it is clear 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, refers to set theory and first-order logic. Many-to-many communication in Access (examples will be given during the explanation) is implemented very, very simply. Consider it.

There are two tables ..

In order not to invent anything new, let's take the one already mentioned in order to clarify the many-to-many relationship, an example of a student body. It is necessary to create a table "Students" and a table "Teachers". As in the first, and in the second of them there are primary keys. To combine instances of these two entities, one more table is required, the fields of which are the keys of the first and second tables.

If you consider a different example: say, players and teams (given that at least one of the players played for different teams, and each team has eleven players in its composition), the essence of building a connection will not change. Also three tables will be needed. Of these, "Footballers" and "Teams" as the main, and one intermediate.

Data schema

The links between the tables in the "Microsoft Access" DBMS are implemented using the "Data Schema" tab. On the appeared panel all necessary entities are added (in our case all three tables). Creating a many-to-many relationship will occur through two one-to-many relationships between the main ("Students" and "Teachers") and the intermediate table. To do this, you need to connect the corresponding primary keys.

The illustration above shows how the "Data schema" tab looks (Relathionships). The number of tables added to the panel is unlimited. The location is completely user adjustable.

SQL

Designing databases on SQL - the task is more complicated than on "Access". If the Microsoft product is fully adapted to the office environment, it has a huge and, with each release and update, all extensible functionality, but at the same time, user-friendly interface, SQL is a separate non-procedural programming language, with which on different platforms You can work with databases. Known software for this task: Oracle MySQL and DB2 (popular, but not the only one of its 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 "Many-to-Many Relationship" section, 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.

The principle of creating a many-to-many relationship

A lengthy introduction could be somewhat embarrassing and "fogging", but in fact the principle of communication realization remains the same. In order for the many-to-many type of communication to be implemented in practice, not only in "Access", but also in SQL, you must initially create two base tables and one intermediate table. Similarly, the case is with the keys: the main entities have the main fields, each of which is written to the link table. What does it mean that many-to-many SQL-communication does not fundamentally differ from "Access".

Communication implementation

To implement many-to-many communication, SQL scripts use foreign keys (FOREIGN KEY) similar to the original keys in the main tables. They are written together with all fields when they are created and / or edited.

The role of many-to-many communication

Generally, the relationships 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 the security of storage, convenience of work and is a structure that is resistant to external influences and changes. Usually, if the database contains data about an entire organization, company or firm, it contains many entities with different instances.

And this means that at least one multi-to-many relationship will be present when composing the data schema (in "Accessory") or writing scripts (in Oracle or DiBiT). An example of SQl, often used in training the course "Database Organization" - DB King.

King's database

This training database is information about King's Corporation. Among the tables:

• Employees of the firm - contains the code of the employee, his name, middle initial (orientation to foreign names), as well as the code of the chief and the position occupied by the employee, the date of his arrival in the firm, his salary and commissions, 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 of its placement;
• Placement of departments, which involves entering information about the code of the placement and the name of the city;
• Positions in the firm - a small table with two fields of job code and its official name;
• Buyer companies - fields: buyer's code and name, address, city and state, postal code and area code, phone number, customer service manager code, buyer's credit and comments (notes and notes);
• Sales contracts containing the code and date of the contract, the buyer's code, the date of delivery and the total amount of the contract;
• Sales acts - the code of the act and the contract code, which includes the act, the product code, its price, the amount of purchased and the total purchase price;
• Goods - the code and the name of the product;
• Prices - the product code, the price announced for it, the lowest possible price, the date of establishment and the date of cancellation of the price.

Small tables, in the presence of no more than two or three fields, are associated with a maximum of one table by a one-to-one or one-to-many relationship.

Large-scale tables, such as "company employees", "buyer firms", "sales contracts" and "sales acts" are associated with several entities at once, and with some - with the help of "intermediaries" many-to-many. The table "firms-buyers" itself is an intermediary, as such, because it has many fields, borrowed from other tables and are external keys. In addition, the scale and interrelation of the database of "King Corporation" is such that all relations are inextricably correlated and influence 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 communication, regardless of which DBMS is used, it is very important to correctly determine the keys with which the relationship will be generated. Incorrectly implemented communication 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 receive, on the contrary, inconveniences and additional problems, especially manifested when filling out tables and editing data in them.