Municipal Finance Lab

ERD & Data Modeling Study Guide

A printable guide for studying table relationships, grain, fact and dimension thinking, business rules, and SQL analysis questions when you do not feel like doing a full lab.

Project Municipal Finance Lab

Main Database municipal_finance_live

Focus Relationships, grain, analysis, and audit thinking

How to use this guide

Read one section at a time. The goal is not to memorize the diagram. The goal is to practice seeing how tables connect to business questions.

On low-energy days, pick one reflection question, write a few lines, and stop there. That still counts as practice.

Reference Visual

Municipal Finance ERD Learning Canvas

Municipal Finance Lab ERD learning canvas

Main entities

Things the system tracks, such as taxpayers and assessments.

Activity tables

Events and measurable activity, such as payments and transactions.

Lookup tables

Reference categories that make reports readable.

Principle 1

Relationships: Tables Are Not Islands

An ERD shows how one table gives context to another. The most important relationship pattern in this project is one-to-many.

taxpayers -> assessments One taxpayer can have many assessments.

assessments -> payments One assessment can have many payments.

assessments -> assessment_transactions One assessment can have many financial events.

Lookup tables -> detail tables One category can describe many detail records.

Reflection Which relationship feels most important for reporting: taxpayer to assessment, assessment to payment, or assessment to transaction? Why?

Principle 2

Grain: What Does One Row Mean?

Grain is the meaning of one row in a table. If you do not know the grain, your counts, sums, and joins can become misleading.

taxpayers One row = one taxpayer account.

assessments One row = one tax assessment.

payments One row = one payment received.

assessment_transactions One row = one financial event.

Why this matters

If you count rows in payments, you are counting payments. If you count rows in assessments, you are counting assessments. Those are different business questions.

Low-energy practice Write the grain of three tables from memory.

Principle 3

Fact Tables And Dimension Tables

This is the bridge from database design to dashboards. Fact tables hold measurable activity. Dimension and lookup tables describe that activity.

Fact-like tables

assessments: assessed amount, due date, tax year
payments: payment amount, posted date, effective date
assessment_transactions: transaction amount and date

Dimension / lookup tables

taxpayers: account and taxpayer details
tax_types: tax category labels
assessment_status: status labels
payment_method: payment method labels
transaction_types: event type labels

Dashboard rule of thumb

Measures usually come from fact tables. Filters, labels, and grouping fields usually come from dimension or lookup tables.

Principle 4

Payments Are Not The Same As Transactions

This is one of the most important modeling decisions in the project. A payment is a transaction, but not every transaction is a payment.

Payments

Payments represent money received. They have payment methods, dates, amounts, and receipt numbers.

Transactions

Transactions represent financial events. They can include payments, adjustments, cancellations, refunds, and write-offs.

Explain it simply How would you explain the difference between a payment and a transaction to a manager?

Principle 5

Business Rules Make The Model Useful

A useful data model does not just store records. It makes business rules testable with SQL.

Paid status: should reconcile to payment totals.
Open status: can be unpaid or partially paid, but should not be fully paid.
Cancelled status: may still need transaction history if financial activity happened first.
Adjusted status: should have an adjustment event or note explaining the change.
Refunds: reduce net transaction activity and help explain overpayments.
Write-offs: reduce collectible balance but are not customer payments.

Audit thinking Pick one rule above. What SQL report could test it?

Analysis Workflow

From Business Question To Query

When you do not know where to start, use this sequence.

1. Business question What does the user need to know or decide?

2. Identify grain Should the result be one row per taxpayer, assessment, payment, or transaction?

3. Pick base table Which table owns the main event or record?

4. Join context tables Which lookup or detail tables make the output readable?

5. Aggregate measures What needs to be counted, summed, averaged, or grouped?

6. Reconcile and explain Do the totals make sense, and what does the result mean?

Practice question Which delinquent accounts need attention first?

Quick Review

Ten-Minute Study Prompts

Explain what one row means in assessments.
Explain why tax_types is a lookup table.
Describe the relationship between taxpayers and assessments.
Describe the relationship between assessments and payments.
Explain why payments and transactions are separated.
Name two business rules that SQL can test.
Identify one measure that belongs in a dashboard.
Identify one filter or grouping field that belongs in a dashboard.
Write one business question this model can answer.
Write one question you still have about the model.

Question I still have