Why MySQL Workbench creates more than two foreign keys for many to many relations?

Posted on

Question :

I’m using MySQL Workbench. I know that an additional table is needed to define a many to many relation. But why sometimes workbench creates this table with more than two foreign keys?
Is it added managing performance issues?
How should I make queries with this structure?

For example this many to many relation is created automatically with three foreign keys:

enter image description here

Answer :

If id is the primary key for pstx_project then you definitely don’t need pstx_project_company_id in your many to many breakdown table. As you say, these tables just need their related tables primary keys (in which it’s composite will become it’s own primary key), and a separate foreign key against each referenced table (which might be composite also, depending on the amount of columns from the referenced table’s primary key), with the addition of other optional columns you might want to add.

If company_id was part of the primary key of pstx_project in some point, then it would make sense to include it in your many to many table. Maybe you edited the primary key at some point?

Remove pstx_project_company_id from your many to many table as it will bring you problems with consistency, as it would be redundant. Also make sure that user_id with pstx_project_id inside user_has_pstx_project is a primary key so it enforces non-duplicates.

As a side note, I also note a loop in your design: there are 2 ways to get to a company from a user. While loops are possible in some scenarios, please review if it’s correct for your case as you might have another redundant link between user and company.

Leave a Reply

Your email address will not be published. Required fields are marked *