Relational Databases Explained: Tables, Rows, and Keys

Relational databases power everything from e-commerce checkouts to bank ledgers. The core ideas are simpler than they sound: data lives in tables, each row represents a real thing (a user, an order, a product), and keys are how we uniquely identify rows and connect tables safely.

This guide explains the building blocks - tables, rows, columns, and keys - with practical SQL examples and a realistic mini schema you can reuse.

If you’re following along as a learning path, the Database Fundamentals series is the “pillar” for this topic, and the Database tag is a good way to browse related posts.

Table of Contents

What Is a Relational Database?

A relational database (often called an RDBMS) stores data in tables with a defined structure (a schema). The “relational” part means we can reliably connect tables using relationships - typically via primary keys and foreign keys.

SQL (Structured Query Language) is the most common way to query and modify data in these databases, but the bigger idea is: the database enforces rules so your data stays consistent as your app grows.

Why Relational Databases Matter

Relational databases are popular because they’re good at the “boring but critical” parts of software:

• Data integrity: constraints prevent impossible states (like an order referencing a non-existent customer)
• Powerful querying: joins + aggregations make reporting and analytics straightforward
• Transactional correctness: ACID-style transactions help keep multi-step updates safe
• Mature tooling: backups, replication, migrations, monitoring, and decades of hard-won operational experience

When your app starts to outgrow a single database server, these concepts also connect directly to scaling patterns like database sharding and the trade-offs between vertical vs horizontal sharding. In many production systems, a distributed cache is also used to reduce database load for read-heavy traffic.

Tables: The Foundation of Relational Databases

A table is a named collection of rows that all share the same set of columns. You can think of it like a spreadsheet, except:

• Columns have types (so “price” can’t accidentally become “hello”)
• Columns can have constraints (like NOT NULL or UNIQUE)
• Tables can be connected with keys (relationships the database can enforce)

Each table usually maps to a single entity in your system: customers, orders, products, and so on.

Table Structure

Here’s a simple example of what a customers table might look like:

Table Naming Conventions

• Use lowercase letters
• Use singular nouns (customer, not customers)
• Use underscores for multi-word names (user_profile)
• Be descriptive but concise

Rows and Columns: Storing Data

Rows (Records/Tuples)

Each row is one “thing” you’re storing. In a customers table, a single row is one customer.

One helpful mental model:

• Table = a collection (all customers)
• Row = one item (one customer)
• Primary key = that item’s ID

Columns (Fields/Attributes)

Columns define what you store about each row. Every column should have:

• A name (email, created_at)
• A type (VARCHAR, INT, TIMESTAMP)
• Optional constraints (rules the database will enforce)

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

If you try to insert a customer without an email, the database rejects it immediately. That’s not the database being strict for fun - it’s preventing your application from accumulating messy, half-valid data over time.

Common Data Types

Primary Keys: Unique Identifiers

A primary key is a column (or combination of columns) that uniquely identifies each row in a table. No two rows can have the same primary key value.

Characteristics of Primary Keys

1. Unique: Each value appears only once
2. Not Null: Cannot contain NULL values
3. Immutable: Should not change once assigned
4. Minimal: Use the smallest number of columns possible

Types of Primary Keys

Natural Keys

Based on “real-world” identifiers:

• Product SKU
• Country code (US, IN, JP)

Natural keys can work well when the value is truly stable. In many apps, values like email addresses can change, which makes them risky as primary keys.

Surrogate Keys

Artificially created identifiers, usually auto-incrementing integers:

• Auto-incrementing ID columns
• UUIDs (Universally Unique Identifiers)

In practice, most teams pick surrogate keys for primary keys and then enforce “human identifiers” (like email) with a UNIQUE constraint.

-- Surrogate primary key (recommended)
CREATE TABLE customers (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE
);

-- Natural primary key (use with caution)
CREATE TABLE countries (
  country_code CHAR(2) PRIMARY KEY,
  country_name VARCHAR(100) NOT NULL
);

Foreign Keys: Creating Relationships

Foreign keys create relationships between tables by referencing a primary key in another table. This is how the database helps you keep data consistent.

How Foreign Keys Work

CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  order_date DATE,
  total_amount DECIMAL(10,2),
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

In this example:

• customer_id in the orders table references id in the customers table
• Each order must belong to an existing customer
• Depending on your configuration, the database can block deleting customers who still have orders (or you can opt into cascading deletes carefully)

In real systems, it’s common to define explicit behavior:

-- Example options (syntax varies by database)
FOREIGN KEY (customer_id) REFERENCES customers(id)
  ON DELETE RESTRICT
  ON UPDATE CASCADE

Relationship Types

One-to-One

One record in Table A relates to one record in Table B:

• Person → Passport
• Employee → Employee Details

One-to-Many

One record in Table A relates to many records in Table B:

• Customer → Orders (one customer, many orders)
• Author → Books (one author, many books)

Many-to-Many

Records in Table A relate to multiple records in Table B and vice versa:

• Students ↔ Courses (students take many courses, courses have many students)
• Products ↔ Categories (products belong to many categories, categories contain many products)

Database Schema: The Big Picture

A database schema is the blueprint of your database. It includes:

