<!-- https://learnsql.cpetoday.com/analyze/how-databases-work -->
# How databases work, for finance professionals

Your billing system, your bank feed and your general ledger are all databases. QuickBooks, Sage and most ERP systems keep their data in tables, and every report you run is a question asked of those tables. This guide explains the parts you touch every week: tables, keys, relationships, journal entries, transactions, indexes and views.

The examples use a CPA firm's invoices, payments and general ledger from the `cpafirm` sample database, because the shapes are the same as in a small business or a controller's ledger. Every query was run against the LearnSQL sample data. Each block says which database it runs on. The queries use SQL Server (T-SQL), and the MariaDB differences are listed where they matter. For the query syntax itself, read [SQL essentials](/analyze/sql-essentials) next.

## A table is a list with fixed columns

A table holds one kind of thing. Each row is one item, and each column is one field of that item. The `invoices` table has one row per invoice, and every row has the same columns. Three real rows look like this:

| invoice_number | client | issue_date | due_date | status | total_amount |
|---|---|---|---|---|---|
| INV-2025-03477 | C-10001 Kim, Jennifer | 2025-10-08 | 2025-11-07 | Paid | 475.00 |
| INV-2026-03847 | C-10207 Morris, Maria & Joseph | 2026-10-21 | 2026-11-20 | Sent | 945.00 |
| INV-2026-03848 | C-10171 Young, Paul | 2026-10-22 | 2026-11-21 | Sent | 868.75 |

Every column has a data type, and the type decides what the database lets you do with it:

- **Whole numbers** (`client_id`, `invoice_id`) are for identifiers and counts.
- **Text** (`invoice_number`, `status`) is for names and codes.
- **Decimals** (`total_amount`) keep money exact to the cent. Store money as a decimal, not a floating-point number, which can drift by fractions of a cent.
- **Dates** (`issue_date`) sort, subtract and filter correctly. A date stored as text does not. In text, `10/02/2026` sorts before `09/15/2026`, and the database cannot count the days between two text values.

## Keys: how rows point at each other

A **primary key** is the column that identifies one row. In `invoices` it is `invoice_id`. The invoice number is unique too, but the database uses the id, because a number a person types can be corrected later and every reference would have to change with it.

A **foreign key** is a column that holds another table's primary key. `invoices.client_id` holds the `client_id` of a row in `clients`. The database can enforce the link, so an invoice cannot point at a client who does not exist.

The next query takes one invoice and follows its two foreign keys to the client and the invoice lines. Runs on `cpafirm`.

```sql
SELECT i.invoice_number, c.client_name, i.total_amount,
       il.line_number, il.description, il.amount
FROM invoices AS i
JOIN clients AS c       ON c.client_id = i.client_id
JOIN invoice_lines AS il ON il.invoice_id = i.invoice_id
WHERE i.invoice_number = 'INV-2026-03848'
ORDER BY il.line_number;
```

**What it returns.** Two rows, one per line. The invoice total is 868.75, and the two lines add to it: 525.00 for a 1040 preparation and 343.75 for two hours of tax planning. The client name appears on both rows because it comes from one row in `clients`. The database did not copy it.

## Why an ERP is many tables and not one big sheet

Picture the whole billing history in one spreadsheet. Each invoice row repeats the client's name, street address, city and email. Then the trouble starts:

- A client moves offices. You must change every invoice row for that client. Miss one, and the same client appears at two addresses.
- The firm renames the account "Accounts Receivable" to "Trade Receivables". If the account name is typed into every journal line, the old name is in tens of thousands of lines, and the report splits into two accounts.

This problem is called **normalization**: store each fact once, in the table that owns it, and let other tables refer to it by key. The address lives in `clients` once. Each invoice holds only `client_id`. The journal lines hold only `gl_account_id`, and the account's name lives in `gl_accounts`.

> Diagram: Left, one wide sheet that repeats the client name and address on every invoice row. Right, two linked tables where the client's address is stored once in the clients table and each invoice holds only the client_id.

The next query counts journal lines by account name. The names come from one table, so a rename in `gl_accounts` changes every report that uses it. Runs on `cpafirm`.

