How Databases Work

Before learning the building blocks of a database, it helps to understand what a database is actually doing every time you interact with it. Most tutorials skip this and jump straight to CREATE TABLE. That creates a mental model of a database as a passive container — a box you put data into and take data out of. The reality is far more active, and understanding it will make every concept in this course easier to grasp.

Here is the complete journey of a single query — what happens in the milliseconds between you pressing Enter and the result appearing on your screen.

A table is the fundamental unit of storage in a relational database. Every piece of data you ever store lives in a table. Understanding what makes a good table — and what makes a bad one — is the most important design skill in all of SQL. Bad table design causes problems that no amount of clever querying can fix.

Rule 1: One table, one thing

The single most important rule in database design: each table stores exactly one type of entity. FreshMart has a customers table that stores only customers. A products table that stores only products. An orders table that stores only orders. You would never put customer data and product data in the same table, even though both are used when a customer buys a product.

When beginners design databases for the first time they often try to put everything in one table — one row per order, with the customer's name, address, and loyalty tier repeated in every single row. This is called data redundancy and it causes serious problems: if Aisha Khan moves from Bangalore to Hyderabad, you have to update her address in every single order row. Miss one row and your data is inconsistent. In a properly designed database you update her address in exactly one place — the customers table — and every order automatically reflects it through the foreign key relationship.

Rule 2: Every column stores exactly one piece of information

Each column must represent one atomic, indivisible piece of data. A column called address that stores "12 Koramangala, Bangalore, 560034" is a bad design — city, street, and pincode are three different pieces of information crammed into one column. When you later want to find all customers in Bangalore, you would have to use string pattern matching on the whole address, which is slow and error-prone. Correct design separates them: street, city, state, pincode — four columns, four clean pieces of data.

Rule 3: Table and column names must be clear, lowercase, and use underscores

The universal convention for SQL tables and columns: all lowercase letters, words separated by underscores, no spaces, no special characters. customers not Customers. order_date not OrderDate or orderDate. unit_price not UnitPrice or unitprice. This matters because SQL is case-sensitive in some databases for table names, and mixed-case names require quoting everywhere — adding noise to every query you write.

The FreshMart tables examined

When you create a table, every column must be given a data type — a declaration of what kind of data that column will hold. This is not just a label. The database uses the data type to: allocate the right amount of storage space, enforce that only valid values can be inserted, and choose the right comparison rules when you filter or sort.

Choosing the wrong data type is a mistake you cannot fix cheaply. Changing a column from VARCHAR(50) to VARCHAR(255) on a table with 500 million rows can take hours and lock the table. Getting it right at design time is worth the extra five minutes of thought.

The money mistake — why FLOAT is wrong for prices

This is one of the most common beginner mistakes. If you define a price column as FLOAT or DOUBLE, you will eventually get values like 28.499999999998 instead of 28.5. This happens because floating-point numbers are stored in binary and cannot represent most decimal fractions exactly — the same way 1/3 cannot be written as a finite decimal. For currency you must always use DECIMAL(precision, scale). In FreshMart, DECIMAL(10,2) means up to 10 total digits with exactly 2 decimal places — sufficient for prices up to ₹99,999,999.99.

VARCHAR vs CHAR — when to use which

VARCHAR(n) stores variable-length strings — it only uses as much space as the actual string needs, plus 1–2 bytes to record the length. A VARCHAR(100) column storing the word "Amul" uses 5 bytes, not 100. CHAR(n) stores fixed-length strings — it always uses exactly n bytes, padding shorter values with spaces. Use CHAR for values that are always the same length: country codes (IN, US), store IDs (ST001), status codes. Use VARCHAR for everything else.

A primary key is a column (or combination of columns) that uniquely identifies every row in a table. It is the most fundamental constraint in all of SQL. Every table you will ever create should have one.

What the database enforces automatically

When you declare a column as the PRIMARY KEY, the database automatically enforces two rules that you can never break: uniqueness — no two rows can have the same primary key value, and NOT NULL — the primary key column can never be empty. You do not need to write separate constraints for these. The PRIMARY KEY declaration implies both.

The database also automatically creates an index on the primary key column. This means lookups by primary key — the most common type of lookup in any application — are always fast, regardless of how large the table gets.

Auto-increment — let the database generate IDs

In almost every table you build, the primary key will be an auto-incrementing integer. This means the database generates the next value automatically whenever you insert a new row. In MySQL this is done with AUTO_INCREMENT. In PostgreSQL it is done with SERIAL or GENERATED ALWAYS AS IDENTITY. You never manually type a primary key value — the database handles it.

CREATE TABLE customers (
  customer_id  INTEGER       PRIMARY KEY AUTO_INCREMENT,
  first_name   VARCHAR(100)  NOT NULL,
  last_name    VARCHAR(100)  NOT NULL,
  email        VARCHAR(255)  NOT NULL UNIQUE,
  city         VARCHAR(100),
  loyalty_tier VARCHAR(20)   DEFAULT 'Bronze',
  joined_date  DATE          NOT NULL
);

CREATE TABLE customers (
  customer_id  INTEGER       PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  first_name   VARCHAR(100)  NOT NULL,
  last_name    VARCHAR(100)  NOT NULL,
  email        VARCHAR(255)  NOT NULL UNIQUE,
  city         VARCHAR(100),
  loyalty_tier VARCHAR(20)   DEFAULT 'Bronze',
  joined_date  DATE          NOT NULL
);

