Script for SQLite

 in Social Enterprise  tagged database / social / sql / enterprise / collaboration / sqlite / portal / hub / Database of Social Enterprises by
This entry is part 5 of 5 in the series Database of Social Enterprises

Database of Social Enterprises

Let’s pull together some of the SQL scripts ChatGPT has written in previous posts and list them here. This way we can create a database in SQLite and then run these scripts to quickly create a prototype database of social enterprise.

Create Table

Here are our create table statements for SQLite. 

CREATE TABLE Social_Issues (
    id INTEGER PRIMARY KEY,
    name TEXT UNIQUE NOT NULL,
    description TEXT
);
CREATE TABLE Categories (
    id INTEGER PRIMARY KEY,
    name VARCHAR(255) UNIQUE NOT NULL,
    description TEXT
);
-- linking table to link Social_Issues to Categories for a many-to-many relationship
CREATE TABLE Social_Issue_Categories (
    issue_id INTEGER,
    category_id INTEGER,
    PRIMARY KEY (issue_id, category_id),
    FOREIGN KEY (issue_id) REFERENCES Social_Issues(id),
    FOREIGN KEY (category_id) REFERENCES Categories(id)
);
CREATE TABLE Organizations (
    id INTEGER PRIMARY KEY,
    name TEXT UNIQUE NOT NULL,
    description TEXT,
    website TEXT,
    country TEXT,
    province TEXT,
    city 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
);

-- 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
);

Insert Into

We need sample data to test our database design and run some queries against it. 

INSERT INTO Categories (id, name, description) VALUES 
    (1, 'Justice & Human Rights', 'Issues related to legal access, equality, and fundamental rights.'),
    (2, 'Poverty & Economic Inclusion', 'Challenges related to financial security, employment, and equitable economic participation.'),
    (3, 'Health & Well-being', 'Access to healthcare, mental health support, and addiction recovery.'),
    (4, 'Education & Opportunity', 'Ensuring equal access to learning and skills development.'),
    (5, 'Environment & Sustainability', 'Addressing environmental protection, conservation, and climate change.'),
    (6, 'Community & Civic Engagement', 'Encouraging active participation in community development and disaster relief.');
-- Insert Social Issues
INSERT INTO Social_Issues (name, description) VALUES 
    ('Affordable Housing', 'Ensuring people have access to safe, affordable living spaces.'),
    ('Employment', 'Providing job opportunities and workforce development.'),
    ('Food Security', 'Ensuring access to nutritious food for all.'),
    ('Water Access', 'Providing reliable clean water supply.'),
    ('Sanitation', 'Improving hygiene and waste management to prevent disease.'),
    ('Environmental Sustainability', 'Promoting eco-friendly practices and conservation efforts.'),
    ('Healthcare Access', 'Ensuring people have access to essential medical services.'),
    ('Education Equity', 'Providing equal learning opportunities for all individuals.'),
    ('Mental Health Support', 'Addressing psychological well-being and access to mental health services.'),
    ('Financial Inclusion', 'Ensuring access to financial services for underserved populations.'),
    ('Digital Divide', 'Bridging gaps in technology access and digital literacy.'),
    ('Gender Equality', 'Promoting equal rights and opportunities regardless of gender.'),
    ('Indigenous Rights', 'Advancing the rights and well-being of Indigenous communities.'),
    ('Climate Change Adaptation', 'Helping communities adjust to environmental changes.'),
    ('Youth Empowerment', 'Supporting young people with education, training, and leadership opportunities.'),
    ('Disability Inclusion', 'Ensuring accessibility and equal opportunities for people with disabilities.'),
    ('Refugee Support', 'Providing assistance and resources for displaced populations.'),
    ('Civic Engagement', 'Encouraging participation in social and political processes.'),
    ('Employment Equity', 'Promoting fair and inclusive work opportunities.'),
    ('Senior Support', 'Addressing the needs and well-being of elderly populations.'),
    ('Prisoner Reintegration', 'Helping formerly incarcerated individuals transition back into society.'),
    ('Human Trafficking Prevention', 'Combating forced labor and exploitation.'),
    ('LGBTQ+ Rights', 'Advocating for equal rights and protections for LGBTQ+ individuals.'),
    ('Addiction Recovery', 'Providing support for individuals struggling with substance abuse.'),
    ('Disaster Relief', 'Providing emergency aid and long-term recovery support for affected communities.'),
    ('Rural Development', 'Improving infrastructure and opportunities in rural areas.'),
    ('Urban Poverty', 'Addressing social and economic challenges in low-income urban areas.'),
    ('Fair Trade & Ethical Sourcing', 'Promoting fair wages and responsible supply chains.'),
    ('Child Protection', 'Preventing abuse, exploitation, and neglect of children.'),
    ('Access to Justice', 'Ensuring legal representation and rights protection for marginalized groups.');
