ilari100 ilari100 - 1 month ago 4x
MySQL Question

MySQL: three tables related to each other

I am designing a database to store software testing data. The basic idea is the following: I want to store the information of each tested configuration, each tested software version and each found fault.

An example of a query I'd like to execute would be the following. I want to find all faults that were found on a specific configuration with a specific software.

My question is related to the table design, more specifically the relationships between them. Would this kind of design work in my case:


Or would it be better if the table "fault" was directly related to table "configuration"?

Thanks in advance.


Well, yes, it would be better to link directly "fault" to "configuration" as configuration_id should be a foreign key of "configuration.configuration_id" (the primary key). But, if there is only one "configuration" for each "software", then, since you already referenced "software_id" into "fault" table, you don't need to add "configuration_id" into "fault" as it is already present into "software" table.

You will retrieve all your data by this query:

SELECT * FROM fault INNER JOIN software ON fault.software_id = software.software_id INNER JOIN configuration ON software.configuration_id = configuration.configuration_id

Then, just remove "configuration_id" from "fault" table.