Database Properties
Database Type: PostgreSQL - 14.11 (Postgres.app)
Schema public
standard public schema
Tables
Table / View | Children | Parents | Columns | Rows | Type | Comments |
---|---|---|---|---|---|---|
fips_stages | 0 | 0 | 4 | 1285 | Table | Records the date at which a FIP entered a particular stage for the first time. |
fips_srs | 0 | 3 | 3 | 700 | Table | Records the association between FIPs and SRs. As this can change over time, records are added each db_version_year. |
countries | 6 | 0 | 5 | 249 | Table | |
gfps | 3 | 3 | 16 | 1188 | Table | Information about each GearFlagProfile (gfp) or fishery associated with FIPs |
fips | 8 | 0 | 12 | 1590 | Table | |
fips_gfps | 0 | 3 | 3 | 6162 | 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 | 212 | Table | |
Table1 | 0 | 0 | 1 | 0 | Table | |
gears | 9 | 0 | 5 | 49 | Table | |
mscs | 0 | 0 | 6 | 581 | Table | |
gfps_mscs | 0 | 1 | 3 | 1678 | Table | |
fip_participants | 0 | 3 | 4 | 8835 | 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 | 22 | Table | |
organizations | 2 | 0 | 4 | 1217 | Table | |
fips_stats | 0 | 0 | 7 | 1848 | Table | Records the statistics for each FIP at the end of the current year. |
jurisdictions | 0 | 3 | 3 | 1957 | Table | |
fs_retrospective_scores | 0 | 1 | 7 | 72476 | Table | Reports the latest retrospective time series of FishSource scores for each GFP |
undp_data | 0 | 1 | 3 | 0 | Table | |
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. |
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_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. |
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_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 |