```sql
SELECT TOP 5 a.account_name, COUNT(*) AS journal_lines
FROM gl_journal_lines AS l
JOIN gl_accounts AS a ON a.gl_account_id = l.gl_account_id
GROUP BY a.account_name
ORDER BY journal_lines DESC;
```

**What it returns.** Accounts Receivable has 42,354 lines, Operating Cash has 20,292, and Bookkeeping Revenue has 14,240. Each name is typed once in `gl_accounts`. MariaDB uses `LIMIT 5` in place of `TOP 5`.

## Relationships and how many rows match

**Cardinality** is how many rows sit on each side of a relationship:

- **One to many.** One client has many invoices. One invoice has many lines. This is the common case, and it is drawn with a "1" at the one side and a crow's-foot "many" mark at the other.
- **Many to many.** One payment can pay several invoices, and one invoice can be paid by several payments. A pair of tables cannot hold that directly. The database uses a third table, a **bridge**, with one row per payment and invoice pair. In `cpafirm` that table is `payment_applications`.

A useful check is an **orphan** test: find child rows whose parent is missing. A foreign key should prevent them, but a check you run yourself proves it. This query returns the invoices whose client is not in `clients`. Runs on `cpafirm`.

```sql
SELECT COUNT(*) AS invoices_without_a_client
FROM invoices AS i
WHERE NOT EXISTS (SELECT 1 FROM clients AS c WHERE c.client_id = i.client_id);
```

**What it returns.** 0. Every invoice has a client. An orphan count above zero is a data problem to chase before you trust any total.

> Exercise: How many invoices were paid by two or more payment applications? Return a single count.


## Journal entries: a header and lines that balance

A journal entry has a **header** (date, source, memo) and **lines** (account, debit or credit). Each line is one side of the entry. The rule is simple: the debits and the credits in one entry are equal.

When the billing system bills an invoice, it writes a journal entry with `source_type = 'Invoice'` and `source_id` set to the invoice. The invoice is the bill. The journal lines are where the ledger balance lives. Here is the entry for INV-2026-03848. Runs on `cpafirm`.

```sql
SELECT a.account_number, a.account_name, l.debit, l.credit
FROM gl_journal_entries AS e
JOIN gl_journal_lines AS l ON l.journal_entry_id = e.journal_entry_id
JOIN gl_accounts AS a      ON a.gl_account_id = l.gl_account_id
WHERE e.source_type = 'Invoice'
  AND e.source_id = (SELECT invoice_id FROM invoices WHERE invoice_number = 'INV-2026-03848')
ORDER BY l.journal_line_id;
```

**What it returns.** Three lines. Accounts Receivable (1200) is debited 868.75. Tax Preparation Revenue (4100) is credited 525.00, and Tax Planning Revenue (4200) is credited 343.75. The debit equals the two credits.

> Diagram: A journal entry with a header line and three lines. Accounts Receivable is debited 868.75; Tax Preparation Revenue is credited 525.00 and Tax Planning Revenue is credited 343.75. Totals are equal.

Two rules follow for any query you write:

1. **A balance is debits minus credits.** Assets and expenses are normally debit balances. Liabilities, equity and revenue are normally credit balances.
2. **Sum the journal lines, not the invoices, for ledger numbers.** An invoice total and a ledger total can differ by timing, adjustments or write-offs. Use the lines for anything that must agree with the books.

The next control runs over every entry in the journal. It counts entries whose debits and credits differ. Runs on `cpafirm`.

```sql
SELECT COUNT(*) AS entries_checked,
       SUM(CASE WHEN x.total_debit <> x.total_credit THEN 1 ELSE 0 END) AS unbalanced,
       SUM(x.total_debit) AS all_debits,
       SUM(x.total_credit) AS all_credits
FROM (SELECT e.journal_entry_id,
             SUM(l.debit)  AS total_debit,
             SUM(l.credit) AS total_credit
      FROM gl_journal_entries AS e
      JOIN gl_journal_lines AS l ON l.journal_entry_id = e.journal_entry_id
      GROUP BY e.journal_entry_id) AS x;
```

**What it returns.** 42,549 entries, none unbalanced, and total debits equal to total credits at 46,902,772.68. Run this control before any other ledger work. If it fails, nothing built on the journal can be trusted.

