Relational Databases & Tables — Schema, Keys & ER Diagrams

Database → Tables → Rows

Hierarchy of relational storage

Table — a collection of rows about one entity (users, orders, products).
Column (field) — a named attribute with a data type (INTEGER, VARCHAR, DATE).
Row (record) — one instance in the table (one user, one order).

Sample Tables

users table (conceptual)json

1| user_id | email              | is_active |
2|---------|--------------------|-----------|
3| 1       | [email protected]  | true      |
4| 2       | [email protected]    | false     |

orders table (conceptual)json

1| order_id | customer_id | total_amount |
2|----------|-------------|--------------|
3| 1001     | 1           | 49.99        |
4| 1002     | 1           | 12.50        |
5| 1003     | 2           | 8.00         |

Notice customer_id in orders matches user_id in users. That link is how SQL joins know which orders belong to which user.

Entity-Relationship Diagram

Users and orders (one-to-many)

Cardinality

One user can place many orders; each order belongs to one user. This is a classic one-to-many (1:N) relationship.

Primary Keys & Foreign Keys

How a foreign key links rows

Primary key (PK)

Uniquely identifies a row. Enforced with PRIMARY KEY — no duplicates, no NULL.

Foreign key (FK)

References a PK in another table. Enforces referential integrity (no orphan orders).

Defining Tables in SQL

CREATE TABLE (ANSI-style, portable core)json

1CREATE TABLE users (
2  user_id   INTEGER PRIMARY KEY,
3  email     VARCHAR(255) NOT NULL UNIQUE,
4  is_active BOOLEAN NOT NULL DEFAULT TRUE
5);
6
7CREATE TABLE orders (
8  order_id     INTEGER PRIMARY KEY,
9  customer_id  INTEGER NOT NULL,
10  total_amount DECIMAL(10, 2) NOT NULL,
11  FOREIGN KEY (customer_id) REFERENCES users (user_id)
12);

Dialect differences

Auto-increment syntax varies: AUTO_INCREMENT (MySQL), SERIAL / GENERATED AS IDENTITY (PostgreSQL), IDENTITY (SQL Server). The idea is the same: stable surrogate primary keys.

Constraints at a Glance

Constraint	Purpose	Example
PRIMARY KEY	Unique row identifier	user_id
FOREIGN KEY	Link to another table	customer_id → users.user_id
UNIQUE	No duplicate values	email
NOT NULL	Value required	email NOT NULL
CHECK	Custom rule (where supported)	total_amount > 0
DEFAULT	Value if omitted on INSERT	DEFAULT TRUE

NULL — Unknown, Not Empty

SQL uses three-valued logic: true, false, and unknown (NULL). WHERE status = NULL is wrong — use WHERE status IS NULL.

Correct NULL checksjson

1SELECT email FROM users WHERE phone IS NULL;
2SELECT email FROM users WHERE phone IS NOT NULL;

Spreadsheet vs Normalized Tables

Pattern	Problem	Relational approach
One giant sheet	Duplicate user data on every order row	Separate users and orders tables
Comma-separated tags	Cannot index or filter tags easily	tags table + junction table
Multiple emails in one cell	Breaks validation	One row per email or child table
No key column	Duplicate rows, no stable JOIN	Surrogate PK on every table

Common Mistakes

Using NULL and empty string interchangeably — they mean different things.
Skipping foreign keys in development, then shipping orphan data to production.
Choosing natural keys (email) that can change; surrogate integer IDs are often safer.
Storing calculated totals without a strategy for updates — prefer computed columns or triggers carefully.

Practice Prompts

Try on paper or a whiteboard before your first SELECT lesson:

Design tables for a library: books, members, loans. What are the primary and foreign keys?
Where would NULL be appropriate (optional phone number) vs NOT NULL (book ISBN)?
Draw a 1:N diagram for authors and books (one author, many books).

Try These Tools

SQL Data Generator

Generate sample INSERT data for practice tables

Open tool

SQL Compare

Diff two schema or query scripts side by side

Open tool

MySQL to PostgreSQL

Convert DDL between dialects

Open tool

SQL Server to MySQL

Convert T-SQL schemas toward MySQL

Open tool

Continue Learning

Introduction to SQL

What SQL is and how queries reach the database

SELECT & FROM — Reading Data

Your first step into querying tables

Frequently Asked Questions

What is a relational database?

A relational database stores data in tables (relations) made of rows and columns. Relationships between tables are expressed with keys, especially foreign keys.

What is the difference between a database and a schema?

Terminology varies by vendor. In PostgreSQL, a database contains schemas, which contain tables. In MySQL, "database" and "schema" often mean the same thing. Focus on tables and keys first.

What is a primary key?

A primary key uniquely identifies each row in a table. It cannot be NULL and must be unique. Common choices: surrogate integer id, or natural keys like email when truly unique.

What is a foreign key?

A foreign key column references a primary key in another table, linking related rows. For example, orders.customer_id references users.id.

What does NULL mean in SQL?

NULL means unknown or missing — not zero, not empty string. Comparisons with NULL use IS NULL / IS NOT NULL, not = NULL.

Should I store lists in a comma-separated column?

Usually no. Normalize into a separate table (or JSON column when appropriate). Comma-separated lists break queries, constraints, and indexing.