I have a Table 'files', with the orders and competitions in which a company participates.
In every order I can have a list of materials ordered.
Thus, there should be another Table called 'materials' with a relation n:m to the 'orders' Table.
Every material ordered can (and must) have an industry from which it was bought from. These industry from where a specific material was bought again under a different order can be different; e.g. material X was bought from industry A in order 1, while in order 2 the same material X was bought from industry B and another material Y was bought from industry A.
Quite complicated, isn't it. Wait, there is more to come.
Every material can have as many competitors, as we wish. Competitors again can differ under different orders, therefore we have again a relation m:n between the Table of 'materials' and that of the 'competitors'.
In the beginning I thought that my starting Table should be that of the orders. After experimenting enough I am thinking that the 'materials' Table should be the main one while thinking of the Database construction, although the user should always see orders, which is actually his main concern.
I assume that I should have a separate Table including the FKs from the Tables