-- insert into the linking table
INSERT INTO Social_Issue_Categories (issue_id, category_id) VALUES 
    -- Justice & Human Rights
    (1, 1), -- Access to Justice
    (2, 1), -- Human Trafficking Prevention
    (3, 1), -- LGBTQ+ Rights
    (4, 1), -- Employment Equity
    (5, 1), -- Gender Equality
    (6, 1), -- Indigenous Rights
    (7, 1), -- Disability Inclusion
    -- Poverty & Economic Inclusion
    (8, 2), -- Affordable Housing
    (9, 2), -- Food Security
    (10, 2), -- Financial Inclusion
    (11, 2), -- Urban Poverty
    (12, 2), -- Rural Development
    (13, 2), -- Fair Trade & Ethical Sourcing
    -- Health & Well-being
    (14, 3), -- Healthcare Access
    (15, 3), -- Mental Health Support
    (16, 3), -- Addiction Recovery
    (17, 3), -- Senior Support
    (7, 3), -- Disability Inclusion (also in Justice & Human Rights)
    -- Education & Opportunity
    (18, 4), -- Education Equity
    (19, 4), -- Digital Divide
    (20, 4), -- Youth Empowerment
    (21, 4), -- Prisoner Reintegration
    -- Environment & Sustainability
    (22, 5), -- Climate Change Adaptation
    (23, 5), -- Environmental Sustainability
    (24, 5), -- Water Access
    (25, 5), -- Sanitation
    -- Community & Civic Engagement
    (26, 6), -- Refugee Support
    (27, 6), -- Civic Engagement
    (28, 6), -- Child Protection
    (29, 6); -- Disaster Relief

Here below are some more insert into statements. 

-- Insert Ontario-based Organizations
INSERT INTO Organizations (id, name, description, website, country, province, city, contact_email) VALUES
    (1, 'Daily Bread Food Bank', 'A Toronto-based food bank dedicated to eliminating food insecurity and advocating for solutions to end poverty.', 'https://www.dailybread.ca/', 'Canada', 'Ontario', 'Toronto', 'info@dailybread.ca'),
    (2, 'Second Harvest', 'Canada''s largest food rescue charity, operating nationwide to reduce food waste and feed communities.', 'https://www.secondharvest.ca/', 'Canada', 'Ontario', 'Toronto', 'info@secondharvest.ca'),
    (3, 'Centre for Addiction and Mental Health (CAMH)', 'Canada''s largest mental health teaching hospital and one of the world''s leading research centers in its field.', 'https://www.camh.ca/', 'Canada', 'Ontario', 'Toronto', 'info@camh.ca'),
    (4, 'Water First', 'An organization dedicated to addressing water challenges in Indigenous communities through education, training, and meaningful collaboration.', 'https://waterfirst.ngo/', 'Canada', 'Ontario', 'Creemore', 'info@waterfirst.ngo'),
    (5, 'Habitat for Humanity GTA', 'A nonprofit organization helping families in the Greater Toronto Area build and improve places to call home.', 'https://habitatgta.ca/', 'Canada', 'Ontario', 'Toronto', 'info@habitatgta.ca'),
    (6, 'Environmental Defence Canada', 'A leading Canadian environmental advocacy organization working to defend clean water, a safe climate, and healthy communities.', 'https://environmentaldefence.ca/', 'Canada', 'Ontario', 'Toronto', 'info@environmentaldefence.ca'),
    (7, 'The 519', 'A Toronto-based organization dedicated to the health, happiness, and full participation of the LGBTQ2S communities.', 'https://www.the519.org/', 'Canada', 'Ontario', 'Toronto', 'info@the519.org'),
    (8, 'Community Living Ontario', 'A non-profit provincial association advocating for people with intellectual disabilities and their families for over 70 years.', 'https://communitylivingontario.ca/', 'Canada', 'Ontario', 'Toronto', 'info@communitylivingontario.ca'),
    (9, 'United Way Greater Toronto', 'A charity dedicated to fighting local poverty and building strong communities across the Greater Toronto Area.', 'https://www.unitedwaygt.org/', 'Canada', 'Ontario', 'Toronto', 'info@unitedwaygt.org'),
    (10, 'WoodGreen Community Services', 'One of Toronto''s largest social service agencies, offering a wide range of programs to build sustainable communities.', 'https://www.woodgreen.org/', 'Canada', 'Ontario', 'Toronto', 'info@woodgreen.org'),
