Databases and Tables

Welcome back.

In the previous lesson, you installed PostgreSQL, opened psql, created your first database, created your first table, inserted data, and ran your first SELECT.

That is already real database work.

Small.

But real.

Today we slow down and look at something extremely important:

Databases and tables.

Yes.

The names sound simple.

But this is where many future problems are born.

A bad table structure is like a messy garage.

At first everything looks fine.

Then one day you need one cable.

And suddenly you are fighting an old bicycle, three boxes, and a mysterious chair nobody remembers buying.

In PostgreSQL, structure matters.

A lot.

What You Will Learn

In this lesson, you will learn:

• what a database is;
• what a table is;
• how rows and columns work;
• what records are;
• how to think about table design;
• how to choose good table names;
• how to choose good column names;
• how to create multiple tables;
• how to inspect table structure;
• how to delete tables safely;
• how to avoid common beginner design mistakes.

By the end of this lesson, you will understand how PostgreSQL organizes data.

Not just how to type commands.

You will start thinking like a database developer.

Slightly dangerous.

Very useful.

Database vs Table

A database is a container.

A table is where the actual structured data lives.

Example:

Database: learning_postgresql

Tables:
  students
  books
  courses

The database contains tables.

The tables contain rows.

The rows contain actual data.

Think of it like this:

Database = house
Table    = room
Row      = object inside the room
Column   = detail about the object

For example, in a students table:

id | name   | age | email
---|--------|-----|-------------------
1  | Anna   | 22  | anna@example.com
2  | Marco  | 28  | marco@example.com

The table is students.

Each row is one student.

Each column describes something about the student.

Simple idea.

Very powerful.

Also very easy to make messy if you are not careful.

Like cables behind a desk.

Rows and Columns

A table has columns and rows.

Columns define the structure.

Rows contain the data.

Example:

products table

id | name     | price | available
---|----------|-------|----------
1  | Laptop   | 900   | true
2  | Mouse    | 25    | true
3  | Keyboard | 70    | false

Columns:

• id;
• name;
• price;
• available.

Rows:

• Laptop row;
• Mouse row;
• Keyboard row.

A column says what kind of information can be stored.

A row stores one real item.

This is important.

Do not create a new column for every new item.

Bad idea:

student1 | student2 | student3
---------|----------|---------
Anna     | Marco    | Viktor

Better idea:

id | name
---|--------
1  | Anna
2  | Marco
3  | Viktor

Rows are for records.

Columns are for properties.

This rule saves lives.

Database lives.

Still important.

What Is a Record?

A record is one row in a table.

In this table:

students

id | name   | age
---|--------|----
1  | Anna   | 22
2  | Marco  | 28

Anna is one record.

Marco is another record.

A record usually represents one thing:

• one user;
• one product;
• one order;
• one book;
• one course;
• one task.

If your table is called books, one row should be one book.

If your table is called users, one row should be one user.

If your table is called orders, one row should be one order.

This sounds obvious.

But database chaos often begins when one row tries to represent too many things.

One row should not try to be a user, an order, a payment, and a pizza receipt.

Even PostgreSQL has limits.

Emotionally.

Connect to Your Database

Open PostgreSQL:

sudo -iu postgres psql

Connect to the database from lesson 1:

\c learning_postgresql

If you do not have it, create it:

CREATE DATABASE learning_postgresql;

Then connect:

\c learning_postgresql

Now you are ready.

The database door is open.

Please do not run into the furniture.

List Tables

To list tables in the current database, run:

\dt

You may see:

students

If you created the table in lesson 1.

If you see no tables, that is fine.

Empty database.

Fresh start.

Like a new notebook.

Beautiful.

Slightly intimidating.

Create a Books Table

Let us create a new table named books.

CREATE TABLE books (
  id SERIAL PRIMARY KEY,
  title VARCHAR(150) NOT NULL,
  author VARCHAR(100),
  year_published INTEGER
);

This table has:

• id;
• title;
• author;
• year_published.

Now list tables:

\dt

You should see:

books
students

Now we have more than one table.

The database is becoming a small library.

Very professional.

Almost.

Inspect Table Structure

To inspect the books table, run:

\d books

PostgreSQL will show the table structure.

You will see column names, data types, and constraints.

Something like:

id             | integer
title          | character varying(150)
author         | character varying(100)
year_published | integer

This is useful when you forget what columns you created.

And you will forget.

No shame.

Databases remember because humans do not.

That is the arrangement.

Insert Books

Now insert some books:

INSERT INTO books (title, author, year_published)
VALUES ('Clean Code', 'Robert C. Martin', 2008);

