3 Working With Tables

3: Working With Tables

Approaching database design

This design will be for a photo-sharing app.

There will be four tables:

users
photos
comments
likes

So what tables should we make?

Common features (like authentication, comments, etc.) are frequently built with conventional table names and columns.
What type of resources exist in your app? Create a separate table for each.
Features that seem to indicate a relationship or ownership between two types of resources need to be related through our design.

One-to-many and Many-to-one Relationships

When you establish the link between different records, you can then begin to implement the relationship.

In our particular case, we would say the following:

Photos can be owned by a user. One user can have many photos. A photo has one user.
Photos can have likes. One photo can have many likes.
Users can have many comments. One user can have many comments.
Photos can have many comments. One photo can have many comments.
User can have many likes. One user can have many likes.

One-to-one and Many-to-many Relationships

This won't be used in our example, but it is important to know about these early in the piece.

Examples of one-to-one:

A boat has a captain. A captain has one boat. (This is generalized).
A company has a CEO. A CEO has one company.
A country has one capitol. One capitol has one country.
A student has a desk. A desk has one student.

Examples of many-to-many:

Students <-> Classes.
Tasks <-> Engineers.
Players <-> Football matches.
Movies <-> Actors/actresses.

Primary keys and foreign keys

Primary key: uniquely identifies this record in this table.
Foreign key: identifies the record in another table that this row is associated with.

Whenever we talk about relationships, we are always going to need a foreign key.

In our example with one-to-many relationships, we will decide that the foreign key is the id of the "one" part of that relationship.

From our example:

comments will have two foreign keys: user_id and photo_id.
likes will have two foreign keys: user_id and photo_id.
photos will have one foreign key: user_id.

Notes on Primary Keys:

Each row in every table has one primary key.
No other row in the same table can have the same value.
99% of the time called 'id'.
Either an integer or a UUID.
Will never change.

Notes on Foreign Keys:

Rows only have this if they belong to another record.
Many rows in the same table can have the same foreign key.
Name varies, usually called something like xyz_id.
Exactly equal to the primary key of the referenced row.
Will change if the relationship changes.

Auto-Generated IDs

The SERIAL keyword will autogenerated auto-incrementing numbers.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

We can then add some users to see what happens:

INSERT INTO users (username) VALUES ('Bob'), ('Billy'), ('Bilfred'), ('Bilbo');
SELECT * FROM users;

We get back the following table:

id	username
1	Bob
2	Billy
3	Bilfred
4	Bilbo

Creating Foreign Key Constraints

We can use REFERENCES to create our foreign key.

CREATE TABLE photos (
    id SERIAL PRIMARY KEY,
    url VARCHAR(200) NOT NULL,
    user_id INTEGER REFERENCES users(id)
);

We can the insert some data:

INSERT INTO photos (url, user_id) VALUES ('https://one.jpg', 4);
SELECT * FROM photos;

We will get back our photo with the user_id reference.

Running Queries on Associated Data

Before we go too far, we are going to insert some more photos.

INSERT INTO photos (url, user_id) VALUES ('https://1.jpg', 1), ('https://2.jpg', 1), ('https://3.jpg', 1), ('https://4.jpg', 2), ('https://5.jpg', 3), ('https://6.jpg', 4);

Now that we have data from here, let's find a way to retrieve these photos in a meaningful way like "find photos created by user X".

SELECT * FROM photos WHERE user_id = 4;

In our example, this will bring back two photos with user id 4.

The next example is us using a join statement.

SELECT url, username FROM photos JOIN users ON users.id = photos.user_id;

From that, we get a list back of the urls and usernames of the photos when joined by the user record.

Foreign Key Constraints Around Insertion

For example, if we create a photo, we want to make sure that the user exists.

If we insert a photo that refers to a user that doesn't exist, we will get an error from a foreign key constraint.

If we insert a photo with no user, it will be successful (unless we enforce the scenario that it must exist).

Constraints Around Deletion

What happens if we delete a user that has photos? If we do not cascade delete, then we will have photos with dangling foreign keys.

We have some options for the DELETE option that we can use:

Delete option	What happen if we delete a user that has photos?
ON DELETE RESTRICT (default)	Throw an error
ON DELETE NO ACTION	Throw an error
ON DELETE CASCADE	Delete the photo too
ON DELETE SET NULL	Set the `user_id` of the photo to `NULL`
ON DELETE SET DEFAULT	Set the `user_id` of the photo to the default value (if one is provided)

Note: There is a difference between RESTRICT and NO ACTION that will be explored later.

To enforce these other delete options, we must create the table with that constraint:

CREATE TABLE photos (
    id SERIAL PRIMARY KEY,
    url VARCHAR(200) NOT NULL,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE
);