SE Database means Social Enterprise Database. Let’s put the previous two posts together here and show the SQL script that creates the database tables and puts some data into the tables. Our next task will be to add more data and check that the connections are making sense. We’ll need to add more connections as well. Thew following SQL code is compatible with SQLite. ChatGPT however recommended that we use Postgrsql as a database for the final product.

CREATE TABLE Social_Issues (
    id INTEGER PRIMARY KEY,
    name TEXT UNIQUE NOT NULL,
    description TEXT
);

CREATE TABLE Organizations (
    id INTEGER PRIMARY KEY,
    name TEXT UNIQUE NOT NULL,
    description TEXT,
    website TEXT,
    contact_email VARCHAR(255)
);

CREATE TABLE Initiatives (
    id INTEGER PRIMARY KEY,
    name TEXT UNIQUE NOT NULL,
    description TEXT,
    organization_id INT,
    FOREIGN KEY (organization_id) REFERENCES Organizations(id) ON DELETE SET NULL
);

CREATE TABLE Resources (
    id INTEGER PRIMARY KEY,
    name TEXT UNIQUE NOT NULL,
    description TEXT,
    type VARCHAR(100) CHECK (type IN ('Funding', 'Data', 'Expertise', 'Tool', 'Policy')),
    provider_id INT,  -- Can be linked to an Organization
    FOREIGN KEY (provider_id) REFERENCES Organizations(id) ON DELETE SET NULL
);
-- 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)
);

-- Connecting Issues to Organizations, Initiatives, and Other Issues
CREATE TABLE Connections (
    id INTEGER PRIMARY KEY,
    issue_id INT,
    related_issue_id INT,  -- Self-referencing for issue-issue relationships
    initiative_id INT,
    organization_id INT,
    connection_type VARCHAR(100) CHECK (connection_type IN ('Causes', 'Affects', 'Supports', 'Depends On')),
    FOREIGN KEY (issue_id) REFERENCES Social_Issues(id) ON DELETE CASCADE,
    FOREIGN KEY (related_issue_id) REFERENCES Social_Issues(id) ON DELETE CASCADE,
    FOREIGN KEY (initiative_id) REFERENCES Initiatives(id) ON DELETE CASCADE,
    FOREIGN KEY (organization_id) REFERENCES Organizations(id) ON DELETE CASCADE
);