Database Properties
Database Type: PostgreSQL - 14.12 (Postgres.app)
Schema public
standard public schema
Tables
| Table / View | Children | Parents | Columns | Rows | Type | Comments |
|---|---|---|---|---|---|---|
| fips_stages | 0 | 0 | 4 | 1365 | Table | Records the date at which a FIP entered a particular stage for the first time. |
| fips_srs | 0 | 3 | 3 | 798 | Table | Records the association between FIPs and SRs. As this can change over time, records are added each db_version_year. |
| countries | 8 | 0 | 5 | 249 | Table | |
| gfps | 3 | 3 | 17 | 1233 | Table | Information about each GearFlagProfile (gfp) or fishery associated with FIPs |
| fips | 8 | 0 | 12 | 1931 | Table | |
| fips_gfps | 0 | 3 | 3 | 7687 | Table | Records the association between FIPs and GFPs. As this can change over time, records are added each db_version_year. |
| species | 1 | 0 | 11 | 223 | Table | |
| Table1 | 0 | 0 | 1 | 0 | Table | |
| gears | 11 | 0 | 5 | 50 | Table | |
| mscs | 0 | 0 | 6 | 735 | Table | |
| gfps_mscs | 0 | 1 | 3 | 2254 | Table | |
| fip_participants | 0 | 3 | 4 | 11175 | Table | Records the association between FIPs and Organizations. As this can change over time, records are added each db_version_year. |
| srs | 1 | 0 | 4 | 23 | Table | |
| organizations | 2 | 0 | 4 | 1487 | Table | |
| fips_stats | 0 | 0 | 7 | 2189 | Table | Records the statistics for each FIP at the end of the current year. |
| jurisdictions | 0 | 3 | 3 | 2417 | Table | |
| fs_retrospective_scores | 0 | 1 | 7 | 89414 | Table | Reports the latest retrospective time series of FishSource scores for each GFP |
| undp_data | 0 | 1 | 3 | 0 | Table | |
| avg_score_over_time_db_2024 | 0 | 0 | 7 | 0 | View | This view requires the scores_crosstab materialized view and averages the retropspective FS scores associated with all GFPs associated with each FIP over time. DB Version 2024. |
| scores_crosstab | 0 | 0 | 8 | 0 | View | This materialized view does a pivot on the public.fs_retrospective_scores table to yield a row for each GFP and YEAR with the FS scores 1-5 in columns. DD and NS scores (less than zero) are not included. |
| fip_sectors_db_2020 | 0 | 0 | 2 | 0 | View | This view shows the T75 sectors associated with each GFP and FIP. DB Version 2020. |
| months_to_stage3_db_2022 | 0 | 0 | 5 | 0 | View | This view shows the months from start of FIP to the first Stage 3 indicator being completed. DB Version 2022. |
| months_to_stage3_db_2021 | 0 | 0 | 5 | 0 | View | This view shows the months from start of FIP to the first Stage 3 indicator being completed. DB Version 2021. |
| fip_sectors_db_2022 | 0 | 0 | 2 | 0 | View | This view shows the T75 sectors associated with each GFP and FIP. DB Version 2022. |
| months_to_stage3_db_2024 | 0 | 0 | 5 | 0 | View | This view shows the months from start of FIP to the first Stage 3 indicator being completed. DB Version 2024. |
| fip_sectors_db_2021 | 0 | 0 | 2 | 0 | View | This view shows the T75 sectors associated with each GFP and FIP. DB Version 2021. |
| months_to_stage3_db_2023 | 0 | 0 | 5 | 0 | View | This view shows the months from start of FIP to the first Stage 3 indicator being completed. DB Version 2023. |
| fip_sectors_db_2024 | 0 | 0 | 2 | 0 | View | This view shows the T75 sectors associated with each GFP and FIP. DB Version 2024. |
| fip_sectors_db_2023 | 0 | 0 | 2 | 0 | View | This view shows the T75 sectors associated with each GFP and FIP. DB Version 2023. |
| last_year_fips | 0 | 0 | 12 | 0 | View | |
| months_to_stage3_db_2020 | 0 | 0 | 5 | 0 | View | This view shows the months from start of FIP to the first Stage 3 indicator being completed. DB Version 2020. |
| max_fip_stage_db_2020 | 0 | 0 | 4 | 0 | View | This view shows the max stage per FIP. DB Version 2020. |
| remote_fs_countries | 0 | 1 | 2 | 0 | View | |
| avg_score_over_time_db_2021 | 0 | 0 | 7 | 0 | View | This view requires the scores_crosstab materialized view and averages the retropspective FS scores associated with all GFPs associated with each FIP over time. DB Version 2021. |
| avg_score_over_time_db_2022 | 0 | 0 | 7 | 0 | View | This view requires the scores_crosstab materialized view and averages the retropspective FS scores associated with all GFPs associated with each FIP over time. DB Version 2022. |
| avg_score_over_time_db_2023 | 0 | 0 | 7 | 0 | View | This view requires the scores_crosstab materialized view and averages the retropspective FS scores associated with all GFPs associated with each FIP over time. DB Version 2023. |
| fip_db_view_2020 | 0 | 3 | 64 | 0 | View | |
| fip_lead_types_db_2021 | 0 | 0 | 3 | 0 | View | This view shows the lead organizations and type (NGO, Governmental or Other). DB Version 2021. |
| fip_lead_types_db_2020 | 0 | 0 | 3 | 0 | View | This view shows the lead organizations and type (NGO, Governmental or Other). DB Version 2020. |
| fip_db_view_2023 | 0 | 3 | 64 | 0 | View | |
| fip_lead_types_db_2023 | 0 | 0 | 3 | 0 | View | This view shows the lead organizations and type (NGO, Governmental or Other). DB Version 2023. |
| max_fip_stage_db_2021 | 0 | 0 | 4 | 0 | View | This view shows the max stage per FIP. DB Version 2021. |
| fip_db_view_2024 | 0 | 3 | 64 | 0 | View | |
| fip_lead_types_db_2022 | 0 | 0 | 3 | 0 | View | This view shows the lead organizations and type (NGO, Governmental or Other). DB Version 2022. |
| max_fip_stage_db_2022 | 0 | 0 | 4 | 0 | View | This view shows the max stage per FIP. DB Version 2022. |
| fip_db_view_2021 | 0 | 3 | 64 | 0 | View | |
| max_fip_stage_db_2023 | 0 | 0 | 4 | 0 | View | This view shows the max stage per FIP. DB Version 2023. |
| fip_db_view_2022 | 0 | 3 | 64 | 0 | View | |
| fip_lead_types_db_2024 | 0 | 0 | 3 | 0 | View | This view shows the lead organizations and type (NGO, Governmental or Other). DB Version 2024. |
| max_fip_stage_db_2024 | 0 | 0 | 4 | 0 | View | This view shows the max stage per FIP. DB Version 2024. |