INSERT INTO books (title, author, year_published)
VALUES ('The Pragmatic Programmer', 'Andrew Hunt', 1999);

INSERT INTO books (title, author, year_published)
VALUES ('Database Design for Mere Mortals', 'Michael J. Hernandez', 2013);

Now read the data:

SELECT * FROM books;

You should see your books.

The table is no longer empty.

It has data.

A table without rows is just a plan.

A table with rows is where the story begins.

Very poetic.

For SQL.

Naming Tables

Good names matter.

A table name should describe what the table stores.

Good table names:

students
books
products
orders
customers
courses

Bad table names:

data
stuff
things
my_table
table1
new_table_final_final_real

Do not name tables like you name emergency files on your desktop.

PostgreSQL deserves better.

So do you.

Use clear names.

In this course, we will usually use plural table names:

students
books
orders
products

Why plural?

Because the table stores many records.

A students table stores many students.

Simple.

Readable.

Not everyone agrees on plural vs singular table names.

That is okay.

The important thing is consistency.

Choose one style.

Use it everywhere.

Do not create student, books, order_table, and tbl_products in the same database.

That is not design.

That is soup.

Naming Columns

Column names should also be clear.

Good column names:

id
title
author
email
created_at
year_published
price
is_active

Bad column names:

x
value
data1
thing
info
abc

A column name should answer:

What is stored here?

Example:

CREATE TABLE courses (
  id SERIAL PRIMARY KEY,
  title VARCHAR(150) NOT NULL,
  description TEXT,
  price INTEGER
);

This is readable.

You can understand what the table stores.

Future you will appreciate this.

Future you is already tired.

Help him.

Use snake_case

In PostgreSQL, it is common to use snake_case.

Good:

year_published
created_at
first_name
last_name
is_active

Avoid spaces:

year published
first name

Avoid mixed case:

yearPublished
FirstName

PostgreSQL can handle quoted mixed-case names, but it becomes annoying quickly.

Example of annoying:

SELECT "FirstName" FROM users;

No thank you.

Use lowercase snake_case.

Your database will be cleaner.

Your queries will be easier.

Your future debugging will contain fewer tears.

One Table, One Topic

A table should usually store one type of thing.

Good:

students
courses
teachers

Bad:

students_and_courses_and_teachers

Why?

Because different things have different data.

A student has:

• name;
• email;
• age.

A course has:

• title;
• description;
• price.

A teacher has:

• name;
• subject;
• email.

Do not mix everything into one giant table.

A giant table looks easy at first.

Then it becomes a monster.

And not a cute monster.

A spreadsheet monster.

Terrifying.

Create a Courses Table

Now create another table:

CREATE TABLE courses (
  id SERIAL PRIMARY KEY,
  title VARCHAR(150) NOT NULL,
  description TEXT,
  price INTEGER
);

Insert some courses:

INSERT INTO courses (title, description, price)
VALUES ('PostgreSQL Basics', 'Learn database fundamentals with PostgreSQL.', 49);

INSERT INTO courses (title, description, price)
VALUES ('JavaScript Basics', 'Learn JavaScript from the beginning.', 59);

INSERT INTO courses (title, description, price)
VALUES ('HTML and CSS', 'Build clean web pages with HTML and CSS.', 39);

Read the data:

SELECT * FROM courses;

Now your database has at least three possible tables:

students
books
courses

Each table has its own topic.

Good.

Clean.

Database approved.

Probably.

Data Types Matter

Every column has a data type.

Example:

title VARCHAR(150)
price INTEGER
description TEXT

The data type tells PostgreSQL what kind of value can go into the column.

For example:

price INTEGER

means the price should be a number.

This is good.

Because databases should protect structure.

If you let every column accept anything, chaos arrives wearing sunglasses.

We will learn data types in more detail later.

For now, remember:

Use text columns for text.

Use number columns for numbers.

Use boolean columns for true or false.

Use dates for dates.

Do not store everything as text.

That is the database version of putting all tools into one box and calling it organization.

NULL and NOT NULL

A column can allow empty values.

In SQL, an empty or missing value is called NULL.

Example:

CREATE TABLE teachers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150),
  subject VARCHAR(100)
);

Here:

name VARCHAR(100) NOT NULL

means name is required.

But:

email VARCHAR(150)

can be empty.

So this works:

INSERT INTO teachers (name, subject)
VALUES ('Laura', 'Databases');

Because email is not required.

But this does not work:

INSERT INTO teachers (email, subject)
VALUES ('laura@example.com', 'Databases');

Why?

Because name is required.

PostgreSQL protects the rule.

