Relational Database Building Blocks

What “Relational” Means in Practice

A relational database stores data in a set of related tables. Each table represents one type of thing you want to keep track of (customers, orders, products), and relationships connect those tables so you can combine data when you query it. The “building blocks” are the parts you use to design, store, protect, and retrieve that data reliably.

When you build a relational database, you are mostly making decisions about: how to split data into tables, how to uniquely identify each row, how to connect rows across tables, which rules must always be true, and which structures make queries fast and safe.

Tables, Rows, and Columns

Tables

A table is a named container for data about a single subject. For example, a customers table stores customer information, and an orders table stores order information. A good rule of thumb is: if you find yourself repeating the same group of fields over and over, it probably deserves its own table.

Rows (Records)

Each row represents one instance of the subject. In customers, one row is one customer. In orders, one row is one order. Rows are what you insert, update, and delete.

Columns (Fields)

Columns define the attributes you store for each row. In customers, you might have first_name, last_name, email, and created_at. Columns have data types and constraints that define what values are allowed.

Continue in our app.

You can listen to the audiobook with the screen off, receive a free certificate for this course, and also have access to 5,000 other free online courses.

Or continue reading below...

Download the app

Practical example: a tiny schema

Here is a small set of tables you can use to understand the building blocks. The syntax is intentionally generic; different databases vary slightly, but the ideas are the same.

CREATE TABLE customers (  customer_id INTEGER PRIMARY KEY,  first_name  VARCHAR(50) NOT NULL,  last_name   VARCHAR(50) NOT NULL,  email       VARCHAR(255) UNIQUE,  created_at  TIMESTAMP NOT NULL); CREATE TABLE products (  product_id  INTEGER PRIMARY KEY,  product_name VARCHAR(100) NOT NULL,  unit_price  DECIMAL(10,2) NOT NULL); CREATE TABLE orders (  order_id    INTEGER PRIMARY KEY,  customer_id INTEGER NOT NULL,  order_date  DATE NOT NULL); CREATE TABLE order_items (  order_id   INTEGER NOT NULL,  product_id INTEGER NOT NULL,  quantity   INTEGER NOT NULL,  unit_price DECIMAL(10,2) NOT NULL,  PRIMARY KEY (order_id, product_id));

This example already hints at several building blocks: primary keys, foreign keys (not yet declared), data types, and constraints like NOT NULL and UNIQUE.

Keys: How Rows Are Identified and Connected

Primary Key (PK)

A primary key is a column (or set of columns) whose value uniquely identifies each row in a table. A primary key should be stable (not changing often) and should never be NULL. Most tables have a single-column primary key like customer_id or order_id.

Why it matters: without a reliable unique identifier, you cannot safely update or reference a specific row. Names, emails, and other “real-world” values can change or may not be unique.

Composite Primary Key

Sometimes uniqueness is naturally defined by a combination of columns. In order_items, the combination of (order_id, product_id) can uniquely identify a line item if you allow each product to appear at most once per order. That is a composite primary key.

Foreign Key (FK)

A foreign key is a column (or set of columns) in one table that references the primary key of another table. Foreign keys create relationships and enforce referential integrity: you cannot reference a row that does not exist.

Example relationships in the schema: orders.customer_id should reference customers.customer_id, and order_items.order_id should reference orders.order_id. Also, order_items.product_id should reference products.product_id.

ALTER TABLE orders  ADD CONSTRAINT fk_orders_customer  FOREIGN KEY (customer_id) REFERENCES customers(customer_id); ALTER TABLE order_items  ADD CONSTRAINT fk_items_order  FOREIGN KEY (order_id) REFERENCES orders(order_id); ALTER TABLE order_items  ADD CONSTRAINT fk_items_product  FOREIGN KEY (product_id) REFERENCES products(product_id);

Candidate Keys and Alternate Keys

A candidate key is any column (or set of columns) that could uniquely identify a row. One of them is chosen as the primary key; others can be enforced with UNIQUE constraints and are sometimes called alternate keys.

For example, if your business rules guarantee that email is unique per customer, then customers.email is a candidate key. You might still use customer_id as the primary key (because emails can change), while enforcing uniqueness on email.

Relationships: One-to-One, One-to-Many, Many-to-Many

One-to-One (1:1)

In a one-to-one relationship, each row in table A relates to at most one row in table B, and vice versa. This is less common, but useful when you want to split optional or sensitive data into a separate table.

Example: customers and customer_profiles where profile details are optional. You typically enforce 1:1 by making the foreign key in the second table also unique (or the primary key).

