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:
- 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/2026sorts before09/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.
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.
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:
- 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
cpafirmthat table ispayment_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.
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.
payment_applications is the bridge between payments and invoices. Group it by invoice and count the rows in each group.
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.
Two rules follow for any query you write:
- A balance is debits minus credits. Assets and expenses are normally debit balances. Liabilities, equity and revenue are normally credit balances.
- 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.
Count the lines inside each entry first. Then count the entries that share each line count.
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.
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.
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.
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.
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.
Read an ERD in this order:
- Find the primary keys. Each table's own key is the one that identifies its rows.
- Follow the foreign keys. The column marked FK points at another table's primary key.
- 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.
- Runs on
cpafirm. List the columns of thepaymentstable, in order.
SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'payments' ORDER BY ORDINAL_POSITION;
- 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;
- 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.