Best way to relate two tables

Question

I have two tables (groups and roles):

Table groups:

+----+------+-------+------+
| id | name | color | role |
+----+------+-------+------+

Table roles:

+----+------+-------+-------------+
| id | name | alias | description |
+----+------+-------+-------------+

The question is: what is the best way to relate the two tables, so that:

• Each group (group) may have one or more functions (roles);
• Every function (role) can be connected to several groups at the same time.

Answer 1

Need to have an intermediate table to make relationship N:N:

Table groups_roles:

+----------+---------+
| id_group | id_role |
+----------+---------+

Answer 2

The reply from @Murillogoulart is correct but only to be more complete...

"In systems analysis, a many-to-many relationship (also abbreviated as N to N or N:N) is a type of cardinality that refers to the relationship between two entities A and L, in which A may contain a parent instance for which there are many daughter instances in L and vice versa.

For example, thinking of A as Authors and L as Books, an Author can write several Books and a Book can be written by several Authors.

In a relational database management system, such relationships are usually implemented through an associative table (also known as junction table or cross-reference table). For example, an AL table possessing two one-to-many relationships A AL and L AL. In this case, the logical primary key for AL is made up of two foreign keys (i.e., copies of the primary keys of A and L)." Wikipedia

Basically, in many-to-many relations, or N to N, a third table is created where the foreign keys are stored, or Foreign Keys (FK) of the two other tables, but not only that, the generated table, as well as the others has a single key (primary key)

When the relation cannot have repeat of foreign key combination

For example, a book may have multiple authors, who may have multiple books but there will never be the same author referencing the same book twice, in which case the index can be formed by merging the columns, the command can change according to the SQL language

In cases there may be two lines with the same combination of FK

A practical example (which I was taught), is a video rental company where it has a table of films and a table of clients, where a client can rent one or more films and the films can be rented by one or more clients, remembering that a video rental company has several units of the same film

Another column is created that will be the primary key for this table/relation:

   filmes_clientes      filmes     clientes
 +-----+-----+-----+    +-----+    +-----+
 | pk  | fk1 | fk2 |    | pk1 |    | pk2 |
 +-----+-----+-----+    +-----+    +-----+
 |  0  |  1  |  0  |    |  0  |    |  0  |
 +-----+-----+-----+    +-----+    +-----+
 |  1  |  1  |  0  |    |  1  |    |  1  |
 +-----+-----+-----+    +-----+    +-----+

Another option is to use another column that saves the date

If for some reason in this relation will be saved also the date in which it occurred can use the same to create the single key, for example:

           filmes_clientes                filmes     clientes
 +-----+-----+-----------------------+    +-----+    +-----+
 | fk1 | fk2 |          data         |    | pk1 |    | pk2 |
 +-----+-----+-----------------------+    +-----+    +-----+
 |  1  |  0  |  2018-04-30 16:05:00  |    |  0  |    |  0  |
 +-----+-----+-----------------------+    +-----+    +-----+
 |  1  |  0  |  2018-04-30 16:05:01  |    |  1  |    |  1  |
 +-----+-----+-----------------------+    +-----+    +-----+

But care should be taken in such cases, imagine a video rental company that has branches where a person rented a film with its user and its companion(a) rented the same film with the same user at the same time, difficult to happen (even more at a video rental company) but it can happen and create conflict. Of course you can also put beyond the date, the branch where the movie was rented, but there are already two extra columns that may not even be used, are only there for no duplicity, so a PK in the table originated from the relation N:N