Composite primary keys — when one column is not enough

Sometimes a single column is not sufficient to uniquely identify a row, and you need a combination of two columns to serve as the primary key. This is called a composite primary key. The order_items table is a good example: item_id is a dedicated surrogate key here, but you could also uniquely identify each item by the combination of (order_id, product_id) — one order cannot contain the same product twice. Composite primary keys are common in junction tables (many-to-many relationships). In FreshMart we use a surrogate integer key for simplicity, but both approaches are correct.

A foreign key is a column in one table that stores the primary key value of a row in another table. It creates an enforced, permanent link between the two tables. Foreign keys are the mechanism that makes a database "relational" in practice — they are what allow you to ask questions that span multiple tables.

What referential integrity means

When you declare a foreign key, the database enforces a rule called referential integrity: every foreign key value must either match an existing primary key value in the referenced table, or be NULL. This has two practical consequences. First, you cannot insert an order with customer_id = 500 if no customer with id 500 exists — the database rejects the insert with an error. Second, you cannot delete a customer who still has orders — the database rejects the delete because it would leave orphaned orders pointing to nobody.

ON DELETE behaviour — what happens when the parent is deleted

You can control what happens to child rows when their parent is deleted by specifying a behaviour on the foreign key constraint:

The FreshMart foreign key map in code

-- orders → customers
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)

-- orders → stores
FOREIGN KEY (store_id) REFERENCES stores(store_id)

-- order_items → orders
FOREIGN KEY (order_id) REFERENCES orders(order_id)

-- order_items → products
FOREIGN KEY (product_id) REFERENCES products(product_id)

-- employees → stores
FOREIGN KEY (store_id) REFERENCES stores(store_id)

-- employees → employees (self-referencing: manager is also an employee)
FOREIGN KEY (manager_id) REFERENCES employees(employee_id)

Notice the last one: employees.manager_id references employees.employee_id — a table referencing itself. This is called a self-referencing foreign key and it is how org charts and hierarchy data are stored. Priya Sharma (employee_id = 1) is the Store Manager with no manager above her (manager_id = NULL). Rahul Verma (employee_id = 2) reports to Priya, so his manager_id = 1. You will learn to query this hierarchy using SELF JOINs in Module 34.

Constraints are rules you attach to columns (or tables) that the database checks on every INSERT and UPDATE. If a value violates a constraint, the operation fails with an error — the data is never saved. Constraints are your first line of defence against bad data. They are far more reliable than checking validity in application code, because application code can have bugs, can be bypassed, and does not cover every path data enters through.

NOT NULL — the column must always have a value

By default, any column can contain NULL — the absence of a value. Adding NOT NULL means the column must always have a real value. Inserting a row without providing a value for a NOT NULL column causes an error. In FreshMart, first_name, last_name, and order_date are NOT NULL — it makes no sense to have a customer without a name or an order without a date.

UNIQUE — no two rows can have the same value

The UNIQUE constraint prevents duplicate values in a column across all rows. In FreshMart, the email column on customers is UNIQUE — no two customers can share the same email address. Unlike PRIMARY KEY, a UNIQUE column can contain NULL (and multiple NULLs are allowed, since NULL is not equal to NULL in SQL — more on this surprising behaviour in Module 11).

DEFAULT — the value used when nothing is provided

The DEFAULT constraint specifies what value the database should use when a row is inserted without providing a value for that column. In FreshMart, loyalty_tier has a DEFAULT of 'Bronze' — when a new customer is added without specifying their tier, they automatically start at Bronze. Defaults keep INSERT statements cleaner and reduce the chance of NULL slipping into columns that should always have a value.

CHECK — custom rule for valid values

The CHECK constraint lets you define an arbitrary condition that every value in a column must satisfy. For example, discount_pct in order_items should always be between 0 and 100. A CHECK constraint enforces this: CHECK (discount_pct >= 0 AND discount_pct <= 100). If you try to insert a discount of 150 or -5, the database rejects it immediately.

CREATE TABLE orders (
  order_id       INTEGER        PRIMARY KEY AUTO_INCREMENT,
  customer_id    INTEGER        NOT NULL,
  store_id       VARCHAR(10)    NOT NULL,
  order_date     DATE           NOT NULL,
  delivery_date  DATE,                          -- nullable: NULL until delivered
  order_status   VARCHAR(20)    NOT NULL
                 DEFAULT 'Processing'
                 CHECK (order_status IN
                   ('Delivered','Processing','Cancelled','Returned')),
  payment_method VARCHAR(20)    NOT NULL
                 CHECK (payment_method IN
                   ('UPI','Card','COD','NetBanking')),
  total_amount   DECIMAL(10,2)  NOT NULL
                 CHECK (total_amount >= 0),

  FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
  FOREIGN KEY (store_id)    REFERENCES stores(store_id)
);

Now that you understand tables, data types, primary keys, foreign keys, and constraints — let us look at the full FreshMart schema in one place. You will use this schema for every query in every module. Read it carefully and notice how every design decision reflects the rules covered in this module.

Notice how the second query uses JOIN to combine orders with customer names. The order table only stores customer_id — a number. To get the actual name, you join to the customers table using the foreign key relationship. This is the relational model working as designed. You will learn exactly how to write JOINs — including multiple-table JOINs — in Modules 30 through 35.

You are hired as a junior backend developer at a Bangalore fintech startup. On your second week, you are asked to review a new table that a colleague designed and give feedback before it goes to production. Here is what that day looks like.