-- Insert Ontario-based Organizations (IDs 11 to 20)
    (11, 'ACCES Employment', 'A leader in connecting employers with qualified employees from diverse backgrounds, serving over 32,000 job seekers annually across the Greater Toronto Area.', 'http://accesemployment.ca/', 'Canada', 'Ontario', 'Toronto', 'info@accesemployment.ca'),
    (12, 'Access Community Capital Fund', 'A Canadian Registered Charity formed in 1999 to help individuals facing barriers to traditional financing obtain microloans to improve their income through sustainable self-employment and employment.', 'http://accessccf.com/', 'Canada', 'Ontario', 'Toronto', 'info@accessccf.com'),
    (13, 'Afghan Women''s Organization', 'Provides settlement services to all newcomers, with a special focus on women, their families, refugees, and people who have experienced war and persecution.', 'http://afghanwomen.org/', 'Canada', 'Ontario', 'Toronto', 'info@afghanwomen.org'),
    (14, 'Ontario Nonprofit Network', 'The independent network for Ontario''s 58,000 nonprofit organizations, engaging with members to address issues and ensure community voices reach stakeholders.', 'https://theonn.ca/', 'Canada', 'Ontario', 'Toronto', 'info@theonn.ca'),
    (15, 'Social Planning Toronto', 'A nonprofit community organization that challenges inequity in Toronto through knowledge generation, debate, civic engagement, and advocacy for social change.', 'https://www.socialplanningtoronto.org/', 'Canada', 'Ontario', 'Toronto', 'info@socialplanningtoronto.org'),
    (16, 'Community Development Halton', 'A nonprofit organization dedicated to enhancing the quality of life in Halton Region through community education, social planning, and advocacy.', 'https://cdhalton.ca/', 'Canada', 'Ontario', 'Burlington', 'info@cdhalton.ca'),
    (17, 'Kin Canada', 'A national non-profit service organization promoting service, fellowship, positive values, and national pride, with clubs located throughout Canada.', 'http://www.kincanada.ca/', 'Canada', 'Ontario', 'Cambridge', 'info@kincanada.ca'),
    (18, 'Goodwill, The Amity Group', 'A nonprofit social enterprise providing work opportunities and skills development for people facing barriers to employment in the Hamilton and Halton regions.', 'https://www.goodwillonline.ca/', 'Canada', 'Ontario', 'Hamilton', 'info@goodwillonline.ca'),
    (19, 'Anishnawbe Health Toronto', 'A fully accredited community health center providing traditional healing within a multi-disciplinary health care model to Toronto’s Indigenous community.', 'https://aht.ca/', 'Canada', 'Ontario', 'Toronto', 'info@aht.ca'),
    (20, 'Applegrove Community Complex', 'A multi-service community organization in Toronto offering programs and services to promote the well-being of individuals and families.', 'https://www.applegrovecc.ca/', 'Canada', 'Ontario', 'Toronto', 'info@applegrovecc.ca'),
    (21, 'Learning Enrichment Foundation', 'Provides services including child care, employment training, and settlement assistance to newcomers in Toronto.', 'https://lefca.org/', 'Canada', 'Ontario', 'Toronto', 'info@lefca.org'),
    (22, 'Family Service Ontario', 'Supports member agencies in providing mental health, wellness, and social services to individuals and families across Ontario.', 'https://familyserviceontario.org/', 'Canada', 'Ontario', 'Toronto', 'info@familyserviceontario.org'),
    (23, 'Ontario Association of Social Workers', 'Advocates for social workers and promotes the profession’s contributions to social justice and well-being in Ontario.', 'https://www.oasw.org/', 'Canada', 'Ontario', 'Toronto', 'info@oasw.org'),
    (24, 'Ontario Municipal Social Services Association', 'Supports municipal service managers and district social services boards in Ontario through policy work, advocacy, and professional development.', 'https://www.omssa.com/', 'Canada', 'Ontario', 'Toronto', 'info@omssa.com'),
    (25, 'The Table Soup Kitchen Foundation', 'Provides food, clothing, and support services to individuals and families in need in the Muskoka region.', 'https://thetablefoundation.ca/', 'Canada', 'Ontario', 'Huntsville', 'info@thetablefoundation.ca'),
    (26, 'SHORE Centre', 'Offers sexual health resources, education, and medical services to individuals in the Waterloo Region.', 'https://shorecentre.ca/', 'Canada', 'Ontario', 'Kitchener', 'info@shorecentre.ca'),
    (27, 'Mission Services of Hamilton', 'Provides emergency shelter, food, clothing, and rehabilitation programs to those in need in the Hamilton area.', 'https://mission-services.com/', 'Canada', 'Ontario', 'Hamilton', 'info@mission-services.com'),
    (28, 'Camp Kirk', 'Offers recreational programs for children with learning disabilities and attention deficit disorders in Ontario.', 'https://www.campkirk.com/', 'Canada', 'Ontario', 'Kirkfield', 'info@campkirk.com'),
    (29, 'Knights Table', 'Provides food bank services, meals, and support programs to individuals and families in need in the Peel Region.', 'https://knightstable.org/', 'Canada', 'Ontario', 'Brampton', 'info@knightstable.org'),
    (30, 'Eva’s Initiatives for Homeless Youth', 'Provides shelter, transitional housing, and support services to help homeless youth build brighter futures.', 'https://www.evas.ca/', 'Canada', 'Ontario', 'Toronto', 'info@evas.ca'),
    (31, 'Oasis By The Bay Vacation Suites', 'An accessible, apartment-style motel in Wasaga Beach providing inclusive accommodations and promoting tourism in the Georgian Triangle area.', 'https://oasisbythebay.ca/', 'Canada', 'Ontario', 'Wasaga Beach', 'inquiries@oasisbythebay.ca'),
    (32, 'ReUse Centre', 'A Collingwood-based retail outlet accepting donations of quality, pre-owned items, promoting waste diversion and supporting community initiatives.', 'https://reusecollingwood.com/', 'Canada', 'Ontario', 'Collingwood', 'reuse@e3.ca'),
    (33, 'Blue Mountain Village Foundation', 'An organization supporting local charitable initiatives in South Georgian Bay, focusing on closing social gaps within the community.', 'https://bluemountainvillage.ca/foundation', 'Canada', 'Ontario', 'Blue Mountains', 'foundation@bluemountainvillage.ca'),
    (34, '100 Women Who Care South Georgian Triangle', 'A group of women from Collingwood, Creemore, Meaford, Stayner, Thornbury, and Wasaga Beach, collectively supporting local charities through coordinated donations.', NULL, 'Canada', 'Ontario', 'Collingwood', NULL),
    (35, 'Elephant Thoughts', 'A registered educational charity dedicated to supporting positive change through education and opportunity both in Canada and abroad.', 'https://www.elephantthoughts.com/', 'Canada', 'Ontario', 'Collingwood', 'inquiries@elephantthoughts.com'),
    (36, 'E3 Community Services', 'A multi-service organization providing support services to individuals with developmental and intellectual disabilities, focusing on individual strengths, needs, and choices.', 'https://e3.ca/', 'Canada', 'Ontario', 'Collingwood', 'e3@e3.ca'),
    (37, 'Community Builders', 'A non-profit construction-based social enterprise in Simcoe County and Greater Sudbury, offering training and employment opportunities.', 'https://www.communitybuilders.co/', 'Canada', 'Ontario', 'Minesing', 'info@communitybuilders.co'),
    (38, 'Fleurish - Botanical Goodness', 'A Barrie-based social enterprise providing floral arrangements and offering paid transitional employment opportunities.', 'https://www.fleurishbarrie.com/', 'Canada', 'Ontario', 'Barrie', 'info@fleurishbarrie.com'),
    (39, 'The Common Roof', 'A collaborative space in Barrie and Orillia that houses multiple human-service non-profit agencies, fostering community support and shared resources.', 'https://www.thecommonroof.ca/', 'Canada', 'Ontario', 'Barrie', 'info@thecommonroof.ca'),
    (40, 'Camphill Communities Ontario', 'An organization offering residential and day support services for individuals with developmental disabilities, operating social enterprises like market gardens and maple syrup production.', 'https://www.camphill.on.ca/', 'Canada', 'Ontario', 'Angus', 'info@camphill.on.ca'),
    (41, 'Habitat for Humanity Huronia ReStore', 'A Barrie-based retail outlet selling donated new and gently used building materials and home furnishings, supporting Habitat for Humanity’s mission.', 'https://habitatgatewaynorth.com/restore/', 'Canada', 'Ontario', 'Barrie', 'restore@habitatgatewaynorth.com'),
    (42, 'Rise Asset Development', 'Provides microfinancing and mentorship to entrepreneurs living with mental health and addiction challenges across Ontario.', 'https://www.riseassetdevelopment.com/', 'Canada', 'Ontario', 'Toronto', 'info@riseassetdevelopment.com');

