How to organize the relationship between the database tables?

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!)
5def935936402099427883.png
April 3rd 20 at 17:19
1 answer
April 3rd 20 at 17:21
Solution
you need to store the history of examinations
in the table point add the field parent_point-that-be on this field to associate all the updates. Records in point will be as much as surveys.

added another method
If to produce the table in this context is bad, do this
method {point_id | surv_id | name | attr_name | value}

Ie, instead of tables of utm and radiographic 'd be something
point_id | surv_id | name | attr_name | value
1 | 1 | Utm | PMin1 | 13
1 | 1 | Utm | PMax1 | 114
2 | 2 | Radiographic | Depth | 11
1 | 3 | Radiographic | Depth | 10
@Lura.Graham Sorry for the long response, had a bit to digest and work the fuss began.
About table point, if you look at the picture below, after the fixation point 1D add a new line with the id of the previous line, right?
5df1dd0e26cec851726398.png
Then we get the history of States of the dots and how this hierarchy to build a query with the table surveys?
In fact, we have state dots and the survey contact by id, for example the first as point 1D is associated with the survey 01.01.2018 , the second 02.02.2019. You may just need the table point to make the status column, where you specify the working and non-working point?
5df1df3f6daa4354591417.png - tessie commented on April 3rd 20 at 17:24
@tessie, parent make not null, set to the parent ID it is the same, what would the query just WHERE parent = to do, and the parent can be found by MIN(date), i.e. the first point. Well, or WHERE id = SELECT parent, if the id to record, then on specific request it is necessary to watch that better.
"working or non-working" - that I do not understand what characterizes the point of working? MAX(date)? - Lura.Graham commented on April 3rd 20 at 17:27
@tessie, my answer is a solution or not?) - Lura.Graham commented on April 3rd 20 at 17:30
@Lura.Graham, I do not understand why would a parent put his own ID.
parent make not null, set to the parent ID it is the same, what would the query just WHERE parent = do

I suggest you store a history of points, but I need to store the connection point of the survey.
Or I did not understand your idea - tessie commented on April 3rd 20 at 17:33
@tessiethat would be one condition to select the parent and all of its further investigation on this field (parent_point) it will be convenient to hang the index. - Lura.Graham commented on April 3rd 20 at 17:36
@Lura.Graham, why when adding new points, the previous id to assign in new parentpoint? - tessie commented on April 3rd 20 at 17:39
@tessie, well, here it is possible to start from specific problems and queries - Lura.Graham commented on April 3rd 20 at 17:42

Find more questions by tags Database design