> Exercise: How many journal entries have each number of lines? Show the number of lines and the number of entries, fewest lines first.


## Transactions: all of the entry, or none of it

A **transaction** groups several writes into one unit. Either every write is saved, or none is. Posting an invoice means writing the invoice header, its lines and the journal entry. If the system fails after the header and before the lines, a transaction undoes the header as well. The database never keeps half an entry.

> Diagram: Three writes in order: invoice header, invoice lines, journal entry. If all three succeed the transaction commits and all are kept. If the second write fails the transaction rolls back and none are kept.

This is why a healthy ledger always balances. The software writes an entry as one unit, and the database refuses to leave a partial one behind. In SQL Server the statements are `BEGIN TRANSACTION`, `COMMIT` and `ROLLBACK`. MariaDB accepts `START TRANSACTION` as well as `BEGIN`.

When you read a report, you read committed data. A query never sees half of an entry that is still being written.

## Read-only access is safe for reporting

Give analysts read-only access to the database. They can run any `SELECT`, but they cannot change a balance, a status or an address. A mistake in a read-only query can give a wrong number, and you can fix it by running the query again.

> **Watch out.** A mistake in an `UPDATE` changes the books.

The LearnSQL sample databases work the same way. The sample API accepts `SELECT` statements only, so every query in this guide reads the data and writes nothing. Keep posting rights with a few people who need them, and reporting rights with everyone who builds reports.

## Indexes: how the database finds rows quickly

An **index** is a sorted list that points at rows, like the index at the back of a book. Without one, the database reads every row to find the ones you asked for, which is a table scan. With an index on `client_id`, it jumps straight to one client's invoices.

> Diagram: Left, without an index the database reads every invoice row to find the matches. Right, an index on client_id is a sorted list that jumps straight to the rows for one client.

The database keeps the index up to date for you. The cost is that every insert or update must also update the index, so a table with many indexes writes a little more slowly. The next query lists the indexes on `invoices`. Runs on `cpafirm`.

```sql
SELECT t.name AS table_name, i.name AS index_name, i.type_desc, i.is_unique
FROM sys.indexes AS i
JOIN sys.tables AS t ON t.object_id = i.object_id
WHERE t.name = 'invoices'
  AND i.name IS NOT NULL
ORDER BY i.name;
```

**What it returns.** The primary key (`pk_invoices`), an index on `client_id`, an index on `issue_date`, an index on `status`, and a unique index on `invoice_number`. The unique index does two jobs: it speeds up lookups by number, and it stops the same number from being entered twice.

**Practical rule.** Filter on the raw column. Write `issue_date >= '2026-01-01' AND issue_date < '2027-01-01'` rather than `YEAR(issue_date) = 2026`. Wrapping the column in a function usually stops the database from using the index on it. MariaDB shows the same index list with `SHOW INDEX FROM invoices`.

## Views: a saved query with a name

A **view** is a saved `SELECT` that you can query as if it were a table. `vw_ar_aging` in `cpafirm` builds the receivables aging: each client's open balance split into buckets by how many days past due it is. The next query asks which clients owe the most. Runs on `cpafirm`.

```sql
SELECT TOP 5 client_name, current_amt, days_1_30, days_31_60,
       days_61_90, days_over_90, total_due
FROM vw_ar_aging
ORDER BY total_due DESC;
```

**What it returns.** Sammamish Tire & Brake LLC owes 8,895.00, of which 5,535.00 is 1 to 30 days past due. Cedar Freight Corp. owes 8,747.88, of which 337.88 is more than 90 days past due. Each row shows how late the money is, not only how much.

A view is only as correct as its definition, so check it against the books. The next query adds up the view and then reads account 1200, Accounts Receivable, from the general ledger. Runs on `cpafirm`.

```sql
SELECT (SELECT SUM(total_due) FROM vw_ar_aging) AS open_per_aging_view,
       (SELECT SUM(l.debit - l.credit)
        FROM gl_journal_lines AS l
        JOIN gl_accounts AS a ON a.gl_account_id = l.gl_account_id
        WHERE a.account_number = 1200) AS receivable_balance_in_gl;
```

**What it returns.** Both numbers are 566,429.12. The subledger (the aging by client) agrees with the general ledger to the cent.

