There is a task in the database to save the application:

Applications:
- Each application may have one or several standard targets.
- One target can be contained in multiple applications.

Goals:
- Each goal can have several standard tasks.
- One task can be contained for several purposes.

Methods:
- Each task may have several standard methods.
- One method can be included in many tasks
enter image description here
I suppose that there should be some kind of dictionaries (tables) of standard goals / tasks / methods. But I can not think of or understand how to reduce all this to a single application?

Is this structure correct? Like this?

  • What do you mean by "put in one application"? - Mirdin
  • one
    on the example of "goal" - "task": each goal has properties (at least id and, possibly, a name), each task also has some properties (at least id and, possibly, a name), these are already two tables. To connect the "goal" and "task" we add a connection between them, i.e. The third table is a task-task, which has properties of task-id and goal-id. Those. one row in this table is the connection of one goal with one task. All tasks of the goal are all the lines with the desired "id_target", all the goals of a specific task are all the lines with the necessary "id_a task". Similarly, we bind the rest. Is it clear? - BOPOH
  • It’s not obvious what the given structure is trying to implement. If we are talking about solving problems, then obviously there is not enough flag "decided / not-helped / try / in the link tables. - Evgeny Borisov

1 answer 1

Summarizing what I suggested @RAVON in the cometary to the question - you need to make at least 7 tables, if without 'directories' and 'templates'.

4 tables of those entities that the author of the question means - applications, goals, objectives and methods. In each of them, in addition to its information fields, there must be a unique identifier field - a key (for example: id int identity) 3 other tables are tables — links, each has only two fields — links to id target tables. Those. the table “Orders_objectives” with the fields: 'id_indications' and 'id_objectives' (conditional names), similarly to the other two tables - 'targets_tasks' and 'task_methods'.

Well, then, for example, to fetch information along the entire chain from the application to the methods, inclusively, you will need to form a request from the from clause of the [application] and 6 consecutive join with the rest of the tables of this structure

  • Inserted a picture into the question. So it should be? - MaximK
  • Yes, that is what was meant. And it is not necessary to have different ID ranges in tables of different entities. They are independent and can (and will) intersect. This will not interfere - ale
  • Well and formally - all links arrows - should be sent in this case FROM the link tables - ale
  • Good. I will try to implement - MaximK