Linking our to-do items to users in the database

If we had both the user and to-do item tables in the same database, we could tether them using a foreign key as seen in figure 11.1.

Figure 11.1 – Foreign key association between our user and items

There are plenty of advantages to the foreign key approach. For instance, we would not be able to enter a user ID into the item table if the user ID did not exist in the user table. We could also enforce a cascading delete in the item table with the following SQL:

CREATE TABLE Item (
    id SERIAL PRIMARY KEY,
    title VARCHAR(50),
    status TEXT NOT NULL,
    user_id INT REFERENCES User(id) ON DELETE CASCADE
);

This means that if we delete a user, then all the items associated with that user would also get deleted. We could also perform a join query with the SQL below:

SELECT
    User.id AS user_id,
    User.username,
    Item.id AS todo_id,
    Item.title,
    status.status,
FROM
    users
JOIN
    todos ON users.id = todos.user_id...