Database Schema Overview

Entity Relationship Diagrams

Main Tables

These tables hold the data for primary records of interest.

resource

Description

This table stores resources related to national reporting of UN Sustainable Development Goals.

Fields

• PRIMARY KEY (uuid)
• uuid : UUID DEFAULT uuid_generate_v1mc()
• content_type_id : INTEGER : REFERENCES content_type (id)
• country_id : CHARACTER(3) : REFERENCES country (iso_alpha3)
• title : TEXT NOT NULL
• organization : TEXT
• url : TEXT NOT NULL
• date_published : TIMESTAMPTZ
• image_url : TEXT
• description : TEXT
• tags : TEXT[]
• notes : TEXT
• publish : BOOLEAN DEFAULT FALSE
• tsv : TSVECTOR
• created_at : TIMESTAMPTZ NOT NULL DEFAULT now()
• updated_at : TIMESTAMPTZ NOT NULL DEFAULT now()

Notes

• Currently, only the month and year are used from the date_published field. Although it's best to maintain accuracy if possible, the specific day and time can be set to any values.
• The tags field should automatically be kept updated by a trigger function. Please edit any associated tags through the resource_tags associative table rather than this field.

submission

Description

This table stores records of resource submissions from the website.

Fields

• PRIMARY KEY (uuid)
• uuid : UUID DEFAULT uuid_generate_v1mc()
• resource_id : UUID : REFERENCES resource (uuid)
• status_id : INTEGER : REFERENCES submission_status (id)
• submitter_country_id : CHARACTER(3) : REFERENCES country (iso_alpha3)
• submitter_name : TEXT
• submitter_organization : TEXT
• submitter_title : TEXT
• submitter_email : TEXT
• submitter_city : TEXT
• tags : TEXT[]
• notes : TEXT
• created_at : TIMESTAMPTZ NOT NULL DEFAULT now()
• updated_at : TIMESTAMPTZ NOT NULL DEFAULT now()

Notes

news

Description

This table stores relevant news items related to national reporting of UN Sustainable Development Goals.

Fields

• PRIMARY KEY (uuid)
• uuid : UUID : DEFAULT uuid_generate_v1mc()
• title : TEXT NOT NULL
• organization : TEXT
• url : TEXT
• description : TEXT
• tags : TEXT[]
• published_at : TIMESTAMPTZ NOT NULL DEFAULT now()
• publish : BOOLEAN NOT NULL DEFAULT FALSE
• created_at : TIMESTAMPTZ NOT NULL DEFAULT now()
• updated_at : TIMESTAMPTZ NOT NULL DEFAULT now()

Notes

events

Description

This table stores relevant events occurring related to national reporting of UN Sustainable Development Goals.

Fields

• PRIMARY KEY (uuid)
• uuid : UUID DEFAULT uuid_generate_v1mc()
• title : TEXT NOT NULL
• url : TEXT
• description : TEXT
• start_time : TIMESTAMPTZ NOT NULL
• end_time : TIMESTAMPTZ
• locations : TEXT[]
• tags : TEXT[]
• publish : BOOLEAN NOT NULL DEFAULT FALSE
• created_at : TIMESTAMPTZ NOT NULL DEFAULT now()
• updated_at : TIMESTAMPTZ NOT NULL DEFAULT now()

Notes

Metadata Tables

These tables hold supporting metadata for the main records we're interested in.

content_type

Description

This table stores the different resource types that resources can be associated with.

Fields

• PRIMARY KEY (id)
• id : SERIAL
• name : TEXT NOT NULL

country

Description

This table stores the different countries that resources can be associated with. This is currently the list of UN member countries.

Fields

• PRIMARY KEY (iso_alpha3)
• iso_alpha3 : CHARACTER(3)
• region_id : CHARACTER(3) : REFERENCES region (m49)
• income_group : TEXT
• name : TEXT NOT NULL

language

Description

This table stores the available languages that resources can be associated with.

Fields

• PRIMARY KEY (ietf_tag)
• ietf_tag : TEXT
• name : TEXT NOT NULL
• label : TEXT NOT NULL

location

Description

This table stores the locations that an event can be associated with.

Fields

• PRIMARY KEY (uuid)
• uuid : UUID DEFAULT uuid_generate_v1mc(),
• name : TEXT NOT NULL UNIQUE

submission_status

Description

This table stores the possible statuses that a submission can take (i.e. Unreviewed, Under review, Accepted).

Fields

• PRIMARY KEY (id)
• id : SERIAL
• status : TEXT NOT NULL

tag

Description

This table stores the different tags that resources can be associated with.

Fields

• PRIMARY KEY (uuid)
• uuid : UUID DEFAULT uuid_generate_v1mc(),
• name : TEXT NOT NULL UNIQUE

topic

Description

This table stores different topic categories that resources can belong to.

Fields

• PRIMARY KEY (id)
• id : SERIAL
• topic : TEXT UNIQUE NOT NULL
• path : LTREE UNIQUE NOT NULL
• label : TEXT NOT NULL
• ordering : LTREE UNIQUE NOT NULL

region

Description

This table stores the different regions that countries can belong to, following the United Nations geoscheme.

Fields

• PRIMARY KEY (m49)
• m49 : CHARACTER(3)
• path : LTREE UNIQUE NOT NULL
• name : TEXT NOT NULL

Associative Tables

These tables are used to manage the many-to-many relationships between other tables.

event_locations

Fields

• PRIMARY KEY (event_id, location_id)
• event_id : UUID : REFERENCES event (uuid)
• location_id : UUID : REFERENCES location (uuid)

resource_content_types

Fields

• PRIMARY KEY (resource_id, content_type_id)
• resource_id : UUID : REFERENCES resource (uuid)
• content_type_id : INTEGER : REFERENCES content_type (id)

resource_countries

Fields

• PRIMARY KEY (resource_id, country_id)
• resource_id : UUID : REFERENCES resource (uuid)
• country_id : CHARACTER(3) : REFERENCES country (iso_alpha3)

resource_languages

Fields

• PRIMARY KEY (resource_id, language_id)
• resource_id : UUID : REFERENCES resource (uuid)
• language_id : TEXT : REFERENCES language (ietf_tag)

resource_tags

Fields

• PRIMARY KEY (resource_id, tag_id)
• resource_id : UUID : REFERENCES resource (uuid)
• tag_id : UUID : REFERENCES tag (uuid)

resource_topics

Fields

• PRIMARY KEY (topic_id, resource_id)
• resource_id : UUID : REFERENCES resource (uuid)
• topic_id : INTEGER : REFERENCES topic (id)