are you looking for something that creates an observation table and also a separate annotations table? for example:
observation
| id | obs date | sub date | observer | taxon | … |
|---|---|---|---|---|---|
| 1 | 2022-05-01 | 2022-05-02 | gall_lover | 430050 | … |
| 2 | 2022-05-02 | 2022-05-02 | jiro | 430050 | … |
| 3 | 2022-05-03 | 2022-05-04 | gall_gal | 430050 | … |
annotation
| id | obs id | term id | term value |
|---|---|---|---|
| 1 | 2 | 1 | 2 |
| 2 | 2 | 9 | 10 |
| 3 | 3 | 1 | 7 |
… or do you just want to join and flatten the records (which could produce a little duplication in the observation records if one is tied to multiple annotation records)? for example:
results
| id | obs date | sub date | observer | taxon | … | annotation id | term id | term value |
|---|---|---|---|---|---|---|---|---|
| 1 | 2022-05-01 | 2022-05-02 | gall_lover | 430050 | … | null | null | null |
| 2 | 2022-05-02 | 2022-05-02 | jiro | 430050 | … | 1 | 1 | 2 |
| 2 | 2022-05-02 | 2022-05-02 | jiro | 430050 | … | 2 | 9 | 10 |
| 3 | 2022-05-03 | 2022-05-04 | gall_gal | 430050 | … | 3 | 1 | 7 |
… or are you wanting to make something more like a crosstab? for example:
observation
|id|obs date|sub date|observer|taxon|…|value for term_id=1|value for term_id=9|
|—|—|—|—|—|—|—|—|—|
|1|2022-05-01|2022-05-02|gall_lover|430050|…|null|null|
|2|2022-05-02|2022-05-02|jiro|430050|…|2|10|
|3|2022-05-03|2022-05-04|gall_gal|430050|…|1|null|