CREATE TABLE customer_profiles (  customer_id INTEGER PRIMARY KEY,  marketing_opt_in BOOLEAN NOT NULL,  bio VARCHAR(500),  FOREIGN KEY (customer_id) REFERENCES customers(customer_id));

One-to-Many (1:N)

In a one-to-many relationship, one row in table A can relate to many rows in table B. This is the most common relationship type.

Example: one customer can have many orders. That is represented by putting customer_id in orders as a foreign key.

Many-to-Many (M:N) and Junction Tables

In a many-to-many relationship, rows in table A can relate to many rows in table B, and rows in table B can relate to many rows in table A. Relational databases represent this using a junction table (also called a bridge or associative table).

Example: orders and products are many-to-many: one order contains many products, and one product can appear in many orders. The junction table is order_items, which also stores attributes of the relationship (quantity, unit price at time of purchase).

Data Types: Choosing the Right Kind of Storage

Every column has a data type. Data types matter because they control what values are valid, how much space is used, and how comparisons and sorting work.

Common categories

• Integer types: for counts and identifiers (e.g., INTEGER, BIGINT).
• Fixed/variable text: for names and descriptions (e.g., CHAR, VARCHAR, TEXT).
• Decimal/numeric: for money and precise values (e.g., DECIMAL(10,2)).
• Floating point: for scientific measurements where small rounding is acceptable (e.g., FLOAT, REAL).
• Date/time: for dates and timestamps (e.g., DATE, TIMESTAMP).
• Boolean: true/false values (e.g., BOOLEAN).
• Binary: raw bytes (e.g., BLOB), often used for files but frequently avoided in favor of storing files elsewhere and keeping only references in the database.

Practical guidance

Use DECIMAL for currency to avoid floating-point rounding issues. Use date/time types for time-based queries (like “orders in the last 30 days”). Use the smallest integer type that fits your expected range if your database supports multiple sizes, but do not over-optimize early; correctness and clarity come first.

Constraints: Rules That Protect Data Quality

Constraints are database-enforced rules that prevent invalid data from being stored. They are a major building block because they move data validation closer to the data itself, not just the application.

NOT NULL

NOT NULL means a value must be provided. Use it when a column is required for a valid row (for example, orders.order_date).

UNIQUE

UNIQUE ensures no two rows have the same value in a column (or set of columns). Use it for business rules like unique email addresses or unique product SKUs.

CHECK

CHECK enforces a condition. For example, quantities should be positive.

ALTER TABLE order_items  ADD CONSTRAINT chk_quantity_positive  CHECK (quantity > 0);

Some databases have limitations around CHECK enforcement or expression support, but the concept remains important: encode simple rules at the database level.

DEFAULT

DEFAULT provides a value when none is supplied. For example, you might default created_at to the current timestamp. The exact function name varies by database.

ALTER TABLE customers  ALTER COLUMN created_at SET DEFAULT CURRENT_TIMESTAMP;

Foreign key actions (ON DELETE / ON UPDATE)

Foreign keys can define what happens when the referenced row is updated or deleted. Common actions include:

• RESTRICT / NO ACTION: prevent deletion/update if dependent rows exist.
• CASCADE: automatically delete/update dependent rows.
• SET NULL: set the foreign key to NULL (requires the column to allow nulls).

Choosing these actions is a design decision. For example, you might restrict deleting a customer if they have orders, because orders are historical records. On the other hand, you might cascade-delete temporary child rows in some systems.

Normalization: Structuring Tables to Reduce Redundancy

Normalization is the practice of organizing data to reduce duplication and avoid update anomalies (situations where changing one fact requires changing it in many places). As a beginner, you can apply normalization as a set of practical habits rather than memorizing formal definitions.

Practical normalization habits

• Store each fact once: customer email belongs in customers, not repeated in orders.
• Separate repeating groups: order line items belong in order_items, not as multiple columns like product1, product2.
• Use lookup tables for controlled lists: if a column has a small set of allowed values that may grow or needs descriptions (like order status), consider a separate table.

Example: order status as a lookup table

CREATE TABLE order_statuses (  status_code VARCHAR(20) PRIMARY KEY,  status_name VARCHAR(50) NOT NULL); CREATE TABLE orders (  order_id    INTEGER PRIMARY KEY,  customer_id INTEGER NOT NULL,  order_date  DATE NOT NULL,  status_code VARCHAR(20) NOT NULL,  FOREIGN KEY (customer_id) REFERENCES customers(customer_id),  FOREIGN KEY (status_code) REFERENCES order_statuses(status_code));

