ilari100 ilari100 - 3 months ago 8
MySQL Question

Foreign keys in many-to-many relationships

The following picture is a part of a software test results database that I'm designing:
[[RELATIONSHIPS]

My question is related to the table

fault
. Do I need a foreign key that is related to the table
configuration
in my table
fault
in order to make a query that, for example, would return me data that is related to all three tables (
configuration
is related to
software
and
fault
is related to both:
software
and
configuration
). Or should the relations be designed in different way?

If I had one-to-many relationships between the tables, MySQL Workbench would automatically create foreign keys in the tables that are needed. But this is all MySQL Workbench automatically created when I use many-to-many relationships.

Thank you in advance.

EDIT:

I manually added some data via phpmyadmin. However, I am trying to execute this query:

SELECT software_version, configuration_name, actCritical
FROM software
LEFT JOIN configuration_has_software ON software.software_id = configuration_has_software.software_id
LEFT JOIN configuration ON configuration.configuration_id = configuration_has_software.configuration_id
LEFT JOIN software_has_fault ON fault.fault_id = software_has_fault.fault_id
LEFT JOIN software ON software.software_id = software_has_fault.software_id;


I get an error:

1066 - Not unique table/alias: 'software'



Am I on right track?

EDIT2:

Actually I started to question my design. I don't think that I am able to relate specific faults to specific configuration with this design as there isn't a direct relationship between configuration and fault. Should I design it differently?

Answer

To fix your error, try this: You are joining the table software twice, but not joining the fault table.

SELECT software_version, configuration_name, actCritical
FROM software
LEFT JOIN configuration_has_software 
       ON software.software_id = configuration_has_software.software_id
LEFT JOIN configuration 
       ON configuration.configuration_id = configuration_has_software.configuration_id
LEFT JOIN software_has_fault 
       ON software.software_id = software_has_fault.software_id
LEFT JOIN fault 
       ON software_has_fault.fault_id = fault.fault_id ;

I assume you have used LEFT JOIN to get the softwares listed even if there is no configuration (or) fault exists for those softwares (if that is not the case, you can replace with INNER JOIN)