0

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?

ilari100
  • 7
  • 3
  • When you have indirect relationships like this, you use multiple joins in the query to get related data. – Barmar Aug 23 '16 at 18:46
  • @Barmar I added another question, please check it if you have time – ilari100 Aug 24 '16 at 09:03
  • When a table appears in multiple `FROM` or `JOIN` clauses, you need to give them aliases so you can distinguish them. – Barmar Aug 24 '16 at 15:28

2 Answers2

1

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.

pid
  • 11,472
  • 6
  • 34
  • 63
1

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)

ramu
  • 1,415
  • 1
  • 13
  • 12
  • This works, thank you! I went through my SELECT query many times while trying different approaches and I just didn't find an error. I guess I became blind to my own writing :). I guess this same kind of approach can be used when doing INSERT to the same tables? – ilari100 Aug 25 '16 at 15:55
  • Inserting into all the tables at once ? Nope. See these posts [MySQL inserts](http://stackoverflow.com/questions/5178697/mysql-insert-into-multiple-tables-database-normalization) , [MySQL inserts 2](http://stackoverflow.com/questions/3860280/sql-insert-into-multiple-tables-in-one-query) – ramu Aug 26 '16 at 02:35