Connection between "legacy" GO entities and DREF tables

[szabozoltan69]

There is a need to connect (and mutually use) the tables of non-DREF GO entites (fieldreports, emergencies, appeals, lessons learned, analytics) and the 3 phase of DREF-s (DREF, Ops Update, Final report). (Diagram from @anamariaescobar, thanks) We should make a mapping with the corresponding data. Maybe a switch table (containing Appeal code (MDR...)) and some other id-s could be a starting point. Or: we could put the dref_id to the Appeal table (where Appeal code is from the beginning), then we can find the belonging dref-*-records. This mapping could/should be used to pre-populate certain fields to help form fill-in, and probably to make more precise analytics.

@frozenhelium @thenav56 @batpad @anamariaescobar @ypyelab @tovari – Any help is highly appreciated.

[szabozoltan69]

The dref's id can be seen as "dref_id" in the later tables:

select * from dref_dref where id=159; (1 record)
select * from dref_drefoperationalupdate where dref_id=159; (can be more records)
select * from dref_dreffinalreport where dref_id=159; (1 record)

These belong together. We also can see that in the early phase there is still not Appeal Code for every DREF-s:

select appeal_code, id from dref_dref order by 2;
select appeal_code, dref_id from dref_drefoperationalupdate order by 2;
select appeal_code, dref_id from dref_dreffinalreport order by 2;

[szabozoltan69]

The "dref_dref" should be the central table in this feature, which has (or can have) appeal_code and dref.id as well. Maybe a data table "view" (which collects the needed information from all related tables) or a simple multitable api endpoint could also be a good solution to see in one place all the needed entities.