Foreign keys in tables

Hello everyone,

I am new in MySQL and this might be a silly question but here it goes:

I want to create multiple tables for storing data for construction materials. For example, the first table, named 'materials' will contain the id (auto-incement and Primary key), the name of the material, the company that produces the material, the country etc. The second table, named 'physical_properties' will also contain the same materials, with one column for the id and one for the name, but the other columns will be the physical properties, such as density, u-value etc.

I managed to create two foreign keys in 'physical_properties' for the name and id and I can choose from a dropdown the values from 'materials' for these two columns. However, I want these two columns to be linked to each other, so that when I choose another material for the names column, the id should also change to match the id that corresponds to this material in the table 'materials'. Is there a way to do that?

Thanks in advance, Polina

You should remove the second column and its foreign key, because it's redundant. The "name" column should only be in one table, not in both. To link a row from one table to another, you only need one foreign key, not two.

I guess you intended the "name" column as well to have it visible where you don't know the id's behind it. But that's bad database design. Or in other words: that should be part of an external application for editing these values, not of the database itself. The database itself doesn't need it, and you would just overload it.

I see what you mean. Then I suppose I will use the name as the primary key for "materials" and foreign key for "physical_properties", because it is more valuable to me than the id. Thanks for the reply :)