Very strict.

Very useful.

Like a guard dog with SQL knowledge.

Create a Teachers Table

Create a table:

CREATE TABLE teachers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150),
  subject VARCHAR(100)
);

Insert data:

INSERT INTO teachers (name, email, subject)
VALUES ('Laura', 'laura@example.com', 'Databases');

INSERT INTO teachers (name, subject)
VALUES ('Paolo', 'Web Development');

Now read:

SELECT * FROM teachers;

You should see that Paolo has no email.

That missing value is NULL.

This is allowed because the email column does not have NOT NULL.

This is table design.

You decide what is required.

PostgreSQL enforces it.

You are the architect.

PostgreSQL is the serious builder.

Drop a Table

Sometimes you need to delete a table.

Be careful.

This removes the table and its data.

Example:

DROP TABLE teachers;

PostgreSQL will delete the table.

Forever.

No emotional recovery.

For safety, you can use:

DROP TABLE IF EXISTS teachers;

This means:

Drop the table if it exists.
If it does not exist, do not throw an error.

Do not randomly drop tables.

This is not a game.

Well, it is a course.

But still.

Respect DROP.

It has power.

Too much power for a word with only four letters.

Recreate a Table

If you dropped teachers, you can recreate it:

CREATE TABLE teachers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150),
  subject VARCHAR(100)
);

This is useful for practice.

During learning, it is okay to create and drop test tables.

Just do not do this in production.

Production databases are not playgrounds.

They are more like museums with electricity.

Touch carefully.

Common Beginner Design Mistakes

One Giant Table

Bad idea:

users_orders_products_payments

One table for everything.

This becomes hard to read, hard to update, and hard to understand.

Better:

users
orders
products
payments

Separate tables for separate things.

Clean structure.

Less crying.

Bad Column Names

Bad:

n
em
val
thing1
thing2

Better:

name
email
price
created_at

Clear names make SQL easier.

Unclear names make your future self angry.

And future self knows where you live.

Storing Lists in One Column

Bad:

student_name | courses
-------------|-------------------------------
Anna         | PostgreSQL, JavaScript, HTML

This looks easy.

But later it becomes difficult to search, update, and connect data.

Better design uses separate tables.

We will learn relationships later.

For now, remember:

If a value contains a list, maybe it wants another table.

Databases whisper these things.

Listen carefully.

Practice

Create a table named movies.

It should have:

• id;
• title;
• director;
• year_released;
• rating.

Example:

CREATE TABLE movies (
  id SERIAL PRIMARY KEY,
  title VARCHAR(150) NOT NULL,
  director VARCHAR(100),
  year_released INTEGER,
  rating INTEGER
);

Insert three movies:

INSERT INTO movies (title, director, year_released, rating)
VALUES ('Inception', 'Christopher Nolan', 2010, 9);

Read all movies:

SELECT * FROM movies;

Then inspect the table:

\d movies

Then list all tables:

\dt

This will help you practice:

• creating a table;
• choosing columns;
• inserting rows;
• reading rows;
• inspecting structure.

Very practical.

Very SQL.

Mini Challenge

Create a small database structure for a learning platform.

You need three tables:

students
courses
teachers

Each table should have at least three columns.

Example:

students:
  id
  name
  email

courses:
  id
  title
  price

teachers:
  id
  name
  subject

Create the tables.

Insert at least two rows into each table.

Then run:

SELECT * FROM students;
SELECT * FROM courses;
SELECT * FROM teachers;

This is still simple.

But now you are thinking in database structure.

Not just commands.

That is the goal.

Summary

Today you learned:

• a database contains tables;
• tables contain rows and columns;
• a row is also called a record;
• each table should store one type of thing;
• good table names are clear and consistent;
• good column names describe the data;
• snake_case is a good naming style;
• data types define what kind of value a column stores;
• NULL means missing value;
• NOT NULL makes a column required;
• \dt lists tables;
• \d table_name shows table structure;
• DROP TABLE deletes a table and its data;
• good structure prevents future chaos.

This lesson may look simple.

But it is extremely important.

Bad database design creates problems that slowly grow teeth.

Good database design makes future queries easier, safer, and cleaner.

PostgreSQL is powerful.

But it cannot save you from a table named stuff.

Be kind to your database.

Name things properly.

Structure things clearly.

Your future self will not send you angry messages.

Maybe.

Next Lesson

In the next lesson, we will learn basic SQL commands:

• SELECT;
• INSERT;
• UPDATE;
• DELETE.

These are the core actions of working with data.

Read.

Create.

Change.

Remove.

The database circle of life.

With semicolons.