Query the Database

ChatGPT provided a few queries for our database. We need to try these and more.

One Script

CREATE TABLE Social_Issues (
    id INTEGER PRIMARY KEY,
    name TEXT UNIQUE NOT NULL,
    description TEXT
);
CREATE TABLE Categories (
    id INTEGER PRIMARY KEY,
    name VARCHAR(255) UNIQUE NOT NULL,
    description TEXT
);
-- linking table to link Social_Issues to Categories for a many-to-many relationship
CREATE TABLE Social_Issue_Categories (
    issue_id INTEGER,
    category_id INTEGER,
    PRIMARY KEY (issue_id, category_id),
    FOREIGN KEY (issue_id) REFERENCES Social_Issues(id),
    FOREIGN KEY (category_id) REFERENCES Categories(id)
);
CREATE TABLE Organizations (
    id INTEGER PRIMARY KEY,
    name TEXT UNIQUE NOT NULL,
    description TEXT,
    website TEXT,
    country TEXT,
    province TEXT,
    city 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
);

-- Connecting Issues to Organizations, Initiatives, and Other Issues
CREATE TABLE Connections (
    id INTEGER PRIMARY KEY,
    issue_id INT,
    related_issue_id INT,
    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
);

INSERT INTO Categories (id, name, description) VALUES 
    (1, 'Justice & Human Rights', 'Issues related to legal access, equality, and fundamental rights.'),
    (2, 'Poverty & Economic Inclusion', 'Challenges related to financial security, employment, and equitable economic participation.'),
    (3, 'Health & Well-being', 'Access to healthcare, mental health support, and addiction recovery.'),
    (4, 'Education & Opportunity', 'Ensuring equal access to learning and skills development.'),
    (5, 'Environment & Sustainability', 'Addressing environmental protection, conservation, and climate change.'),
    (6, 'Community & Civic Engagement', 'Encouraging active participation in community development and disaster relief.');
	

