Stop 06 · Databases (SQL & NoSQL)Topics · 1/8

Relational Database Concepts

Part of the Full Stack Developer Roadmap

In short

Almost every app you use, from IRCTC to your college ERP, keeps its core data in tables. Learn how tables link up and you can read any backend's data like a story.

The model of organizing data into related tables with rows and columns — the foundation of SQL databases and still the default choice for most structured application data.

The blob sits between two open registers, with a thread joining matching rows.

Think of it like this

The college office registers

The office keeps a student register and a separate fee register. The fee register doesn't copy your name and address. It just writes your roll number, and anyone can flip to the student register for the rest. That roll number is the link.

Watch it happen

Inside college_db

college_db

students table

fees table

cols: id, name

row: 7, Riya

cols: student_id

row: 101, sid 7

1/5A database is one container. Here it's college_db, holding every table the app needs.

The big ideas

What you need to know

Tables, rows, columns

A table holds one kind of thing. Each row is one record, and each column is one fact about it, with a fixed type.

Primary key

A column that uniquely names each row, usually id. No two rows share it, and it's never empty.

Foreign key

A column that points at another table's primary key. It's how a fee knows which student it belongs to.

Ask with SQL

You describe what you want, like 'Riya's unpaid fees', and the database works out how to fetch it.

See it in code

Walk through the code

sql

1CREATE TABLE students (2  id    SERIAL PRIMARY KEY,3  name  TEXT NOT NULL4);5 6CREATE TABLE fees (7  id          SERIAL PRIMARY KEY,8  student_id  INT NOT NULL REFERENCES students(id),9  amount      INT NOT NULL,10  paid        BOOLEAN DEFAULT false11);12 13INSERT INTO students (name) VALUES ('Riya');          -- gets id 114INSERT INTO fees (student_id, amount) VALUES (1, 45000);

1/3students has a primary key called id. SERIAL makes Postgres hand out 1, 2, 3 automatically.

Run this in any PostgreSQL shell. Two tables, one link, and Riya's name is stored exactly once.

Save yourself the pain

Common mistakes

Oops

Putting everything in one giant table, with the student's name repeated on every fee row.

Do this instead

Keep one table per kind of thing and link them with ids. Change Riya's name once and it's right everywhere.

Oops

Using a name or email as the primary key.

Do this instead

Use a plain id. People change names and emails; an id never needs to change.

Your mission

Model your hostel

~20 min

Quick check

Why does the fees table store student_id instead of the student's name?

Go deeper

1Sketch tables for a simple app, e.g. a blog, and how they relate.
2Learn the difference between a table, row, column and schema.
3Practice a few JOIN queries once you have related tables to work with.