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.

如果我在表之间有一对多的关系,MySQL Workbench会自动在所需的表中创建外键。但是当我使用多对多关系时,这就是所有MySQL Workbench自动创建的。

Thank you in advance.

先谢谢你。

EDIT:

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

我通过phpmyadmin手动添加了一些数据。但是,我正在尝试执行此查询:

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?

实际上我开始质疑我的设计。我不认为我能够将特定故障与特定配置相关联,因为配置和故障之间没有直接关系。我应该设计不同的吗?

2 个解决方案

#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)

我假设您已经使用LEFT JOIN来获取列出的软件,即使这些软件没有配置(或)故障(如果不是这样,您可以用INNER JOIN替换)

#2

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.

没有!它会打破3NF。如果您不知道它是什么和/或为什么您可能需要更多地研究ER(实体 - 关系模型)中的关系。这是一个我无法在此解释的复杂主题。但是互联网上有很多文档。

#1