-- Insert Social Issues
INSERT INTO Social_Issues (name, description) VALUES 
    ('Affordable Housing', 'Ensuring people have access to safe, affordable living spaces.'),
    ('Employment', 'Providing job opportunities and workforce development.'),
    ('Food Security', 'Ensuring access to nutritious food for all.'),
    ('Water Access', 'Providing reliable clean water supply.'),
    ('Sanitation', 'Improving hygiene and waste management to prevent disease.'),
    ('Environmental Sustainability', 'Promoting eco-friendly practices and conservation efforts.'),
    ('Healthcare Access', 'Ensuring people have access to essential medical services.'),
    ('Education Equity', 'Providing equal learning opportunities for all individuals.'),
    ('Mental Health Support', 'Addressing psychological well-being and access to mental health services.'),
    ('Financial Inclusion', 'Ensuring access to financial services for underserved populations.'),
    ('Digital Divide', 'Bridging gaps in technology access and digital literacy.'),
    ('Gender Equality', 'Promoting equal rights and opportunities regardless of gender.'),
    ('Indigenous Rights', 'Advancing the rights and well-being of Indigenous communities.'),
    ('Climate Change Adaptation', 'Helping communities adjust to environmental changes.'),
    ('Youth Empowerment', 'Supporting young people with education, training, and leadership opportunities.'),
    ('Disability Inclusion', 'Ensuring accessibility and equal opportunities for people with disabilities.'),
    ('Refugee Support', 'Providing assistance and resources for displaced populations.'),
    ('Civic Engagement', 'Encouraging participation in social and political processes.'),
    ('Employment Equity', 'Promoting fair and inclusive work opportunities.'),
    ('Senior Support', 'Addressing the needs and well-being of elderly populations.'),
    ('Prisoner Reintegration', 'Helping formerly incarcerated individuals transition back into society.'),
    ('Human Trafficking Prevention', 'Combating forced labor and exploitation.'),
    ('LGBTQ+ Rights', 'Advocating for equal rights and protections for LGBTQ+ individuals.'),
    ('Addiction Recovery', 'Providing support for individuals struggling with substance abuse.'),
    ('Disaster Relief', 'Providing emergency aid and long-term recovery support for affected communities.'),
    ('Rural Development', 'Improving infrastructure and opportunities in rural areas.'),
    ('Urban Poverty', 'Addressing social and economic challenges in low-income urban areas.'),
    ('Fair Trade & Ethical Sourcing', 'Promoting fair wages and responsible supply chains.'),
    ('Child Protection', 'Preventing abuse, exploitation, and neglect of children.'),
    ('Access to Justice', 'Ensuring legal representation and rights protection for marginalized groups.');
-- insert into the linking table
INSERT INTO Social_Issue_Categories (issue_id, category_id) VALUES 
    -- Justice & Human Rights
    (1, 1), -- Access to Justice
    (2, 1), -- Human Trafficking Prevention
    (3, 1), -- LGBTQ+ Rights
    (4, 1), -- Employment Equity
    (5, 1), -- Gender Equality
    (6, 1), -- Indigenous Rights
    (7, 1), -- Disability Inclusion
    -- Poverty & Economic Inclusion
    (8, 2), -- Affordable Housing
    (9, 2), -- Food Security
    (10, 2), -- Financial Inclusion
    (11, 2), -- Urban Poverty
    (12, 2), -- Rural Development
    (13, 2), -- Fair Trade & Ethical Sourcing
    -- Health & Well-being
    (14, 3), -- Healthcare Access
    (15, 3), -- Mental Health Support
    (16, 3), -- Addiction Recovery
    (17, 3), -- Senior Support
    (7, 3), -- Disability Inclusion (also in Justice & Human Rights)
    -- Education & Opportunity
    (18, 4), -- Education Equity
    (19, 4), -- Digital Divide
    (20, 4), -- Youth Empowerment
    (21, 4), -- Prisoner Reintegration
    -- Environment & Sustainability
    (22, 5), -- Climate Change Adaptation
    (23, 5), -- Environmental Sustainability
    (24, 5), -- Water Access
    (25, 5), -- Sanitation
    -- Community & Civic Engagement
    (26, 6), -- Refugee Support
    (27, 6), -- Civic Engagement
    (28, 6), -- Child Protection
    (29, 6); -- Disaster Relief
	
	
