Chinwuba

Posted on Jun 4

Understanding PostgreSQL Tables, Relationships, and Foreign Keys: A Beginner's Journey

#webdev #backend #postgres #programming

Today I moved beyond JavaScript arrays and started learning how real applications store data using PostgreSQL.

If you're just getting into backend development, here's a practical explanation of what I learned.

What Is PostgreSQL?

PostgreSQL is a relational database.

Instead of storing data in temporary variables that disappear when your server restarts, PostgreSQL stores data permanently in tables.

Think of it like a highly structured spreadsheet that can handle millions of records and enforce rules on your data.

Understanding Tables

A table contains:

Columns → Define the structure
Rows → Store the actual data

Example:

| id | name     | email                                               |
| -- | -------- | --------------------------------------------------- |
| 1  | Chinwuba | [chinwuba@example.com](mailto:chinwuba@example.com) |
| 2  | John     | [john@example.com](mailto:john@example.com)         |

Each row represents one record.

Each column represents a specific attribute of that record.

Data Types Matter

Unlike spreadsheets, PostgreSQL enforces data types.

sql
CREATE TABLE clients (
    id UUID PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    phone TEXT,
    brand_info JSONB,
    created_at TIMESTAMP DEFAULT now()
);

Some common data types:

TEXT → Strings
UUID → Unique identifiers
TIMESTAMP → Date and time
BOOLEAN → True or false
JSONB → Structured JSON data
DECIMAL → Money and financial values

One important lesson:

Phone numbers should be stored as TEXT, not numbers.

Why?

Because numbers like:

08012345678

can lose their leading zero when treated as numeric values.

CRUD Operations

Most database interactions fall into four categories.

Create

INSERT INTO clients (name, email)
VALUES ('Chinwuba', 'chinwuba@example.com');

Read

SELECT * FROM clients;

Update

UPDATE clients
SET email = 'new@email.com'
WHERE id = '123';

Delete

DELETE FROM clients
WHERE id = '123';

Always use a WHERE clause when updating or deleting records.

Without it, every row gets modified.

Understanding Relationships

The biggest concept I learned today was relationships.

One-to-Many

One client can have many projects.

Example:

Client
  └── Project A
  └── Project B
  └── Project C

To model this relationship:

CREATE TABLE projects (
    id UUID PRIMARY KEY,
    name TEXT NOT NULL,
    status TEXT NOT NULL,
    stage TEXT NOT NULL,
    budget DECIMAL NOT NULL,
    client_id UUID REFERENCES clients(id),
    created_at TIMESTAMP DEFAULT now()
);

The key piece here is:

client_id UUID REFERENCES clients(id)

This is called a foreign key.

It tells PostgreSQL:

"Every client_id inside projects must exist inside the clients table."

If it doesn't exist, PostgreSQL rejects the insert.

A Database Design Mistake I Made

Initially, I wanted my clients table to contain:

status
stage
budget
invoice

It sounded reasonable until I asked:

"What happens when one client has multiple projects?"

Each project can have:

Different status
Different budget
Different stage

That means those fields describe a project, not a client.

This was my first lesson in data modeling:

Store data where it logically belongs.

Why Foreign Keys Are Important

Foreign keys enforce integrity.

Without them, you could accidentally create a project linked to a client that doesn't exist.

With foreign keys:

client_id UUID REFERENCES clients(id)

PostgreSQL guarantees the relationship remains valid.

The Error That Taught Me the Most

When inserting data, I got this error:

null value in column "id" violates not-null constraint

The reason?

I defined:

id UUID PRIMARY KEY

but never told PostgreSQL how to generate UUIDs automatically.

This taught me an important lesson:

Creating a column and configuring its behavior are two different things.

Final Thoughts

Today's session wasn't really about SQL syntax.

It was about learning how to model real-world relationships in a database.

The biggest takeaway:

A good database design mirrors how things relate in reality.

Clients have projects.

Projects belong to clients.

Relationships matter more than tables.

And every error message is an opportunity to understand how the database thinks.

Next up: inserting data, querying related records, and exploring JOINs.

DEV Community