> **Audit tip.** When they do not agree, look for a missing payment, an adjustment that was posted to the wrong account, or a view that filters out rows.

## Reading a schema diagram

A **schema** is the map of tables, columns and keys. Most database tools draw it as an **entity relationship diagram** (ERD). Each box is a table. The top line names the table, and the lines below list its key columns. A line between two boxes is a relationship. The end with the "many" mark belongs to the child table, which holds the foreign key.

> Diagram: Entity relationship diagram of the invoice tables: clients to invoices to invoice lines, invoice lines to engagements, engagements to services, and services to service lines, with the key columns named in each box.

Read an ERD in this order:

1. **Find the primary keys.** Each table's own key is the one that identifies its rows.
2. **Follow the foreign keys.** The column marked FK points at another table's primary key.
3. **Read the marks at each end.** A "1" at one end and a "many" mark at the other means one parent to many children.

You can list the keys in the database itself. This query returns the primary and foreign keys for `invoices` and `invoice_lines`. Runs on `cpafirm`.

```sql
SELECT tc.TABLE_NAME, tc.CONSTRAINT_TYPE, kcu.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu ON kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
WHERE tc.TABLE_NAME IN ('invoices', 'invoice_lines')
  AND tc.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'FOREIGN KEY')
ORDER BY tc.TABLE_NAME, tc.CONSTRAINT_TYPE, kcu.COLUMN_NAME;
```

**What it returns.** For `invoices`, the primary key `invoice_id` and foreign keys to `client_id`, `billing_contact_id` and `issued_by`. For `invoice_lines`, the primary key `invoice_line_id` and foreign keys to `invoice_id` and `engagement_id`. The foreign keys are the answer to "how do I join this table?"

## Where the LearnSQL sample databases fit

Each sample database teaches a different shape. Pick the one that matches the work you do.

| Database | Shape | Use it to practice |
|---|---|---|
| `cpafirm` | Accounting: clients, invoices, payments, time entries, a general ledger | Receivables, billing, ledger tie-outs |
| `nonprofit` | Fund accounting: a general ledger by fund, budgets, grants | Budget against actual by fund and account |
| `qbo` | QuickBooks Online-style tables in the `QuickBooksOnline` schema (Invoices, JournalEntries, Accounts) | Extracts from a small business ledger |
| `qbd` | QuickBooks Desktop-style tables in the `QuickBooks` schema | Desktop-style exports and aging |
| `sage100` | Sage 100 table names (AR_Customer, AP_InvoiceHeader) in `dbo` | Real ERP table naming |
| `contoso` | A star schema: `FactSales` with dimension tables | Power BI measures and model design |
| `retailwh` | A star-schema warehouse with a monthly budget and snapshots | Budget against actual, and the modeling guide |

`cpafirm`, `nonprofit`, `sage100` and `retailwh` keep their tables in `dbo`, so `invoices` and `dbo.invoices` are the same table. `qbo` and `contoso` use their own schema names, which is why the queries on them write `QuickBooksOnline.Invoices` or `contoso.FactSales`.

## Try it

Run these on the databases named in each line. Read the output before you change anything.

1. **Runs on `cpafirm`.** List the columns of the `payments` table, in order.

```sql
SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'payments'
ORDER BY ORDINAL_POSITION;
```

2. **Runs on `cpafirm`.** How did clients pay in fiscal 2026 (July 2025 to June 2026)? Check the payment method that brought in the most cash.

```sql
SELECT payment_method, COUNT(*) AS payments, SUM(amount) AS received
FROM payments
WHERE fiscal_year = 2026
GROUP BY payment_method
ORDER BY received DESC;
```

3. **Runs on `cpafirm`.** Is any invoice missing its lines? The answer should be 0. If it is not, the invoice totals cannot be checked against the detail.

```sql
SELECT COUNT(*) AS invoices_without_lines
FROM invoices AS i
WHERE NOT EXISTS (SELECT 1 FROM invoice_lines AS il WHERE il.invoice_id = i.invoice_id);
```

Next, read [SQL for accountants](/analyze/sql-for-accountants) for the close queries that build on these tables, and [SQL essentials](/analyze/sql-essentials) for the syntax behind every example here.