-- Insert Ontario-based Organizations
INSERT INTO Organizations (id, name, description, website, country, province, city, contact_email) VALUES
    (1, 'Daily Bread Food Bank', 'A Toronto-based food bank dedicated to eliminating food insecurity and advocating for solutions to end poverty.', 'https://www.dailybread.ca/', 'Canada', 'Ontario', 'Toronto', 'info@dailybread.ca'),
    (2, 'Second Harvest', 'Canada''s largest food rescue charity, operating nationwide to reduce food waste and feed communities.', 'https://www.secondharvest.ca/', 'Canada', 'Ontario', 'Toronto', 'info@secondharvest.ca'),
    (3, 'Centre for Addiction and Mental Health (CAMH)', 'Canada''s largest mental health teaching hospital and one of the world''s leading research centers in its field.', 'https://www.camh.ca/', 'Canada', 'Ontario', 'Toronto', 'info@camh.ca'),
    (4, 'Water First', 'An organization dedicated to addressing water challenges in Indigenous communities through education, training, and meaningful collaboration.', 'https://waterfirst.ngo/', 'Canada', 'Ontario', 'Creemore', 'info@waterfirst.ngo'),
    (5, 'Habitat for Humanity GTA', 'A nonprofit organization helping families in the Greater Toronto Area build and improve places to call home.', 'https://habitatgta.ca/', 'Canada', 'Ontario', 'Toronto', 'info@habitatgta.ca'),
    (6, 'Environmental Defence Canada', 'A leading Canadian environmental advocacy organization working to defend clean water, a safe climate, and healthy communities.', 'https://environmentaldefence.ca/', 'Canada', 'Ontario', 'Toronto', 'info@environmentaldefence.ca'),
    (7, 'The 519', 'A Toronto-based organization dedicated to the health, happiness, and full participation of the LGBTQ2S communities.', 'https://www.the519.org/', 'Canada', 'Ontario', 'Toronto', 'info@the519.org'),
    (8, 'Community Living Ontario', 'A non-profit provincial association advocating for people with intellectual disabilities and their families for over 70 years.', 'https://communitylivingontario.ca/', 'Canada', 'Ontario', 'Toronto', 'info@communitylivingontario.ca'),
    (9, 'United Way Greater Toronto', 'A charity dedicated to fighting local poverty and building strong communities across the Greater Toronto Area.', 'https://www.unitedwaygt.org/', 'Canada', 'Ontario', 'Toronto', 'info@unitedwaygt.org'),
    (10, 'WoodGreen Community Services', 'One of Toronto''s largest social service agencies, offering a wide range of programs to build sustainable communities.', 'https://www.woodgreen.org/', 'Canada', 'Ontario', 'Toronto', 'info@woodgreen.org'),
