Databases Explained Using a Kiosk Ledger

Mama Njeri runs a kiosk in Umoja. She sells bread, milk, airtime, and household items. Some customers pay cash. Others buy on credit and pay at the end of the month. To keep track, she has a thick exercise book. Her ledger.

The first few pages list her customers. One line per customer: name, phone number, and how much they owe. "Mary Wanjiku, 0722-xxx-xxx, KES 350." "John Otieno, 0711-xxx-xxx, KES 0." Every customer gets one line.

The next section lists her products. One line per product: item name, buying price, selling price, and how many she has in stock. "Bread, KES 50, KES 60, 12 loaves." "Milk 500ml, KES 45, KES 55, 8 packets."

Another section records daily sales. Each line: date, customer name, items bought, amount, and whether it was cash or credit. "15/07, Mary, 2 bread + 1 milk, KES 175, credit."

That exercise book is a database. It is organized, structured, and queryable (Mama Njeri can flip to the customer page and find who owes her money). It has tables (customer list, product list, sales records). It has rows (individual entries). It has columns (name, phone, amount owed). The only difference between this and a digital database is scale and speed. Mama Njeri can look up 50 customers. PostgreSQL can look up 50 million in milliseconds.

A database organizes information into tables. Each table stores one type of thing. Mama Njeri's ledger has a customer table, a product table, and a sales table. In a digital database, these are literally called tables.

Each table has columns that define what information is stored. The customer table has columns for: name, phone_number, balance_owed. Every customer record has these same fields. You define the columns when you create the table, and they stay consistent.

Each table has rows that are individual records. Each row is one customer, one product, or one sale. When Mary buys bread on credit, Mama Njeri adds a new row to the sales table. When a new customer starts buying on credit, she adds a row to the customer table.

Here is what the customer table looks like in database terms:

| id | name          | phone        | balance_owed |
|----|---------------|--------------|-------------|
| 1  | Mary Wanjiku  | 0722-xxx-xxx | 350         |
| 2  | John Otieno   | 0711-xxx-xxx | 0           |
| 3  | Grace Achieng | 0733-xxx-xxx | 1200        |
| 4  | Peter Kamau   | 0700-xxx-xxx | 500         |

The id column is special. Every row gets a unique number. This is the row's identity. Even if two customers have the same name, their IDs are different. In the sales table, you record "customer_id: 1" instead of "Mary Wanjiku." This links the sale to the correct customer without ambiguity.

This structure (tables with defined columns and linked by IDs) is what makes relational databases relational. Tables relate to each other through these ID references.

SQL (Structured Query Language, pronounced "sequel") is the language you use to talk to the database. It is not a programming language like JavaScript or Python. It is a query language: you ask questions, and the database answers.

Reading data (SELECT):

-- Show all customers
SELECT * FROM customers;

-- Show only customers who owe money
SELECT name, balance_owed FROM customers WHERE balance_owed > 0;

-- Show the customer who owes the most
SELECT name, balance_owed FROM customers ORDER BY balance_owed DESC LIMIT 1;

Adding data (INSERT):

-- Add a new customer
INSERT INTO customers (name, phone, balance_owed)
VALUES ('Janet Muthoni', '0745-xxx-xxx', 0);

Updating data (UPDATE):

-- Mary paid KES 200 of her debt
UPDATE customers SET balance_owed = 150 WHERE id = 1;

Removing data (DELETE):

-- Remove a customer who moved away
DELETE FROM customers WHERE id = 4;

These four operations, SELECT, INSERT, UPDATE, DELETE, cover 90% of what you do with a database daily. The syntax is close to English: SELECT what you want FROM which table WHERE some condition is true. It reads almost like a sentence.

Advanced SQL includes joining tables (combining data from two tables in a single query), grouping (calculating totals, averages, counts), and subqueries (queries within queries). You will learn these as you build more complex applications, but the four basic operations get you very far.

The real power of databases comes from connecting tables together. In Mama Njeri's ledger, the sales section references customer names from the customer section. In a database, this connection is formal and precise.

Consider these two tables:

customers table:
| id | name         | phone        |
|----|--------------|--------------|
| 1  | Mary Wanjiku | 0722-xxx-xxx |
| 2  | John Otieno  | 0711-xxx-xxx |

