2

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

migrated from stackoverflow.com Jul 18 '18 at 5:28

This question came from our site for professional and enthusiast programmers.

2

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.

  • You're right. company_id is primary key. But is it needed to be primary? This column is created automatically too. Workbenck makes all foreign keys primary. What's the benefit? – rostamiani Jul 18 '18 at 7:18
  • 1
    @rostamiani Making the columns from the foreign keys a primary key in the many to many table is to enforce non-duplicates and also to speed up queries as primary keys in most (if not all) databases also creates an index by these columns. Check if the primary key on pstx_project table is actually just id or if it also includes company_id. – EzLo Jul 18 '18 at 7:24
  • both id and company_id are listed as primary keys and there is two indexes. on for both and one just for company_id. company_id is for a 1 to many relation not m2m. update: both are primary keys but just id is unique. – rostamiani Jul 18 '18 at 7:30
  • 2
    @rostamiani I believe you should change pstx_project's primary key from (id, company_id) to (id), unless you can have the same project linked with several companies. Once you change this, recreate the many to many table and it will only consider id from project, and not company_id. – EzLo Jul 18 '18 at 8:03
  • 1
    @rostamiani i don't think you need company_id on project table. If the project changes company and the primary key contains company_id then you will have to create a new row and delete the previous one, instead of just doing an update. You can have an identifying relationship just with a non-null foreign key, doesn't necessarily have to be part of the primary. That being said, when you create a m2m table, it needs all columns for both primary keys. If you still doubt that company_id isn't necessary then you realise it shouldn't be PK on project. – EzLo Jul 18 '18 at 8:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.