How Databases Work: Tables, Rows, and Columns Explained

Every application you use daily, from social media platforms to online banking, from e-commerce stores to streaming services, stores and retrieves data constantly. But have you ever wondered how databases actually work under the hood? Where does all that data live, and how is it organized so that applications can find exactly what they need in milliseconds?

This guide breaks down the fundamental building blocks of relational databases: tables, rows, and columns. You will learn what a relational database is, how data is structured inside it, what data types are available, and why primary keys are essential. By the end, you will have a solid mental model of how databases work that will serve you throughout your entire development career.

The Spreadsheet Analogy: Your Starting Point

Before diving into technical definitions, let us start with something familiar. If you have ever used Google Sheets or Microsoft Excel, you already understand the basic concept behind a relational database.

Imagine you run a small online bookstore. You might create a spreadsheet to track your books:

Now imagine you create another spreadsheet for your customers, another one for orders, and another for shipping details. Each spreadsheet has its own structure, and they reference each other. Your "orders" spreadsheet might point to specific customers and specific books.

A relational database works exactly like this, but with critical advantages:

• It can handle millions of rows without slowing down
• It enforces rules about what data is allowed
• It lets you query and combine data from multiple tables instantly
• It ensures data integrity even when thousands of users access it simultaneously
• It protects against data loss through transactions and backups

What Is a Relational Database?

A relational database is a type of database that organizes data into structured tables that can be linked to one another through defined relationships. The word "relational" comes from the mathematical concept of a relation, which in practical terms means a table of data.

Relational databases are managed by software called a Relational Database Management System (RDBMS). Popular examples include:

• PostgreSQL (open source, feature-rich)
• MySQL (open source, widely used in web development)
• SQLite (lightweight, file-based, great for learning)
• Microsoft SQL Server (enterprise-focused)
• Oracle Database (enterprise-focused)

You interact with all of these systems using SQL (Structured Query Language), which is the standard language for querying and managing relational databases.

The Core Idea

The fundamental principle of a relational database is straightforward: store related data in separate, well-organized tables and connect them through shared references. Instead of cramming everything into one giant spreadsheet, you split your data into logical groups.

For example, instead of repeating a customer's full name and address on every order row, you store customer information once in a customers table and simply reference the customer's ID in the orders table. This eliminates redundancy and keeps your data clean.

Tables: The Foundation of Every Database

A table is the primary structure where data lives in a relational database. Every table represents a single entity or concept in your application. Think of an entity as a "thing" you want to track.

Examples of entities and their corresponding tables:

Creating a Table

You create a table in SQL using the CREATE TABLE statement. Here you define the table's name, its columns, and the rules each column must follow:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE,
    is_active BOOLEAN DEFAULT true
);

This single statement tells the database: "Create a table called employees with these eight columns, each accepting a specific type of data, with these specific rules."

Naming Conventions

While databases allow various naming styles, widely accepted conventions include:

• Use lowercase for table and column names
• Use snake_case (underscores between words) rather than camelCase
• Name tables as plurals (e.g., users, not user)
• Choose descriptive, meaningful names (e.g., hire_date instead of hd)

Rows: Individual Records of Data

A row (also called a record or tuple) represents a single entry in a table. Each row contains one complete set of information about the entity the table represents.

If the employees table describes the concept of an employee, then each row describes one specific employee.

Inserting Rows

You add rows to a table using the INSERT INTO statement:

INSERT INTO employees (id, first_name, last_name, email, department, salary, hire_date, is_active)
VALUES (1, 'Alice', 'Johnson', 'alice@company.com', 'Engineering', 95000.00, '2021-03-15', true);

INSERT INTO employees (id, first_name, last_name, email, department, salary, hire_date, is_active)
VALUES (2, 'Bob', 'Martinez', 'bob@company.com', 'Marketing', 72000.00, '2022-07-01', true);

INSERT INTO employees (id, first_name, last_name, email, department, salary, hire_date, is_active)
VALUES (3, 'Carol', 'Singh', 'carol@company.com', 'Engineering', 102000.00, '2019-11-20', true);

INSERT INTO employees (id, first_name, last_name, email, department, salary, hire_date, is_active)
VALUES (4, 'David', 'Chen', 'david@company.com', 'Sales', 68000.00, '2023-01-10', false);

Viewing Rows

After inserting data, you can retrieve it using a SELECT query:

SELECT * FROM employees;

Output:

Each row is a complete record. Row 1 tells you everything you need to know about Alice Johnson: her name, email, department, salary, when she was hired, and whether she is currently active.

Key Characteristics of Rows

• Each row should represent one and only one instance of the entity
• The order of rows does not matter in a relational database (you control order with ORDER BY when querying)
• Each row should be uniquely identifiable (more on this in the Primary Keys section)

Columns: Defining the Shape of Your Data

A column (also called a field or attribute) defines a specific piece of information that every row in the table must have. Columns determine the structure of the table, meaning what kind of data it holds.

In the employees table, the columns are: id, first_name, last_name, email, department, salary, hire_date, and is_active. Every single employee row must have values for these columns (unless the column allows NULL, which means "no value").

Column Constraints

When defining columns, you can apply constraints to enforce rules about the data:

Here is a table definition using multiple constraints:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    sku VARCHAR(20) UNIQUE NOT NULL,
    price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
    stock_quantity INTEGER DEFAULT 0 CHECK (stock_quantity >= 0),
    category VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

This definition ensures that every product must have a name and SKU, no two products can share the same SKU, prices must be positive numbers, and stock quantities can never go negative.

Data Types: What Kind of Data Each Column Holds

Every column in a table has a data type that determines what values it can store. Data types serve two important purposes: they validate incoming data and help the database optimize storage and performance.

Here is a practical overview of the most common data types you will encounter.

Numeric Types

Used for numbers, whether whole numbers or decimals.

CREATE TABLE financial_records (
    id INTEGER PRIMARY KEY,
    transaction_amount DECIMAL(12, 2),  -- Up to 12 digits, 2 after decimal
    quantity INTEGER,
    tax_rate DECIMAL(5, 4)              -- e.g., 0.0825 for 8.25%
);

Text/String Types

Used for storing words, sentences, and any character-based data.

CREATE TABLE users (
    username VARCHAR(30),      -- Short, limited-length text
    country_code CHAR(2),      -- Always exactly 2 characters: 'US', 'UK', 'DE'
    bio TEXT                   -- Can be any length
);

When to use which:

• Use VARCHAR(n) for most text fields where you know the approximate maximum length
• Use CHAR(n) for fixed-length codes like country codes, state abbreviations, or currency codes
• Use TEXT for long-form content where imposing a length limit does not make sense

Date and Time Types

Used for storing temporal information.

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    event_name VARCHAR(100),
    event_date DATE,
    start_time TIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO events (id, event_name, event_date, start_time)
VALUES (1, 'Tech Conference', '2024-09-20', '09:00:00');

Boolean Type

Used for true/false values.

CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,
    title VARCHAR(200),
    is_completed BOOLEAN DEFAULT false
);

Choosing the Right Data Type

Selecting appropriate data types is an important decision that impacts data integrity, storage efficiency, and query performance. Here are some practical guidelines:

-- A well-designed table with appropriate data types
CREATE TABLE customers (
    id INTEGER PRIMARY KEY,              -- Whole number for unique ID
    first_name VARCHAR(50) NOT NULL,     -- Names rarely exceed 50 chars
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,  -- Email standard max is ~254 chars
    phone VARCHAR(20),                   -- Phone stored as text (preserves leading zeros, formatting)
    date_of_birth DATE,                  -- Date only, no time needed
    account_balance DECIMAL(12, 2),      -- Exact decimal for money
    is_verified BOOLEAN DEFAULT false,   -- Simple yes/no flag
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Primary Keys: Uniquely Identifying Every Row

A primary key is a column (or combination of columns) that uniquely identifies each row in a table. It is the single most important concept for maintaining data integrity in a relational database.

Why Primary Keys Are Essential

Consider this table without a primary key:

Which John Smith do you want to update? The one in Engineering? But there are two of them! Without a unique identifier, there is no reliable way to target a specific row.

Now add a primary key:

Now every row is unmistakably unique. You want to update employee id = 3? Done. No ambiguity.

-- This targets exactly one row, guaranteed
UPDATE employees SET department = 'Sales' WHERE id = 3;

Rules of Primary Keys

Every primary key must follow these rules:

1. Uniqueness: No two rows can have the same primary key value
2. Not NULL: A primary key can never be empty
3. Immutable (best practice): Once assigned, a primary key should not change
4. Single per table: Each table can have only one primary key (though it can span multiple columns)

Auto-Incrementing Primary Keys

Manually assigning ID numbers is impractical. Most databases support auto-incrementing, where the database automatically assigns the next number:

-- PostgreSQL
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100),
    total DECIMAL(10, 2)
);

