Database Basics

Before choosing a database or writing a query, you need to understand the relational model — how data is structured, how tables relate to each other, and what rules the engine enforces automatically. These fundamentals apply whether you end up using PostgreSQL, MySQL, or any other relational database.

Tables

A relational database organises data into tables (also called relations). Each table represents a single entity type — users, orders, products. A table is made up of:

Columns (fields / attributes) — define the structure. Each column has a name and a fixed data type. The schema is declared once and enforced on every row.
Rows (records / tuples) — the actual data. Every row conforms to the column schema of its table.
Primary key — one or more columns that uniquely identify each row. No two rows in a table may share the same primary key value. A surrogate key (auto-incremented integer or UUID) is common; a natural key (e.g. email) works when it is truly unique and stable.

Common data types

Type	Use for	Examples
`INT / BIGINT`	Whole numbers, IDs, counters	user_id, quantity, age
`VARCHAR(n)`	Variable-length strings up to n chars	username, email, country_code
`TEXT`	Unbounded strings	post body, description
`BOOLEAN`	True / false flags	is_active, email_verified
`DECIMAL(p, s)`	Exact fixed-point numbers	price, balance (never use FLOAT for money)
`TIMESTAMP`	Date and time, usually UTC	created_at, updated_at
`UUID`	Globally unique identifiers	Surrogate PKs in distributed systems

Relationships

Tables rarely exist in isolation. A foreign key is a column in one table that references the primary key of another, creating a link between the two. There are three fundamental relationship types:

One-to-many

The most common relationship. One row in table A relates to many rows in table B. The foreign key lives on the "many" side.

users          orders
──────         ──────────────────
id (PK)  ←──  user_id (FK)
name           id (PK)
               total

One user can have many orders. Each order belongs to exactly one user.

Many-to-many

When rows on both sides can relate to multiple rows on the other side, a direct foreign key is not enough. A junction table (also called a bridge or associative table) sits between the two and holds a pair of foreign keys.

students          enrollments          courses
────────          ───────────────      ───────
id (PK)  ←──  student_id (FK)      id (PK)
name           course_id  (FK)  ──→ title

A student can enrol in many courses; a course can have many students. The junction table enrollments can carry its own attributes too — such as enrolled_at or grade.

One-to-one

Each row in table A maps to at most one row in table B. Used to split a table when some columns are rarely accessed (vertical partitioning) or when two entities have different access-control requirements.

users               user_profiles
──────              ─────────────────────────
id (PK)  ←──  user_id (FK, UNIQUE)
email          bio
               avatar_url

Constraints

Constraints are rules enforced by the database engine at write time. They are the first line of defence for data integrity — they prevent invalid data from ever reaching the disk, regardless of application-level bugs.

Constraint	What it enforces	Example
`PRIMARY KEY`	Uniqueness + NOT NULL on the identifier column(s)	`id SERIAL PRIMARY KEY`
`FOREIGN KEY`	Referenced row must exist in the parent table	`user_id INT REFERENCES users(id)`
`UNIQUE`	No two rows may share the same value in this column	`email VARCHAR UNIQUE`
`NOT NULL`	Column must always have a value	`created_at TIMESTAMP NOT NULL`
`CHECK`	Value must satisfy a boolean expression	`CHECK (price > 0)`
`DEFAULT`	Provides a value when none is supplied on insert	`is_active BOOLEAN DEFAULT true`

ON DELETE behaviour

When a parent row is deleted, the database must decide what to do with its child rows. This is configured on the foreign key:

RESTRICT / NO ACTION — reject the delete if any child rows exist. The default in most databases. Safe but requires manual cleanup.
CASCADE — automatically delete all child rows. Convenient for tightly-coupled entities (e.g. deleting a post should delete its comments), but dangerous if applied carelessly.
SET NULL — set the foreign key column to NULL in all child rows. Useful when the child can exist independently (e.g. a comment orphaned after a user account is deleted).
SET DEFAULT — set the foreign key to its default value. Rarely used.

SQL vs NoSQL

The first fork in every data layer decision: relational or non-relational. The answer depends on your data model, consistency requirements, and scale targets.

	SQL (Relational)	NoSQL
Data model	Tables with rows and columns, strict schema	Documents, key-value, wide-column, or graph — flexible schema
Consistency	ACID transactions	Usually eventual consistency (BASE)
Joins	First-class support	Typically avoided — denormalize instead
Scaling	Vertical primarily; horizontal via read replicas or sharding	Horizontal by design
Best for	Complex relationships, strong consistency, reporting	High write throughput, flexible schema, massive scale
Examples	PostgreSQL, MySQL, SQLite	MongoDB, Cassandra, DynamoDB, Redis

NoSQL data models

Document store (MongoDB, Firestore) — stores JSON-like documents. Good for nested, variable-shape data. Queries within a document are fast; cross-document joins are expensive.
Key-value store (Redis, DynamoDB) — the simplest model: a key maps to a blob. Extremely fast for lookups by key; poor for range scans or complex queries.
Wide-column store (Cassandra, HBase) — rows identified by a partition key, columns grouped into families. Optimised for writing and reading entire rows at high throughput across a distributed cluster.
Graph database (Neo4j) — nodes and edges with properties. Native support for relationship traversal. Ideal for social graphs, recommendation engines, and fraud detection — anywhere the connections between data matter as much as the data itself.