Insert data into strongly normalized DB and maintain the integrity (Postgres)

Question

I'm trying to develop a simple database for the phonebook. This is what I wrote:

CREATE TABLE phone
(
    phone_id SERIAL PRIMARY KEY,
    phone    CHAR(15),
    sub_id   INT,   -- subscriber id --
    cat_id   INT    -- category id --
);

CREATE TABLE category
(
    cat_id   SERIAL PRIMARY KEY,    -- category id --
    cat_name CHAR(15)       -- category name --
);

CREATE TABLE  subscriber
(
    sub_id  SERIAL PRIMARY KEY,
    name    CHAR(20),
    fname   CHAR(20),   -- first name --
    lname   CHAR(20),   -- last name --
);

CREATE TABLE address
(
    addr_id       SERIAL PRIMARY KEY,
    country       CHAR(20),
    city          CHAR(20),
    street        CHAR(20),
    house_num     INT,
    apartment_num INT
);

-- many-to-many relation --
CREATE TABLE sub_link
(
    sub_id   INT REFERENCES subscriber(sub_id),
    addr_id  INT
);

I created a link table for many-to-many relation because few people can live at the same address and one person can live in different locations at different times.

But I cannot figure out how to add data in strongly normalized DB like this and maintain the integrity of the data.

The first improvement was that I added inique key on address table bacause this table should not contain duplicated data:

CREATE TABLE address
(
    addr_id       SERIAL PRIMARY KEY,
    country       CHAR(20),
    city          CHAR(20),
    street        CHAR(20),
    house_num     INT,
    apartment_num INT,
    UNIQUE (country, city, street, house_num, apartment_num)
);

Now the problem is how to add a new record about some person into DB. I think I should use the next order of actions:

1. Insert a record into subscriber table, because sub_link and phone tables must use id of a new subscriber.

2. Insert a record into address table because addr_id must exist before adding record into sub_link.

3. Link last records from subscriber and address in sub_link table. But at this step I have a new problem: how can I get sub_id and addr_id from steps 1) and 2) in PostgreSQL effectively?

4. Then I need to insert a record into the phone table. As at 3) step I dont know how to get sub_id from previous queries effectively.

I read about WITH block in the Postgres but I cannot figure out how to use it in my case.

UPDATE I've done like ASL suggested:

-- First record --
WITH t0 AS (
    WITH t1 AS (
            INSERT INTO subscriber
            VALUES(DEFAULT, 'Twilight Sparkle', NULL, NULL)
            RETURNING sub_id
    ),
    t2 AS (
            INSERT INTO address
            VALUES(DEFAULT, 'Equestria', 'Ponyville', NULL, NULL, NULL)
            RETURNING addr_id 
    )
    INSERT INTO sub_link
    VALUES((SELECT sub_id FROM t1), (SELECT addr_id FROM t2))
)
INSERT INTO phone
VALUES (DEFAULT, '000000', (SELECT sub_id FROM t1), 1);

But I have an error: WITH clause containing a data-modifying statement must be at the top level LINE 2: WITH t1 AS (INSERT INTO subscriber VALUES(DEFAULT,

Answer 1

You can do it all in one query using a WITH block with a RETURNING clause. See PostgreSQL docs on INSERT. For example:

WITH t1 AS (INSERT INTO subscriber VALUES ... RETURNING sub_id),
t2 AS (INSERT INTO address VALUES ... RETURNING addr_id)
INSERT INTO sub_link VALUES ((SELECT sub_id FROM t1), (SELECT addr_id FROM t2))

Note that this simple form will only work when inserting a single row into each table.

This is somewhat off the topic of your question, but I suggest you also consider making sub_id and cat_id columns in the phone table foreign keys (use REFERENCES).