Downloads

How databases work, for finance professionals

Tables, keys and relationships explained with invoices and ledgers, why an ERP is not one big spreadsheet, and how to read a schema.

Last updated

View as Markdown For AI agents All guides as text

On this page

You'll be able to

  • Explain tables, keys and relationships using invoices and ledger entries.
  • Say why an ERP is many linked tables rather than one big spreadsheet.
  • Read a schema diagram and follow how the tables join.
  • Explain why a journal entry is saved whole or not at all, and why read-only access is safe for reports.

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 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:

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.

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:

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.

One wide sheet Invoice Client Address Amount INV-1001 Client A 12 Pine Rd 475.00 INV-1002 Client A 12 Pine Rd 945.00 INV-1003 Client A 12 Pine Rd 868.75 INV-1004 Client A 12 Pine Rd 120.00 The address is typed on every row. A move means four edits, and a missed row leaves the client with two addresses. Linked tables clients (one row) client_id 1 | Client A | 12 Pine Rd the address is stored here, once invoices (many rows) INV-1001 | client_id 1 INV-1002 | client_id 1 (and so on) client_id
Normalization. The address is typed once in clients. Invoices point at it with client_id, so a change of address happens in one place.

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.

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:

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.

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.

Try it

Invoices paid in more than one payment

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

Runs on cpafirm (SQL Server). Only read-only SELECT queries run here.

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.

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.

Journal entry 42496 · 2026-10-22 Source: Invoice INV-2026-03848 (Young, Paul) · 3 lines Account Debit Credit 1200 Accounts Receivable 868.75 0.00 4100 Tax Preparation Revenue 0.00 525.00 4200 Tax Planning Revenue 0.00 343.75 Totals 868.75 868.75 Balanced: debits equal credits
One journal entry for an invoice. The lines are the ledger. Debits and credits in every entry are equal, so the total of the debit column equals the total of the credit column.

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.

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.

Try it

How many lines each journal entry has

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

Runs on cpafirm (SQL Server). Only read-only SELECT queries run here.

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.

1. Write invoice header invoices 2. Write invoice lines invoice_lines 3. Write journal entry gl_journal_entries, gl_journal_lines COMMIT all three kept all succeed ROLLBACK all three writes undone step 2 fails A reader never sees half of an entry. The journal balances because a partial entry is never saved.
A transaction is all or nothing. Posting an invoice writes three things. If one write fails, the database undoes the others, so a half-posted entry is never saved.

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.

Without an index: read every row every row is checked, one by one With an index on client_id client_id points at 170 a few invoice rows 171 invoice rows for Young, Paul 172 a few invoice rows INV-2026-03848 and the other invoices are found without reading the rest the index is kept up to date by the database
An index is a sorted lookup list. Without one, the database reads all 21,282 invoice rows. With one, it jumps to the matching rows.

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.

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.

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.

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.

1* 1* *1 1* 1* clients client_id (PK) client_name city, state, status invoices invoice_id (PK) client_id (FK) issue_date, due_date total_amount, status invoice_lines invoice_line_id (PK) invoice_id (FK) engagement_id (FK) amount, quantity engagements engagement_id (PK) service_id (FK) fee_amount, status services service_id (PK) service_line_id (FK) service_name, billing_type service_lines service_line_id (PK) line_name
The invoice tables in cpafirm. PK marks a primary key and FK a foreign key that points at another table. The "1" end is the parent and the "*" end is the child.

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.

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.
SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'payments'
ORDER BY ORDINAL_POSITION;
  1. 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.
SELECT payment_method, COUNT(*) AS payments, SUM(amount) AS received
FROM payments
WHERE fiscal_year = 2026
GROUP BY payment_method
ORDER BY received DESC;
  1. 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.
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 for the close queries that build on these tables, and SQL essentials for the syntax behind every example here.

Want to see it run?

Open a finished Power BI project or Excel workbook built on the same data, or ask an AI assistant that has read these guides.