sales table:
| id | customer_id | product     | amount | date       |
|----|-------------|-------------|--------|------------|
| 1  | 1           | Bread x2    | 120    | 2026-07-15 |
| 2  | 1           | Milk x1     | 55     | 2026-07-15 |
| 3  | 2           | Airtime     | 100    | 2026-07-16 |
| 4  | 1           | Sugar 1kg   | 180    | 2026-07-17 |

The customer_id column in the sales table references the id column in the customers table. This is called a foreign key. It links each sale to the customer who made it.

Now you can ask powerful questions that span both tables:

-- How much has Mary spent in total?
SELECT SUM(amount) FROM sales WHERE customer_id = 1;
-- Result: 355

-- Show all sales with customer names
SELECT customers.name, sales.product, sales.amount
FROM sales
JOIN customers ON sales.customer_id = customers.id;
-- Result:
-- Mary Wanjiku  | Bread x2  | 120
-- Mary Wanjiku  | Milk x1   | 55
-- John Otieno   | Airtime   | 100
-- Mary Wanjiku  | Sugar 1kg | 180

The JOIN operation combines data from two tables based on a matching condition. This is what Mama Njeri does mentally when she looks at a sale and then flips to the customer page to update the balance. The database does it in milliseconds, across millions of records.

There are many database systems. For a Kenyan developer, here are the ones that matter and when to use each.

PostgreSQL. The standard for serious applications in Kenya. Used by fintech companies, banks, and startups handling financial data. Relational (tables, rows, columns, SQL). Extremely reliable with strong data consistency guarantees (your transaction either completes fully or not at all, no halfway state). If you learn one database, learn PostgreSQL.

MySQL. Another relational database. Very similar to PostgreSQL in basics. Used by older applications and some hosting platforms. Many shared web hosts (like those used by Kenyan agencies for WordPress sites) run MySQL by default. If a job listing says MySQL, your PostgreSQL skills transfer directly. The SQL is almost identical.

SQLite. A lightweight relational database that runs as a single file on your computer. No separate server needed. Perfect for development, testing, and small applications (mobile apps, desktop tools). Not suitable for production web applications with many concurrent users, but excellent for learning SQL without setting up a database server.

MongoDB. A non-relational (NoSQL) database. Instead of tables with fixed columns, data is stored as flexible JSON-like documents. Popular for rapid prototyping because you can store data without defining a strict schema first. Less common in Kenyan fintech (financial data needs strict consistency), but used by some startups for content management and social features.

Redis. An in-memory data store used for caching and temporary data. Not a primary database but often used alongside PostgreSQL. Stores session data, caches frequently accessed data (like product prices that do not change often), and handles real-time features (like online user counts). Fast because everything is in memory, but data can be lost if the server restarts.

For learning and for the Kenyan job market: start with PostgreSQL. It covers the widest range of job opportunities and teaches you SQL fundamentals that work across all relational databases.

To make this concrete, here is what the database for a simple M-Pesa-integrated ordering app looks like.

-- Users table
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  phone VARCHAR(15) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Products table
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price INTEGER NOT NULL, -- in KES
  in_stock BOOLEAN DEFAULT true
);

-- Orders table
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  total INTEGER NOT NULL,
  status VARCHAR(20) DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT NOW()
);

-- Payments table
CREATE TABLE payments (
  id SERIAL PRIMARY KEY,
  order_id INTEGER REFERENCES orders(id),
  mpesa_receipt VARCHAR(20),
  amount INTEGER NOT NULL,
  status VARCHAR(20) DEFAULT 'pending',
  paid_at TIMESTAMP
);

Four tables. Users, products, orders, and payments. The orders table links to users (who placed the order). The payments table links to orders (which order was paid for). When an M-Pesa callback comes in, you update the payment status and the order status. When a user views their order history, you query orders joined with payments for their user ID.

This small schema supports a complete ordering flow. It can track what was ordered, who ordered it, whether they paid, and when. It is the foundation of every e-commerce, food delivery, and service booking app in Kenya.

Our Full-Stack Software and AI Engineering course (KES 120,000) includes database design and PostgreSQL as core curriculum. You design schemas for real projects, not textbook exercises.