Database Schema Modification

 in Social Enterprise by

I suggested to ChatGPT that we add the 17 UN SDGs to the database and it agreed with me.

Key Modifications
Add an SDGs Table – This will store the 17 SDGs.

Link Social Issues to SDGs – Since one issue can relate to multiple SDGs, we need a many-to-many relationship table.

Link Organizations & Initiatives to SDGs – This allows organizations and initiatives to explicitly state which SDGs they contribute to. 

-- SDGs Table (Stores the 17 UN Sustainable Development Goals)
CREATE TABLE SDGs (
    id INTEGER PRIMARY KEY,  -- 1 to 17
    name TEXT UNIQUE NOT NULL,
    description TEXT
);

-- Many-to-Many: Link Social Issues to SDGs
CREATE TABLE Social_Issue_SDGs (
    id INTEGER PRIMARY KEY,
    issue_id INT NOT NULL,
    sdg_id INT NOT NULL,
    FOREIGN KEY (issue_id) REFERENCES Social_Issues(id) ON DELETE CASCADE,
    FOREIGN KEY (sdg_id) REFERENCES SDGs(id) ON DELETE CASCADE,
    UNIQUE(issue_id, sdg_id)  -- Prevents duplicate links
);

-- Many-to-Many: Link Organizations to SDGs
CREATE TABLE Organization_SDGs (
    id INTEGER PRIMARY KEY,
    organization_id INT NOT NULL,
    sdg_id INT NOT NULL,
    FOREIGN KEY (organization_id) REFERENCES Organizations(id) ON DELETE CASCADE,
    FOREIGN KEY (sdg_id) REFERENCES SDGs(id) ON DELETE CASCADE,
    UNIQUE(organization_id, sdg_id)
);

-- Many-to-Many: Link Initiatives to SDGs
CREATE TABLE Initiative_SDGs (
    id INTEGER PRIMARY KEY,
    initiative_id INT NOT NULL,
    sdg_id INT NOT NULL,
    FOREIGN KEY (initiative_id) REFERENCES Initiatives(id) ON DELETE CASCADE,
    FOREIGN KEY (sdg_id) REFERENCES SDGs(id) ON DELETE CASCADE,
    UNIQUE(initiative_id, sdg_id)
);


INSERT INTO SDGs (id, name, description) VALUES
(1, 'No Poverty', 'End poverty in all its forms everywhere'),
(2, 'Zero Hunger', 'End hunger, achieve food security and improved nutrition, and promote sustainable agriculture'),
(3, 'Good Health and Well-Being', 'Ensure healthy lives and promote well-being for all at all ages'),
(4, 'Quality Education', 'Ensure inclusive and equitable quality education and promote lifelong learning opportunities for all'),
(5, 'Gender Equality', 'Achieve gender equality and empower all women and girls'),
(6, 'Clean Water and Sanitation', 'Ensure availability and sustainable management of water and sanitation for all'),
(7, 'Affordable and Clean Energy', 'Ensure access to affordable, reliable, sustainable, and modern energy for all'),
(8, 'Decent Work and Economic Growth', 'Promote sustained, inclusive, and sustainable economic growth, full and productive employment, and decent work for all'),
(9, 'Industry, Innovation, and Infrastructure', 'Build resilient infrastructure, promote inclusive and sustainable industrialization, and foster innovation'),
(10, 'Reduced Inequalities', 'Reduce inequality within and among countries'),
(11, 'Sustainable Cities and Communities', 'Make cities and human settlements inclusive, safe, resilient, and sustainable'),
(12, 'Responsible Consumption and Production', 'Ensure sustainable consumption and production patterns'),
(13, 'Climate Action', 'Take urgent action to combat climate change and its impacts'),
(14, 'Life Below Water', 'Conserve and sustainably use the oceans, seas, and marine resources for sustainable development'),
(15, 'Life on Land', 'Protect, restore, and promote sustainable use of terrestrial ecosystems, sustainably manage forests, combat desertification, and halt and reverse land degradation and halt biodiversity loss'),
(16, 'Peace, Justice, and Strong Institutions', 'Promote peaceful and inclusive societies for sustainable development, provide access to justice for all, and build effective, accountable, and inclusive institutions at all levels'),
(17, 'Partnerships for the Goals', 'Strengthen the means of implementation and revitalize the global partnership for sustainable development');

(Example: Finding social issues related to “No Poverty” (SDG 1)) 

SELECT si.id, si.name, si.description
FROM Social_Issues si
JOIN Social_Issue_SDGs sis ON si.id = sis.issue_id
JOIN SDGs s ON sis.sdg_id = s.id
WHERE s.name = 'No Poverty';

(Example: Which organizations are focused on “Quality Education” (SDG 4)?) 

SELECT o.id, o.name, o.description, o.website, o.contact_email
FROM Organizations o
JOIN Organization_SDGs os ON o.id = os.organization_id
JOIN SDGs s ON os.sdg_id = s.id
WHERE s.name = 'Quality Education';

Find initiatives that support multiple SDGs
(Example: Initiatives addressing both “Clean Water and Sanitation” (SDG 6) and “Climate Action” (SDG 13)) 

SELECT i.id, i.name, i.description, o.name AS organization
FROM Initiatives i
JOIN Initiative_SDGs isd ON i.id = isd.initiative_id
JOIN SDGs s ON isd.sdg_id = s.id
JOIN Organizations o ON i.organization_id = o.id
WHERE s.name IN ('Clean Water and Sanitation', 'Climate Action')
GROUP BY i.id, i.name, i.description, o.name
HAVING COUNT(DISTINCT s.id) = 2;  -- Ensures the initiative is linked to both SDGs

Leave a Reply