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:

id	title	author	price	in_stock
1	The Great Gatsby	F. Scott Fitzgerald	12.99	true
2	To Kill a Mockingbird	Harper Lee	14.50	true
3	1984	George Orwell	11.99	false

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

Think of It This Way

A relational database is essentially a collection of connected spreadsheets with superpowers. Each spreadsheet is a table, each row in the spreadsheet is a record, and each column header defines a field. The "relational" part means these spreadsheets can reference and connect to each other.

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:

Real-World Entity	Table Name
A user	users
A product	products
An order	orders
A blog post	posts
A payment	payments

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)

Avoid Vague Names

-- Bad: What does this table store? What are these columns?
CREATE TABLE data (
    a INT,
    b VARCHAR(50),
    c VARCHAR(50),
    d VARCHAR(255),
    e VARCHAR(100),
    f DECIMAL(10, 2),
    g DATE,
    h BOOLEAN
);

-- Good: Clear, descriptive, and self-documenting
CREATE TABLE employees (
    id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(255),
    department VARCHAR(100),
    salary DECIMAL(10, 2),
    hire_date DATE,
    is_active BOOLEAN
);

A well-named table should tell you exactly what it contains without needing to look at its data.

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:

id	first_name	last_name	email	department	salary	hire_date	is_active
1	Alice	Johnson	alice@company.com	Engineering	95000.00	2021-03-15	true
2	Bob	Martinez	bob@company.com	Marketing	72000.00	2022-07-01	true
3	Carol	Singh	carol@company.com	Engineering	102000.00	2019-11-20	true
4	David	Chen	david@company.com	Sales	68000.00	2023-01-10	false

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:

Constraint	Purpose	Example
NOT NULL	The column must always have a value	first_name VARCHAR(50) NOT NULL
UNIQUE	No two rows can have the same value in this column	email VARCHAR(100) UNIQUE
DEFAULT	Provides a fallback value if none is specified	is_active BOOLEAN DEFAULT true
CHECK	Ensures values meet a specific condition	CHECK (salary > 0)
PRIMARY KEY	Uniquely identifies each row (combines NOT NULL + UNIQUE)	id INTEGER PRIMARY KEY
FOREIGN KEY	References a row in another table	FOREIGN KEY (dept_id) REFERENCES departments(id)

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.

Why Constraints Matter

Constraints act as safety nets for your data. Without them, nothing prevents someone from inserting an employee with a negative salary or two users with the same email address. Catching bad data at the database level is far more reliable than relying solely on application code.

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.

Data Type	Description	Example Values
INTEGER (or INT)	Whole numbers	1, 42, -100
SMALLINT	Smaller range whole numbers	1, 255
BIGINT	Very large whole numbers	9999999999
DECIMAL(p, s) / NUMERIC(p, s)	Exact decimal numbers (precision, scale)	99999.99, 3.14
FLOAT / REAL	Approximate floating-point numbers	3.14159, 2.718

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%
);

Common Mistake: Using FLOAT for Money

-- Wrong: FLOAT can cause rounding errors with financial data
CREATE TABLE orders (
    total FLOAT  -- Don't do this!
);

-- Correct: Use DECIMAL for precise monetary values
CREATE TABLE orders (
    total DECIMAL(10, 2)  -- Exact precision
);

FLOAT and REAL use approximate storage, which can lead to tiny rounding errors. For money, always use DECIMAL or NUMERIC.

Text/String Types

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

Data Type	Description	Example Values
VARCHAR(n)	Variable-length string, up to n characters	'Alice', 'Hello World'
CHAR(n)	Fixed-length string, always n characters	'US', 'NY'
TEXT	Variable-length string with no practical limit	Long descriptions, articles

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.

Data Type	Description	Example Values
DATE	Calendar date only	'2024-06-15'
TIME	Time of day only	'14:30:00'
TIMESTAMP	Date and time combined	'2024-06-15 14:30:00'
INTERVAL	A span of time (PostgreSQL)	'2 days', '3 hours'

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.

Data Type	Description	Example Values
BOOLEAN	True or false	true, false

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

Database Variations

Not all databases handle BOOLEAN the same way. PostgreSQL supports true/false natively. MySQL uses TINYINT(1) where 0 is false and 1 is true. SQLite does not have a dedicated boolean type and also uses 0 and 1. The concept is the same, but the implementation varies.

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
);

Common Mistake: Storing Phone Numbers as Integers

-- Wrong: Phone numbers are not "numbers" in the mathematical sense
CREATE TABLE contacts (
    phone INTEGER  -- Leading zeros get stripped! +1-800-555-0199 can't be stored!
);

-- Correct: Phone numbers are character strings
CREATE TABLE contacts (
    phone VARCHAR(20)  -- Preserves formatting, leading zeros, and special characters
);

A phone number like 0044 7911 123456 would lose the leading zero if stored as an integer. Phone numbers, zip codes, and similar identifiers should always be stored as VARCHAR.

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:

first_name	last_name	department
John	Smith	Engineering
John	Smith	Marketing
John	Smith	Engineering

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:

id	first_name	last_name	department
1	John	Smith	Engineering
2	John	Smith	Marketing
3	John	Smith	Engineering

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:

id	customer_name	total
1	Alice Johnson	149.99
2	Bob Martinez	89.50
3	Carol Singh	234.00

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)
);

Best Practice

For most tables, use a surrogate key (auto-incrementing integer or UUID) as the primary key. Natural keys can change over time (a person might change their email, a country might change its code), but surrogate keys remain stable forever. You can still enforce uniqueness on natural identifiers using the UNIQUE constraint.

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:

title	author	price	is_available
One Hundred Years of Solitude	Gabriel García Márquez	16.00	false
To Kill a Mockingbird	Harper Lee	14.50	true
1984	George Orwell	13.99	true
Animal Farm	George Orwell	9.99	true

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.