• Tables and columns (structure)
• Keys and relationships (connections)
• Constraints (rules)
• Indexes (performance)

In day-to-day engineering, “schema work” usually shows up as migrations: adding a column, tightening constraints, introducing a new table, or indexing a query that got slow.

Schema Design Process

1. Identify Entities: What things need to be stored?
2. Define Attributes: What information describes each entity?
3. Establish Relationships: How do entities relate to each other?
4. Apply Normalization: Reduce data redundancy
5. Add Constraints: Ensure data integrity

Entity-Relationship Diagram (ERD)

flowchart TD
  C[customers]
  O[orders]
  OI[order_items]
  P[products]
  CAT[categories]

  C -->|"1 customer places many orders"| O
  O -->|"1 order has many order_items"| OI
  P -->|"1 product appears in many order_items"| OI
  CAT -->|"1 category has many products"| P

Real-World Example: E-commerce Database

Let’s sketch a small e-commerce schema that supports customers, products, orders, and order line items.

This isn’t “the one true schema” - it’s a realistic starting point that keeps relationships explicit.

-- Customers
CREATE TABLE customers (
  id INT PRIMARY KEY AUTO_INCREMENT,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Categories
CREATE TABLE categories (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  description TEXT
);

-- Products
CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  price DECIMAL(10,2) NOT NULL,
  stock_quantity INT DEFAULT 0,
  category_id INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- Orders
CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  total_amount DECIMAL(10,2) NOT NULL,
  status ENUM('pending', 'processing', 'shipped', 'delivered') DEFAULT 'pending',
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- Order items (each row is one product on one order)
CREATE TABLE order_items (
  id INT PRIMARY KEY AUTO_INCREMENT,
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

Once you have relationships, you can query across them. For example: show order totals with customer info.

SELECT
  o.id AS order_id,
  o.order_date,
  o.total_amount,
  c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
ORDER BY o.order_date DESC;

This schema handles:

• Customer management
• Product catalog with categories
• Order processing
• Order line items

Common Relational Database Systems

Popular RDBMS Options

1. MySQL: Open-source, widely used for web applications
2. PostgreSQL: Advanced open-source with rich features
3. SQLite: Lightweight, file-based database
4. Microsoft SQL Server: Enterprise-grade commercial database
5. Oracle Database: High-performance commercial database

Choosing the Right RDBMS

• MySQL: very common in web stacks, simple to operate, lots of hosting options
• PostgreSQL: great defaults, strong data integrity features, excellent for complex querying
• SQLite: perfect for local apps, prototypes, embedded use, or small services
• SQL Server / Oracle: common in enterprise environments with integrated tooling and support

Interview Questions

1. What is a relational database?

A relational database stores data in tables with a schema and uses relationships (via keys) to connect tables. The database enforces constraints (like UNIQUE or foreign keys) so your data remains consistent even when multiple parts of the application write concurrently.

2. What’s the difference between a primary key and a foreign key?

A primary key uniquely identifies a row inside its own table (for example, customers.id). A foreign key is a column that points to another table’s primary key (for example, orders.customer_id → customers.id). Foreign keys help prevent “dangling references” (orders that point to customers that don’t exist).

3. Why do we use surrogate keys instead of natural keys?

Surrogate keys (like auto-increment IDs or UUIDs) are stable and don’t change, while natural identifiers often change in real life (email updates, product codes reissued, etc.). Surrogate keys also tend to produce smaller, faster indexes and simpler joins.

4. What are the different types of relationships in relational databases?

The main relationship types are:

• One-to-One: One record relates to exactly one record in another table
• One-to-Many: One record relates to many records in another table
• Many-to-Many: Records in both tables can relate to multiple records in the other table (requires a junction table)

5. What is database normalization?

Normalization is a design approach that reduces duplication and makes updates safer. Instead of repeating the same data in many places (which leads to inconsistencies), you split data into related tables and connect them with keys. The trade-off is you may need more joins to reassemble the full view.

6. Why are foreign key constraints important?

Foreign keys are a guardrail. They stop your database from drifting into impossible states (like an order row pointing to a deleted customer). They also make bugs loud: instead of silently writing inconsistent data, the database throws an error immediately.

Conclusion

Relational databases are built on a few core ideas: tables hold structured data, rows represent entities, and keys connect those entities safely. Once you understand primary keys and foreign keys, most “database design” questions become much easier to reason about.

If you’re building your first schema, start small, add constraints early, and let the database enforce rules wherever it can. It’s one of the easiest ways to keep your system clean as your app evolves.

References

1. PostgreSQL Documentation: Table Basics
2. MySQL Documentation: Creating Tables
3. Database Design Fundamentals

YouTube Videos

1. “Relational Databases Explained” - freeCodeCamp [https://www.youtube.com/watch?v=OqjJjpRwtLM]

2. “Database Keys | Primary Key, Foreign Key, Candidate Key, Super Key” - GeeksforGeeks [https://www.youtube.com/watch?v=8KrrXTjFvJ4]

3. “SQL Tutorial - 9: Create Table Statement” - ProgrammingKnowledge [https://www.youtube.com/watch?v=9JuD7T2XkN0]