Untitled

-- Create tables

CREATE TABLE persons (
    person_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

CREATE TABLE addresses (
    address_id SERIAL PRIMARY KEY,
    street_address VARCHAR(100),
    city VARCHAR(50),
    county VARCHAR(50),
    country VARCHAR(50),
    postal_code VARCHAR(20)
);

CREATE TABLE contact_numbers (
    number_id SERIAL PRIMARY KEY,
    contact_id INT REFERENCES contacts(contact_id),
    phone_number VARCHAR(20),
    mobile_number VARCHAR(20),
    PRIMARY KEY (number_id, contact_id)
);

CREATE TABLE working_hours (
    hours_id SERIAL PRIMARY KEY,
    contact_id INT REFERENCES contacts(contact_id),
    working_hours VARCHAR(50),
    PRIMARY KEY (hours_id, contact_id)
);

CREATE TABLE contacts (
    contact_id SERIAL PRIMARY KEY,
    person_id INT REFERENCES persons(person_id),
    address_id INT REFERENCES addresses(address_id),
    UNIQUE(person_id, address_id),
    UNIQUE(contact_id, person_id, address_id) -- ensure unique combinations
);

-- Insert data

INSERT INTO persons (first_name, last_name)
VALUES
    ('Teresa Eli', 'Garcia Ramirez Arroyo Lopez'),
    (NULL, NULL);

INSERT INTO addresses (street_address, city, county, country, postal_code)
VALUES
    ('Urion 30', 'Col. Atlatilco', 'D.F', 'MEXICO', '02860'),
    ('Box 777, 91 Western Road', 'Brighton', 'East Sussex', 'England', 'BN1 2NW');

INSERT INTO contact_numbers (contact_id, phone_number, mobile_number)
VALUES
    (1, '011-52 (55) 5277-5875', NULL),
    (2, NULL, '+44 7700 900512');

INSERT INTO working_hours (contact_id, working_hours)
VALUES
    (1, 'Monday - Friday 9:00 - 18:00'),
    (2, NULL);

INSERT INTO contacts (person_id, address_id)
VALUES
    (1, 1),
    (2, 2);