This avoids inconsistent status spellings like “Shipped”, “shipped”, “SHIP”. It also lets you attach additional information to a status later (display order, whether it is final, etc.).

Indexes: Making Data Retrieval Fast

An index is a data structure that helps the database find rows faster, similar to an index in a book. Without an index, the database may need to scan many rows to find matches. Indexes are a building block because they are part of physical design: they don’t change what data means, but they strongly affect performance.

What to index first

• Primary keys: most databases automatically index primary keys.
• Foreign keys: indexing foreign key columns often speeds up joins and helps with referential integrity checks.
• Columns used in frequent filters: for example, orders.order_date if you often query by date ranges.

Example indexes

CREATE INDEX idx_orders_customer_id ON orders(customer_id); CREATE INDEX idx_orders_order_date ON orders(order_date); CREATE INDEX idx_items_product_id ON order_items(product_id);

Trade-offs

Indexes speed up reads but can slow down writes because the index must be updated when rows change. Avoid creating many indexes “just in case.” Start with the ones that support your most common queries.

Views: Saved Queries as Virtual Tables

A view is a stored query that behaves like a table when you select from it. Views are useful for simplifying complex joins, providing a stable interface to data, and restricting which columns users can see.

Example: an order summary view

CREATE VIEW order_summary AS SELECT  o.order_id,  o.order_date,  c.customer_id,  c.first_name,  c.last_name FROM orders o JOIN customers c ON c.customer_id = o.customer_id;

You can query it like a table:

SELECT * FROM order_summary WHERE order_date >= DATE '2026-01-01';

Some databases support materialized views (physically stored results) for performance, but a standard view is typically computed when queried.

Transactions: Keeping Changes Consistent

A transaction is a group of database operations treated as a single unit. Either all changes succeed, or none do. Transactions are essential when you need to insert/update multiple tables and keep them consistent.

Why transactions matter for relational design

Consider placing an order: you might insert into orders and then insert multiple rows into order_items. If the order is inserted but inserting an item fails, you would end up with an incomplete order. A transaction prevents that by rolling back everything if any step fails.

Step-by-step: inserting an order with items safely

BEGIN TRANSACTION; INSERT INTO orders (order_id, customer_id, order_date) VALUES (1001, 10, DATE '2026-01-13'); INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (1001, 501, 2, 19.99); INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (1001, 502, 1, 5.49); COMMIT;

If any statement fails (for example, a product_id that does not exist, or a quantity that violates a CHECK constraint), you would typically run:

ROLLBACK;

Exact transaction commands vary slightly across databases, but the concept is universal.

Schemas and Namespaces: Organizing Database Objects

A schema is a logical container for database objects like tables, views, and procedures. Some databases use “schema” as a namespace within a database (for example, sales.orders), while others treat it differently. The key idea is organization and separation.

Why use schemas

• Organization: group related tables (e.g., sales, hr).
• Security: grant access to a schema without granting access to everything.
• Avoid naming collisions: two teams can have an events table in different schemas.

When you reference objects, you may use a qualified name like sales.orders depending on your database.

Putting the Building Blocks Together: A Mini Design Walkthrough

This walkthrough shows how the building blocks work together when you design a small relational model for ordering products.

Step 1: Identify entities (tables)

• Customers: who buys.
• Products: what is sold.
• Orders: a purchase event.
• Order items: which products are in each order.

Step 2: Choose primary keys

• customers.customer_id
• products.product_id
• orders.order_id
• order_items uses composite PK (order_id, product_id)

Step 3: Add foreign keys to define relationships

• orders.customer_id references customers.customer_id (1:N)
• order_items.order_id references orders.order_id (1:N)
• order_items.product_id references products.product_id (1:N)

Step 4: Add constraints for correctness

• NOT NULL on required fields like names, dates, prices.
• UNIQUE on customers.email if required by business rules.
• CHECK (quantity > 0) to prevent invalid quantities.

Step 5: Add indexes to support common queries

• Index foreign keys for joins.
• Index date columns for date filtering.

Step 6: Create a view for a common read pattern

If your application frequently needs order headers with customer names, create a view to simplify queries and reduce repeated join logic.

CREATE VIEW orders_with_customer AS SELECT  o.order_id,  o.order_date,  o.customer_id,  c.first_name,  c.last_name,  c.email FROM orders o JOIN customers c ON c.customer_id = o.customer_id;

Step 7: Use transactions for multi-step changes

When inserting an order plus items, wrap the statements in a transaction so the database stays consistent even if something fails mid-way.