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