-- MySQL
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100),
    total DECIMAL(10, 2)
);

-- SQLite
CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_name VARCHAR(100),
    total DECIMAL(10, 2)
);

With auto-increment, you can insert rows without specifying the id:

INSERT INTO orders (customer_name, total) VALUES ('Alice Johnson', 149.99);
INSERT INTO orders (customer_name, total) VALUES ('Bob Martinez', 89.50);
INSERT INTO orders (customer_name, total) VALUES ('Carol Singh', 234.00);

SELECT * FROM orders;

Output:

The database assigned 1, 2, and 3 automatically.

Natural Keys vs Surrogate Keys

There are two philosophies for choosing primary keys:

Natural Key: Using a real-world value that is naturally unique.

CREATE TABLE countries (
    country_code CHAR(2) PRIMARY KEY,  -- 'US', 'UK', 'DE' naturally unique
    country_name VARCHAR(100)
);

Surrogate Key: Using an artificial value (like an auto-incremented integer) with no real-world meaning.

CREATE TABLE countries (
    id INTEGER PRIMARY KEY,          -- Artificial, but simple and reliable
    country_code CHAR(2) UNIQUE,
    country_name VARCHAR(100)
);

Putting It All Together: A Complete Example

Let us build a small but realistic database schema for an online bookstore. This example ties together everything you have learned: tables, rows, columns, data types, primary keys, and constraints.

-- Authors table
CREATE TABLE authors (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    country VARCHAR(50),
    born_year INTEGER
);

-- Books table (references authors)
CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author_id INTEGER NOT NULL,
    price DECIMAL(8, 2) NOT NULL CHECK (price > 0),
    published_date DATE,
    isbn VARCHAR(13) UNIQUE,
    is_available BOOLEAN DEFAULT true,
    FOREIGN KEY (author_id) REFERENCES authors(id)
);

-- Customers table
CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    joined_date DATE DEFAULT CURRENT_DATE
);

Now insert some data:

INSERT INTO authors VALUES (1, 'George Orwell', 'United Kingdom', 1903);
INSERT INTO authors VALUES (2, 'Harper Lee', 'United States', 1926);
INSERT INTO authors VALUES (3, 'Gabriel García Márquez', 'Colombia', 1927);

INSERT INTO books VALUES (1, '1984', 1, 13.99, '1949-06-08', '9780451524935', true);
INSERT INTO books VALUES (2, 'Animal Farm', 1, 9.99, '1945-08-17', '9780451526342', true);
INSERT INTO books VALUES (3, 'To Kill a Mockingbird', 2, 14.50, '1960-07-11', '9780061120084', true);
INSERT INTO books VALUES (4, 'One Hundred Years of Solitude', 3, 16.00, '1967-05-30', '9780060883287', false);

INSERT INTO customers VALUES (1, 'Emily', 'Davis', 'emily@email.com', '2024-01-15');
INSERT INTO customers VALUES (2, 'James', 'Wilson', 'james@email.com', '2024-03-22');

And query across tables:

SELECT b.title, a.name AS author, b.price, b.is_available
FROM books b
JOIN authors a ON b.author_id = a.id
ORDER BY b.price DESC;

Output:

Notice how the author_id column in the books table connects to the id column in the authors table. George Orwell's information is stored once in the authors table, but it can appear alongside any number of his books. This is the power of relational databases: no data duplication, full data connectivity.

Summary

Understanding how databases work at a structural level is foundational knowledge for any developer. Here is a recap of the key concepts covered in this guide:

• A relational database is a collection of structured, interconnected tables, much like a set of connected spreadsheets with built-in rules and superpowers
• Tables represent entities (users, products, orders) and define the structure of your data
• Rows are individual records, each representing one instance of the entity
• Columns define the specific attributes each record has, with enforced data types and constraints
• Data types (INTEGER, VARCHAR, DECIMAL, DATE, BOOLEAN, and others) control what values a column can hold and ensure data quality
• Primary keys uniquely identify every row, making it possible to reference, update, and delete specific records without ambiguity

These building blocks are universal across all relational database systems. Whether you are working with PostgreSQL, MySQL, SQLite, or any other RDBMS, the concepts of tables, rows, columns, data types, and primary keys work the same way. Master these fundamentals, and you will have the solid foundation needed to tackle more advanced topics like joins, indexes, transactions, and query optimization.