Hi all! Please suggest how to organize relationships between tables.
The situation is the following:
1. There is a table with Survey(Survey), it shall specify the date of examination, type etc
2. There is a table Point(Point) of the surveyed objects with characteristics.
3. Table Utm, Radiographic here are the results of the surveys on the methods of surveys.
As can be seen from the diagram, table and Survey Point are connected via a staging table Utm and Radiographic, as the point can be examined as soon as Utm method, and at the same time Utm and Radio. For example, if you added another test method, you need to add a table3 with key point and survey, is it right? I have a feeling that there will be difficulties with sample data.
There is another question, the measuring point can be fixed and, accordingly, change the characteristics, but I need to store the history of surveys taking into account the repaired points(point name may remain the same). For example, point 1 was surveyed 01.01.2018, repair occurred 01.01.2019, and already updated the point was surveyed 05.05.2019, and then at the sample of these surveys 01.01.2018 point 1 should be displayed with the features to 05.05.2019 be displayed with updated specifications. While the idea to create an archive table, but I think that can confused the new and old measurements.
Come to the rescue!)