I have a question about how to relate two tables.
The problem is I don't know what type of relationship to create for this example.
Taking into account that in my FUEL table I have 2 records:
IDcombustible | Combustible
---------------------------
1 | Diesel
2 | Gasolina
Now I enter an engine and assign the fuel type to it. Number 1 which is diesel.
idmotor | tipo_motor | combustible_idcombustible
------------------------------------------------
1 | '2.5cc' | 1
Now the problem is that I need to enter another '2.5cc' engine but it exists on gasoline.
idmotor | tipo_motor | combustible_idcombustible
------------------------------------------------
1 | '2.5cc' | 2
When I do that, I realize that I have entered another record in the MOTOR table again with the column tipo_motor
with the value '2.5cc'
but with the column combustible_idcombustible
with the value 2. I think it is not correct because there is data redundancy.
What would be the correct way to relate the two tables?
It sounds like you need to create an additional table that supports your many-to-many relationship between the tables
motor
andcombustible
. This would make you have less data redundancy. So:And there you associate the records of both tables.
Just in case, doing this means you're going to have to remove the direct relationship between your table
combustible
and your tablemotor
.An analysis of the following questions can point the way: Can an engine use different types of fuel? Can a fuel be used in different engines? If both answers are true, you can use a "many-to-many" relationship which makes use of an intermediate table between engine and fuel, for example engine_fuel, which would contain the engine id and the fuel id (removing the id from fuel from the engine table).
In this way, you would only store the relationships between an engine and a fuel. If you want to delve into how to avoid data redundancy, I invite you to investigate database normalization .
Similar to what Luiggi Mendoza said, and without overlooking the database engine, you can do it with two tables, just like in your diagram:
Your second table would look like this: