family_structure.sql

-- * Adam is a widower. He was married to Bea (deceased) with whom he had two children: Clive and Doris.
-- * Eric is divorced. He was married to Fiona. Fiona has two children by a previous marriage, Greta and Hillary. Eric shares parental responsibilities for G and H with Fiona.
-- * Adam and Eric are currently married.
-- * Because Adam and Eric don't have enough headaches, Fiona is currently acting as a surrogate and is pregnant with Adam's child, who will be adopted by Eric.
-- Design a fully normalized database schema that can accommodate this family.

CREATE TABLE human
(
    id UUID NOT NULL PRIMARY KEY,
    name VARCHAR(255) NULL,  -- some people don't have names
    died TIMESTAMP NULL
)

CREATE TABLE marriage
(
    id UUID NOT NULL PRIMARY KEY
);

CREATE TABLE marriage_partner
(
    -- No primary key here -- a person might marry and divorce the same person several times, resulting in
    -- multiple marriage_partner entries.
    marriage_id UUID NOT NULL FOREIGN KEY REFERENCES marriage(id),
    human_id UUID NOT NULL FOREIGN KEY REFERENCES human(id),
    start TIMESTAMP NOT NULL,
    end TIMESTAMP NULL
);

CREATE TABLE parental_role
(
    parent_id UUID NOT NULL FOREIGN KEY REFERENCES human(id),
    child_id UUID NOT NULL FOREIGN KEY REFERENCES human(id),
    -- Application-defined enum.
    -- Can be something like "BIRTH_PARENT", "GUARDIAN", "CHILD_SUPPORT".
    -- In the case of a parent who gave birth to the kid and also is raising them,
    -- there will be two parental roles for the same parent + kid, one for
    -- BIRTH_PARENT and one for GUARDIAN.
    -- BIRTH_PARENT roles don't ever expire and could be moved to their own table.
    role_type VARCHAR(20),
    start TIMESTAMP NOT NULL,
    end TIMESTAMP NULL
);


CREATE TABLE subscription
(
    id UUID NOT NULL PRIMARY KEY,
    human_id UUID FOREIGN KEY REFERENCES human(id),
    subscription_type VARCHAR(20),
    start TIMESTAMP NOT NULL,
    end TIMESTAMP NULL
);


-- * Show example query to determine whether any two individual members are eligible for "home sharing" of internet content with one another (one must be married to, parent of, child of, or the actual content owner).
-- (Not sure if the union syntax is entirely correct...should be vaguely close.)
SELECT id FROM
    (SELECT :target AS id)
UNION
    (WITH role_matches AS
        SELECT * FROM parental_role
            WHERE (:target = parental_role.child_id OR :target = parental_role.parent_id)
                AND parental_role.role = 'GUARDIAN'
                AND parental_role.end = NULL
        SELECT role_matches.parent_id UNION (SELECT role_matches.child_id))
UNION
    (SELECT my_partner.human_id AS id FROM marriage_partner my_marriage
        WHERE my_marriage.human_id = :target AND my_marriage.end = NULL
        INNER JOIN marriage_partner my_partner
            ON (my_partner.marriage_id = my_marriage.marriage_id AND my_partner.end = NULL)
    );

-- * For added credit, show how the database schema may be generalized to encompass polyamorous marriages, divorces, and bereavements.
-- The schema already encompasses polyamorous marriages. It allows for overlapping marriages too --
-- I'm married to Agnes, who's married to Sue, but Sue and I aren't married.
-- Divorces are handled by setting the `end` field of the marriage_partner entry.
-- At the moment, this will allow you to share content with dead people. Maybe not a problem?