CODEWITHSUNDEEP
mysqlsqldatabasemany-to-manydata-modeling
ilari100
ilari100

Reputation: 7

Foreign keys in many-to-many relationships

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

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?

Upvotes: 0

Views: 661

Answers (2)

ramu
ramu

Reputation: 1495

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)

Upvotes: 1

pid
pid

Reputation: 11597

NO! It would break 3NF. If you don't know what it is and/or why you probably need to study a bit more about relations in the ER (entity-relationship model). This is a vast complex topic I can't explain here. But there's plenty of documentation on the internet.

Upvotes: 1

Related Questions