Tables

Relationships

There are three types of relationships between entities:

• One to One
• One to Many
• Many to Many

One to One

A one to one relationship is where one entity is related to another entity.

For example, a user can have one profile and a profile can belong to one user.

One to Many

A one to many relationship is where one entity is related to many entities.

For example, a user can have many posts but a post can only belong to one user.

Many to Many

A many to many relationship is where many entities are related to many entities.

For example, a user can have many roles and a role can belong to many users.

Primary Keys

A primary key is a column that uniquely identifies each row in a table.

For example, if we have a users table, we can use the id column as the primary key.

id	name	email
1	John
2	Sarah
3	Jane

Creating a Primary Key

When creating a table, we can specify a column as a primary key by using the PRIMARY KEY constraint.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255),
  email VARCHAR(255)
);

Tip

It is common practice to use the id column as the primary key and to make it an INTEGER type.

This makes sure it is unique, easier to reference and more performant.

Foreign Keys

When we have a table that references another table, we call the column that references the other table a foreign key.

An example would be the below schema which has a users table and a posts table. The posts table has a user_id column which references the id column in the users table.

users	posts
id	id
name	title
email	body
	user_id

As the posts collumn references the users table, we call it a foreign key.

Creating a Foreign Key

When creating a table, we can specify a column as a foreign key by using the references method.

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255),
  body TEXT,
  user_id INTEGER
  FOREIGN KEY (user_id) REFERENCES users(id)
);

Tip

You can also specify the REFERENCES clause after the column definition.

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255),
  body TEXT,
  user_id INTEGER REFERENCES users(id)
);

However some databases such as MySQL do not support this syntax.

Table Joins

When we have two tables that are related to each other, we can use a JOIN to combine the data from both tables into a single result set.

For example, if we have a users table and a posts table, we can use a JOIN to get all the posts for a given user.

SELECT * FROM posts
JOIN users ON users.id = posts.user_id
WHERE users.id = 1;

The above query will return all the posts for the user with an id of 1.

| id | title | body | user_id | id | name | email |
|----|-------|------|---------|----|------|-------|
| 1  | ...   | ...  | 1       | 1  | ...  | ...   |
| 2  | ...   | ...  | 1       | 1  | ...  | ...   |
| 3  | ...   | ...  | 1       | 1  | ...  | ...   |