INSERT and UPDATE in n:m relationship in Mysql

Question

I need to create a n:m relationship in mysql, follow my example:

I have a table called empresas and another representantes. An enterprise may have more than one representative, and a representative may belong to more than one enterprise. In this case, the best relationship that fits this problem, is relationship n:m.

In relationship n:m, there is a third table linking the representative and a company. How can I make an INSERT and an UPDATE in this third table recursively, without duplicating the records, knowing that when registering a company, I have several representatives, and vice versa?

Answer 1

I don’t know if I understand the need, but I think it would be:

insert into empresas_has_representantes (empresas_id, representantes_id) values (<id da empresa>, <id do representante>)

How this data persists depends a lot on what Oce is using to manage your data access layer.

In Hibernate, for example, there is a type of relationship @Manytomany that treats this to Voce automatically. Something about Voce le here

If Voce uses pure JDBC, or pure database access, Oce needs to iterate its data and handle it manually, using the query I posted above to put the relations.

Obviously before the relationship persists, Oce needs to persist companies and/or representatives.

As for the duplicity, Voce can guarantee via bank, inserting a Unique Constraint of the two columns. More information here