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?
-- - . , , , . -- --.
-- , , , , - --.
--?
, , , , . , .
Microsoft Access
"" . Worfd, Excel , "". Access ( "", - "") "". , , , , , . " " . , , , .
, " " - . . , , , . -- Access ( ) . .
..
, , --, . "" "". , . , - .
: , ( , , ), . . "" "" , .
" " " ". ( ). -- -- ("" "") . .
, " " (Relathionships). . .
SQL
SQL - , "". , , , , , SQL - , . : Oracle MySQL DB2 (, ). , , SQL "". , .
, SQL . , " --", , SQL.
--
" ", . -- , "", SQL, - . : , . , SQL- -- "".
-- SQL (FOREIGN KEY) . / .
--
, . , , . , , , .
, ( "") ( "" "") --. SQl, " " - .
. :
- - , , ( ), , , , ;
- - , ;
- , ;
- - ;
- - - : , , , , , , ( );
- , , , ;
- - , , , , ;
- - ;
- - , , , .
, - , -- --.
, " ", "-", " " " " , - "" --. "-" , , , . , " " , . .
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.