-- Insert Ontario-based Organizations (IDs 11 to 20)
    (11, 'ACCES Employment', 'A leader in connecting employers with qualified employees from diverse backgrounds, serving over 32,000 job seekers annually across the Greater Toronto Area.', 'http://accesemployment.ca/', 'Canada', 'Ontario', 'Toronto', 'info@accesemployment.ca'),
    (12, 'Access Community Capital Fund', 'A Canadian Registered Charity formed in 1999 to help individuals facing barriers to traditional financing obtain microloans to improve their income through sustainable self-employment and employment.', 'http://accessccf.com/', 'Canada', 'Ontario', 'Toronto', 'info@accessccf.com'),
    (13, 'Afghan Women''s Organization', 'Provides settlement services to all newcomers, with a special focus on women, their families, refugees, and people who have experienced war and persecution.', 'http://afghanwomen.org/', 'Canada', 'Ontario', 'Toronto', 'info@afghanwomen.org'),
    (14, 'Ontario Nonprofit Network', 'The independent network for Ontario''s 58,000 nonprofit organizations, engaging with members to address issues and ensure community voices reach stakeholders.', 'https://theonn.ca/', 'Canada', 'Ontario', 'Toronto', 'info@theonn.ca'),
    (15, 'Social Planning Toronto', 'A nonprofit community organization that challenges inequity in Toronto through knowledge generation, debate, civic engagement, and advocacy for social change.', 'https://www.socialplanningtoronto.org/', 'Canada', 'Ontario', 'Toronto', 'info@socialplanningtoronto.org'),
    (16, 'Community Development Halton', 'A nonprofit organization dedicated to enhancing the quality of life in Halton Region through community education, social planning, and advocacy.', 'https://cdhalton.ca/', 'Canada', 'Ontario', 'Burlington', 'info@cdhalton.ca'),
    (17, 'Kin Canada', 'A national non-profit service organization promoting service, fellowship, positive values, and national pride, with clubs located throughout Canada.', 'http://www.kincanada.ca/', 'Canada', 'Ontario', 'Cambridge', 'info@kincanada.ca'),
    (18, 'Goodwill, The Amity Group', 'A nonprofit social enterprise providing work opportunities and skills development for people facing barriers to employment in the Hamilton and Halton regions.', 'https://www.goodwillonline.ca/', 'Canada', 'Ontario', 'Hamilton', 'info@goodwillonline.ca'),
    (19, 'Anishnawbe Health Toronto', 'A fully accredited community health center providing traditional healing within a multi-disciplinary health care model to Toronto’s Indigenous community.', 'https://aht.ca/', 'Canada', 'Ontario', 'Toronto', 'info@aht.ca'),
    (20, 'Applegrove Community Complex', 'A multi-service community organization in Toronto offering programs and services to promote the well-being of individuals and families.', 'https://www.applegrovecc.ca/', 'Canada', 'Ontario', 'Toronto', 'info@applegrovecc.ca'),
    (21, 'Learning Enrichment Foundation', 'Provides services including child care, employment training, and settlement assistance to newcomers in Toronto.', 'https://lefca.org/', 'Canada', 'Ontario', 'Toronto', 'info@lefca.org'),
    (22, 'Family Service Ontario', 'Supports member agencies in providing mental health, wellness, and social services to individuals and families across Ontario.', 'https://familyserviceontario.org/', 'Canada', 'Ontario', 'Toronto', 'info@familyserviceontario.org'),
    (23, 'Ontario Association of Social Workers', 'Advocates for social workers and promotes the profession’s contributions to social justice and well-being in Ontario.', 'https://www.oasw.org/', 'Canada', 'Ontario', 'Toronto', 'info@oasw.org'),
    (24, 'Ontario Municipal Social Services Association', 'Supports municipal service managers and district social services boards in Ontario through policy work, advocacy, and professional development.', 'https://www.omssa.com/', 'Canada', 'Ontario', 'Toronto', 'info@omssa.com'),
    (25, 'The Table Soup Kitchen Foundation', 'Provides food, clothing, and support services to individuals and families in need in the Muskoka region.', 'https://thetablefoundation.ca/', 'Canada', 'Ontario', 'Huntsville', 'info@thetablefoundation.ca'),
    (26, 'SHORE Centre', 'Offers sexual health resources, education, and medical services to individuals in the Waterloo Region.', 'https://shorecentre.ca/', 'Canada', 'Ontario', 'Kitchener', 'info@shorecentre.ca'),
    (27, 'Mission Services of Hamilton', 'Provides emergency shelter, food, clothing, and rehabilitation programs to those in need in the Hamilton area.', 'https://mission-services.com/', 'Canada', 'Ontario', 'Hamilton', 'info@mission-services.com'),
    (28, 'Camp Kirk', 'Offers recreational programs for children with learning disabilities and attention deficit disorders in Ontario.', 'https://www.campkirk.com/', 'Canada', 'Ontario', 'Kirkfield', 'info@campkirk.com'),
    (29, 'Knights Table', 'Provides food bank services, meals, and support programs to individuals and families in need in the Peel Region.', 'https://knightstable.org/', 'Canada', 'Ontario', 'Brampton', 'info@knightstable.org'),
    (30, 'Eva’s Initiatives for Homeless Youth', 'Provides shelter, transitional housing, and support services to help homeless youth build brighter futures.', 'https://www.evas.ca/', 'Canada', 'Ontario', 'Toronto', 'info@evas.ca'),
    (31, 'Oasis By The Bay Vacation Suites', 'An accessible, apartment-style motel in Wasaga Beach providing inclusive accommodations and promoting tourism in the Georgian Triangle area.', 'https://oasisbythebay.ca/', 'Canada', 'Ontario', 'Wasaga Beach', 'inquiries@oasisbythebay.ca'),
    (32, 'ReUse Centre', 'A Collingwood-based retail outlet accepting donations of quality, pre-owned items, promoting waste diversion and supporting community initiatives.', 'https://reusecollingwood.com/', 'Canada', 'Ontario', 'Collingwood', 'reuse@e3.ca'),
    (33, 'Blue Mountain Village Foundation', 'An organization supporting local charitable initiatives in South Georgian Bay, focusing on closing social gaps within the community.', 'https://bluemountainvillage.ca/foundation', 'Canada', 'Ontario', 'Blue Mountains', 'foundation@bluemountainvillage.ca'),
    (34, '100 Women Who Care South Georgian Triangle', 'A group of women from Collingwood, Creemore, Meaford, Stayner, Thornbury, and Wasaga Beach, collectively supporting local charities through coordinated donations.', NULL, 'Canada', 'Ontario', 'Collingwood', NULL),
    (35, 'Elephant Thoughts', 'A registered educational charity dedicated to supporting positive change through education and opportunity both in Canada and abroad.', 'https://www.elephantthoughts.com/', 'Canada', 'Ontario', 'Collingwood', 'inquiries@elephantthoughts.com'),
    (36, 'E3 Community Services', 'A multi-service organization providing support services to individuals with developmental and intellectual disabilities, focusing on individual strengths, needs, and choices.', 'https://e3.ca/', 'Canada', 'Ontario', 'Collingwood', 'e3@e3.ca'),
    (37, 'Community Builders', 'A non-profit construction-based social enterprise in Simcoe County and Greater Sudbury, offering training and employment opportunities.', 'https://www.communitybuilders.co/', 'Canada', 'Ontario', 'Minesing', 'info@communitybuilders.co'),
    (38, 'Fleurish - Botanical Goodness', 'A Barrie-based social enterprise providing floral arrangements and offering paid transitional employment opportunities.', 'https://www.fleurishbarrie.com/', 'Canada', 'Ontario', 'Barrie', 'info@fleurishbarrie.com'),
    (39, 'The Common Roof', 'A collaborative space in Barrie and Orillia that houses multiple human-service non-profit agencies, fostering community support and shared resources.', 'https://www.thecommonroof.ca/', 'Canada', 'Ontario', 'Barrie', 'info@thecommonroof.ca'),
    (40, 'Camphill Communities Ontario', 'An organization offering residential and day support services for individuals with developmental disabilities, operating social enterprises like market gardens and maple syrup production.', 'https://www.camphill.on.ca/', 'Canada', 'Ontario', 'Angus', 'info@camphill.on.ca'),
    (41, 'Habitat for Humanity Huronia ReStore', 'A Barrie-based retail outlet selling donated new and gently used building materials and home furnishings, supporting Habitat for Humanity’s mission.', 'https://habitatgatewaynorth.com/restore/', 'Canada', 'Ontario', 'Barrie', 'restore@habitatgatewaynorth.com'),
    (42, 'Rise Asset Development', 'Provides microfinancing and mentorship to entrepreneurs living with mental health and addiction challenges across Ontario.', 'https://www.riseassetdevelopment.com/', 'Canada', 'Ontario', 'Toronto', 'info@riseassetdevelopment.com');

-- Insert Initiatives
INSERT INTO Initiatives (name, description, organization_id) VALUES 
    ('Low-Cost Housing Project', 'Developing 1,000 affordable homes in urban areas.', 1),
    ('Youth Employment Drive', 'Providing job training for young adults.', 2);

-- Insert Resources
INSERT INTO Resources (name, description, type, provider_id) VALUES 
    ('Housing Development Grant', 'Funding available for low-income housing projects.', 'Funding', 1),
    ('Job Training Toolkit', 'A set of resources to improve employability skills.', 'Tool', 2);

-- Insert Connections
INSERT INTO Connections (issue_id, related_issue_id, initiative_id, organization_id, connection_type) VALUES 
    (1, 2, NULL, NULL, 'Affects'), -- Affordable Housing affects Employment
    (2, 3, NULL, NULL, 'Depends On'), -- Employment depends on Food Security
    (3, 4, NULL, NULL, 'Depends On'), -- Food Security depends on Water Access
    (1, NULL, 1, NULL, 'Supports'), -- Low-Cost Housing Project supports Affordable Housing
    (2, NULL, 2, NULL, 'Supports'); -- Youth Employment Drive supports Employment

Below is a screenshot of the database loaded into DBeaver.

Queries

We can run some queries against the database to look at some of the date. Here are a few examples. 

SELECT * FROM Organizations ORDER BY name LIMIT 3;
SELECT * FROM Organizations WHERE name LIKE '%acc%' ORDER BY name LIMIT 3;

Bell Let’s Talk needs to be added, as well as Mike Shoreman

A few considerations from ChatGPT:

  • Indexes for Performance: If you expect large datasets, consider adding indexes to frequently queried foreign keys (e.g., issue_id, category_id, organization_id).
  • Connection Types: The Connections table allows linking issues to other issues, initiatives, and organizations with predefined relationship types. You might want to consider allowing a description column to provide context on the connection.
  • Organization Location Structure: Right now, Organizations includes country, province, and city as text fields. If you plan to expand globally or enable filtering by location, a separate Locations table with standardized region names and a foreign key in Organizations might be useful.
  • Resource Generalization: If resources could be provided by both Organizations and Initiatives, you might consider a provider_type column to differentiate between them.
  • Validation on URLs & Emails: If working with an application layer, ensure email and URL formats are validated.
Series Navigation<< SE DatabaseProtected: Script for SQLite 4 >>

Leave a Reply