Database-Schema
a Database Schema describes the structure of a database. Database schemas generally contain information about table/column names, data types/constraints, relationships between tables, and user roles.
PostgreSQL Naming Conventions
There are several naming conventions in PostgreSQL:
- Column names should be lower case with underscores between words (eg.,
birth_date
). - The primary key of a table is often named
id
.
Database Schema Design Tools
The image above shows an example database schema that was illustrated using a design tool. The schema contains four tables named orders, customers, orders_items, and items, respectively. The lines in the visualization show how columns in different tables are related to each other.

Database Tables
Database schemas describe the number of tables in a database and the data that they contain. Database tables should usually relate to a single construct described by an identifier called a primary key. For example, in an orders
table where every order has a customer, it would be inefficient to write the complete details of each customer for every order they made. Instead, a separate customers
table could contain that information.
PostgreSQL Variable Types
When designing a database schema in PostgreSQL, every column must have a data type. For example, the table created in the example code has three columns with types integer
, varchar
, and boolean
, respectively. This helps preserve data integrity over time by restricting the data that can be entered into the table.
CREATE TABLE people (
age integer,
name varchar,
is_citizen boolean
);
Primary Keys
The primary key of a database table is a column or group of columns that can be used to uniquely identify every row of the table. For example, a table of students might have a primary key named student_id
, which contains unique ID numbers for each student.
Composite Primary Keys
A primary key made up of multiple columns is called a composite primary key. Composite primary keys can be used when no single column uniquely identifies a row of a table, but multiple columns do. For example, the example table shown here could have a composite primary key made up of student_id
and course_id
.
| student_id| course_id | grade |
|-----------|-----------|-------|
| 1 | 503 | A |
| 1 | 401 | A- |
| 2 | 503 | B |
Foreign Keys
Database tables can have foreign key(s), which reference the primary key of another table. For example, an orders
table may contain a column named customer_id
, which references an id
column in a separate customers
table. In this case, the customer_id
column in the orders
table could be designated as a foreign key.
Creating a Primary Key
In PostgreSQL, a column can be designated as a primary key using the PRIMARY KEY
keyword. For example, the example code shows two CREATE TABLE
statements. In the orders
table, order_id
is designated as a primary key. In the orders_items
table, order_id
and item_id
are used to designate a composite primary key.
CREATE TABLE orders(
order_id integer PRIMARY KEY,
order_date date,
);
CREATE TABLE orders_items(
order_id integer,
order_date date,
item_id integer,
PRIMARY KEY (order_id,item_id)
);
Creating a Foreign Key
In PostgreSQL a foreign key is defined by using the REFERENCES
keyword. For example, the code here shows two CREATE TABLE
statements. The orders
table contains a foreign key called customer_id
, which references the id
column of the customers
table.
CREATE TABLE orders (
order_number integer,
customer_id integer REFERENCES customers(id),
);
CREATE TABLE customers (
id integer PRIMARY KEY,
name text
);
One-to-One Database Relationships
In a relational database, two tables have a one-to-one relationship if each row in one table links to exactly one row in the other table, and vice versa. For example, a table of employees
and a table of employee_contact_info
might have a one-to-one relationship if every employee listed in the employees
table has contact information listed in the employee_contact_info
table and vice versa.
Many-to-One Database Relationships
In a relational database, two tables have a many-to-one relationship if each row in one table links to multiple rows of the other table. For example, a table of customers
and a table of orders
would have a many-to-one relationship if each customer can make multiple orders, but each order can only be associated with one customer.
Many-to-Many Database Relationships
In a relational database, two tables have a many-to-many relationship if each row in one table can link to multiple rows in the other table, and vice versa. For example, a table of songs
and a table of artists
would likely have a many-to-many relationship because songs can have multiple artists and artists can have multiple songs.
Implementing a Many-to-One Database Relationship
A many-to-one relationship can be implemented in PostgreSQL by creating a foreign key that references the primary key of another table. For example, the code here implements a many-to-one relationship between an orders
and a customers
table, where each customer can be associated with multiple orders.
CREATE TABLE orders (
order_number integer,
customer_id integer REFERENCES customers(id),
);
CREATE TABLE customers (
id integer PRIMARY KEY,
name text
);
Implementing a Many-to-Many Database Relationship
A many-to-many database relationship can be implemented in PostgreSQL using a third cross-reference table. This table should have two foreign keys referencing the primary keys of the related tables, as well as a composite primary key made up of the foreign key columns. For example, the code here implements a many-to-many relationship between a songs
and an artists
table.
CREATE TABLE songs (
id integer PRIMARY KEY,
name varchar(100)
);
CREATE TABLE artists (
id integer PRIMARY KEY,
name varchar(100)
);
CREATE TABLE songs_artists (
artist_id integer REFERENCES artists(id),
song_id integer REFERENCES songs(id),
PRIMARY KEY (artist_id, song_id)
);
Implementing a One-to-One Database Relationship
A one-to-one relationship can be enforced in PostgreSQL by first creating a many-to-one relationship via a foreign key, then implementing a UNIQUE
constraint on the foreign key. For example, the code here implements a one-to-one relationship between tables named employees
and contact_info
.
CREATE TABLE employees (
id integer PRIMARY KEY,
name varchar(100)
);
CREATE TABLE contact_info (
employee_id integer REFERENCES employees(id) UNIQUE,
email text,
phone_number varchar(9)
);