# Nonprofit data dictionary

Sample database `nonprofit` on learnsql.cpetoday.com (SQL Server login: CPE Today course). Each table lists its columns with the type, keys, what the column means, and, for codes and statuses, the values seen in the sample copy (with row counts when the dictionary was written).
16 of 49 tables are described. Undocumented tables are listed at the end.

## appeals

Individual appeals sent within a campaign, with cost and how many people were asked.

Grain: one row per appeal

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `appeal_id` | int | PK | Unique appeal ID. |  |
| `campaign_id` | int | FK to campaigns | Campaign (campaigns). |  |
| `appeal_name` | varchar(100) |  | Appeal name. |  |
| `channel` | varchar(20) |  | How it was sent (mail, email and so on). | Email (30), Mail (24), Social (6) |
| `appeal_date` | date |  | Date sent. |  |
| `cost` | decimal |  | Cost of the appeal. |  |
| `solicited_count` | int |  | People asked. |  |

## bills

Bills from vendors that the nonprofit owes.

Grain: one row per bill

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `bill_id` | int | PK | Unique bill ID. |  |
| `vendor_id` | int | FK to vendors | Vendor (vendors). |  |
| `bill_number` | varchar(30) |  | Vendor's bill number. |  |
| `bill_date` | date |  | Bill date. |  |
| `due_date` | date |  | Due date. |  |
| `paid_date` | date |  | Date paid, if paid. |  |
| `amount` | decimal |  | Bill amount. |  |
| `gl_account_id` | int | FK to gl_accounts |  |  |
| `fund_id` | int | FK to funds | Fund charged (funds). |  |
| `cost_center_id` | int | FK to cost_centers |  |  |
| `grant_id` | int | FK to grants | Grant the cost is charged to, if any (grants). |  |
| `event_id` | int | FK to events |  |  |
| `status` | varchar(20) |  | Bill status (see the values). | Paid (13613), Open (226), Overdue (32) |
| `description` | varchar(200) |  |  |  |
| `fiscal_year` | smallint |  |  |  |
| `fiscal_quarter` | tinyint |  |  |  |
| `fiscal_period` | tinyint |  |  |  |

## campaigns

Fundraising campaigns, with goals.

Grain: one row per campaign

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `campaign_id` | int | PK | Unique campaign ID. |  |
| `campaign_name` | varchar(100) |  | Campaign name. |  |
| `campaign_type` | varchar(30) |  | Type of campaign. | Event (18), Annual Appeal (12), Corporate (6), Major Gifts (6), Online (6), Program Drive (6), Sustainer (6), Year-End (6), Capital (1) |
| `fiscal_year` | smallint |  | Fiscal year. | 2023 (12), 2022 (11), 2024 (11), 2025 (11), 2026 (11), 2027 (11) |
| `start_date` | date |  | Start date. |  |
| `end_date` | date |  | End date. |  |
| `goal_amount` | decimal |  | Fundraising goal. |  |
| `default_fund_id` | int | FK to funds |  |  |

## constituents

People and organizations that support the nonprofit: donors, members, volunteers and prospects.

Grain: one row per constituent

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `constituent_id` | int | PK | Unique constituent ID. |  |
| `constituent_number` | varchar(12) |  | Number on the donor record. |  |
| `constituent_type` | varchar(20) |  | Individual or organization (see the values). | Individual (20039), Organization (848) |
| `first_name` | varchar(40) |  | First name. |  |
| `last_name` | varchar(40) |  | Last name. |  |
| `organization_name` | varchar(150) |  | Organization name, for organizations. |  |
| `organization_type` | varchar(30) |  |  |  |
| `email` | varchar(120) |  |  |  |
| `phone` | varchar(20) |  |  |  |
| `address_line1` | varchar(100) |  |  |  |
| `city` | varchar(60) |  |  |  |
| `state` | char(2) |  |  |  |
| `postal_code` | varchar(10) |  |  |  |
| `status` | varchar(20) |  | Record status (see the values). | Active (20500), Do Not Contact (258), Deceased (129) |
| `email_opt_in` | bit |  | 1 if the constituent accepts email. | True (15538), False (5349) |
| `mail_opt_in` | bit |  | 1 if the constituent accepts mail. | True (15514), False (5373) |
| `created_date` | date |  | Date the record was created. |  |
| `source` | varchar(40) |  | How the constituent was added. |  |
| `giving_capacity` | char(1) |  | Estimated capacity to give. | E (12964), (blank) (4207), D (2827), C (614), B (206), A (69) |
| `assigned_officer_id` | int | FK to employees | Gift officer responsible (employees). | (blank) (20124), 26 (395), 23 (368) |
| `county` | varchar(60) |  |  |  |
| `country` | char(2) |  |  |  |
| `latitude` | decimal |  |  |  |
| `longitude` | decimal |  |  |  |
| `geocode_quality` | varchar(20) |  |  |  |
| `full_address` | varchar(220) |  |  |  |

## employees

Staff, with job title, cost center and pay.

Grain: one row per employee

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `employee_id` | int | PK | Unique employee ID. |  |
| `employee_number` | varchar(10) |  |  |  |
| `first_name` | varchar(40) |  | First name. |  |
| `last_name` | varchar(40) |  | Last name. |  |
| `email` | varchar(100) |  |  |  |
| `job_title_id` | int | FK to job_titles | Job title (job_titles). |  |
| `cost_center_id` | int | FK to cost_centers | Cost center (cost_centers). | 1 (14), 2 (12), 7 (9), 3 (6), 4 (5), 5 (5), 6 (5), 8 (2) |
| `manager_id` | int | FK to employees |  |  |
| `work_location` | varchar(40) |  |  |  |
| `employment_type` | varchar(20) |  | Full-time, part-time or other (see the values). | Full-time (51), Part-time (7) |
| `fte` | decimal |  | Full-time equivalent share. | 1.0 (48), 0.75 (4), 0.5 (3), 0.8 (3) |
| `pay_type` | varchar(10) |  | Salaried or hourly (see the values). | Salary (42), Hourly (16) |
| `current_pay` | decimal |  | Current pay. |  |
| `hire_date` | date |  | Start date. |  |
| `termination_date` | date |  | Leave date, if any. |  |
| `termination_reason` | varchar(40) |  |  |  |
| `work_site_id` | int | FK to program_sites |  |  |

## event_registrations

Who registered for each event.

Grain: one row per registration

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `registration_id` | int | PK | Unique registration ID. |  |
| `event_id` | int | FK to events | Event (events). |  |
| `constituent_id` | int | FK to constituents |  |  |
| `ticket_type` | varchar(30) |  |  |  |
| `ticket_count` | smallint |  |  |  |
| `amount_paid` | decimal |  |  |  |
| `attended` | bit |  |  |  |
| `gift_id` | int | FK to gifts |  |  |

## events

Fundraising events such as galas and auctions.

Grain: one row per event

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `event_id` | int | PK | Unique event ID. |  |
| `campaign_id` | int | FK to campaigns | Campaign (campaigns). |  |
| `event_name` | varchar(100) |  | Event name. |  |
| `event_type` | varchar(30) |  | Kind of event. | Dinner (6), Donor Reception (6), Fun Run (6), Gala (6) |
| `event_date` | date |  | Date of the event. |  |
| `venue` | varchar(100) |  | Venue. | Alliance Community Hub (6), Lakeside Conference Center (6), Marymoor Park (6), Riverside Grange Hall (6) |
| `goal_amount` | decimal |  | Revenue goal. | 0.0 (6), 90000.0 (6), 120000.0 (6), 400000.0 (6) |
| `ticket_price` | decimal |  | Ticket price. | 0.0 (6), 35.0 (6), 75.0 (6), 175.0 (6) |
| `fmv_per_ticket` | decimal |  |  |  |
| `venue_address` | varchar(100) |  |  |  |
| `venue_city` | varchar(60) |  |  |  |
| `venue_state` | char(2) |  |  |  |
| `venue_postal_code` | varchar(10) |  |  |  |
| `venue_latitude` | decimal |  |  |  |
| `venue_longitude` | decimal |  |  |  |

## funds

Funds that gifts and grants are restricted to, with the net asset class that reports them.

Grain: one row per fund

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `fund_id` | int | PK | Unique fund ID. |  |
| `fund_code` | varchar(12) |  | Fund code. |  |
| `fund_name` | varchar(120) |  | Fund name. |  |
| `net_asset_class` | varchar(40) |  | Reporting class: with donor restrictions or without (see the values). | With Donor Restrictions (101), Without Donor Restrictions (2) |
| `restriction_type` | varchar(30) |  | Kind of donor restriction (see the values). | Purpose (100), (blank) (1), Board Designated (1), Perpetual (1) |
| `program_id` | int | FK to programs | Program it supports (programs). | 3 (34), 1 (32), 2 (27), 4 (6), (blank) (4) |
| `is_active` | bit |  | 1 while the fund accepts gifts. | False (73), True (30) |
| `opened_date` | date |  |  |  |

## gifts

Gifts received: one row per gift, with the donor, fund, campaign and whether it came from a pledge or recurring plan.

Grain: one row per gift

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `gift_id` | int | PK | Unique gift ID. |  |
| `constituent_id` | int | FK to constituents | Donor (constituents). |  |
| `gift_date` | date |  | Date received. |  |
| `amount` | decimal |  | Gift amount. |  |
| `gift_type` | varchar(20) |  | Kind of gift (see the values). | One-time (48631), Recurring (44494), Event Ticket (6682), Matching Gift (1368), Pledge Payment (1014), In-Kind (719), DAF Grant (358), Stock (20) |
| `payment_method` | varchar(20) |  | How it was paid (see the values). | Credit Card (55857), Check (25109), ACH (19482), Cash (1657), In-Kind (719), Wire (442), Stock Transfer (20) |
| `fund_id` | int | FK to funds | Fund the gift supports (funds). | 1 (98824), 5 (1967), 9 (646), 8 (599), 4 (453), 6 (371), 7 (352), 3 (74) |
| `campaign_id` | int | FK to campaigns | Campaign credited (campaigns). |  |
| `appeal_id` | int | FK to appeals | Appeal that prompted the gift (appeals). |  |
| `pledge_id` | int | FK to pledges | Pledge the gift pays, if any (pledges). |  |
| `recurring_gift_id` | int | FK to recurring_gifts | Recurring plan, if any (recurring_gifts). |  |
| `batch_id` | int | FK to gift_batches | Gift batch it was entered in (gift_batches). |  |
| `benefit_value` | decimal |  | Value of any benefit the donor received, such as event tickets. |  |
| `tribute_type` | varchar(20) |  |  |  |
| `tribute_name` | varchar(100) |  |  |  |
| `is_anonymous` | bit |  | 1 if the donor asked not to be named. | False (102837), True (449) |
| `fiscal_year` | smallint |  | Fiscal year of the gift date. | 2022 (19945), 2025 (18679), 2026 (18255), 2024 (18131), 2023 (17782), 2027 (10494) |
| `fiscal_quarter` | tinyint |  |  |  |
| `fiscal_period` | tinyint |  |  |  |

## gl_accounts

The chart of accounts.

Grain: one row per account

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `gl_account_id` | int | PK | Unique account ID. |  |
| `account_number` | varchar(10) |  | Account number. |  |
| `account_name` | varchar(80) |  | Account name. |  |
| `account_type` | varchar(20) |  | Account type (see the values). | Expense (21), Revenue (13), Asset (7), Net Assets (2), Liability (1) |
| `parent_account_id` | int | FK to gl_accounts |  |  |
| `grant_budget_category` | varchar(40) |  | Budget category used for grant reporting. | (blank) (28), Other (7), Personnel (3), Contractual (2), Supplies (2), Direct Client Assistance (1), Travel (1) |

## gl_journal_lines

Debit and credit lines of general ledger entries, tagged by fund and cost center.

Grain: one row per journal line

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `journal_line_id` | int | PK | Unique line ID. |  |
| `journal_entry_id` | int | FK to gl_journal_entries | Entry (gl_journal_entries). |  |
| `gl_account_id` | int | FK to gl_accounts | Account (gl_accounts). |  |
| `fund_id` | int | FK to funds | Fund (funds). |  |
| `cost_center_id` | int | FK to cost_centers | Cost center (cost_centers). | (blank) (79545), 3 (6941), 1 (6274), 2 (4632), 4 (2214), 7 (1342), 5 (1186), 8 (986), 6 (688) |
| `debit` | decimal |  | Debit amount. |  |
| `credit` | decimal |  | Credit amount. |  |

## grant_payments

Money the funder pays on a grant.

Grain: one row per payment

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `grant_payment_id` | int | PK | Unique payment ID. |  |
| `grant_id` | int | FK to grants | Grant (grants). |  |
| `payment_date` | date |  | Date received. |  |
| `amount` | decimal |  | Amount. |  |
| `payment_type` | varchar(20) |  | Kind of payment (see the values). | Reimbursement (159), Installment (105) |
| `reference_number` | varchar(30) |  |  |  |
| `fiscal_year` | smallint |  |  |  |
| `fiscal_quarter` | tinyint |  |  |  |
| `fiscal_period` | tinyint |  |  |  |

## grants

Grants awarded to the nonprofit by funders.

Grain: one row per grant

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `grant_id` | int | PK | Unique grant ID. |  |
| `proposal_id` | int | FK to proposals | Proposal that led to it (proposals). |  |
| `funder_id` | int | FK to funders | Funder (funders). |  |
| `fund_id` | int | FK to funds | Fund (funds). |  |
| `program_id` | int | FK to programs |  |  |
| `grant_number` | varchar(30) |  | Grant number. |  |
| `grant_title` | varchar(150) |  | Title. |  |
| `award_amount` | decimal |  | Amount awarded. |  |
| `start_date` | date |  | Start date. |  |
| `end_date` | date |  | End date. |  |
| `is_reimbursable` | bit |  | 1 if the funder pays costs after they are spent. | False (58), True (36) |
| `indirect_rate` | decimal |  | Indirect cost rate allowed. | 10.0 (78), 15.0 (5), 0.0 (4), 5.0 (4), 12.0 (3) |
| `status` | varchar(20) |  | Grant status (see the values). | Closed (68), Active (20), Closing Out (5), Pending Start (1) |

## interactions

Contact history: calls, visits and emails with constituents.

Grain: one row per interaction

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `interaction_id` | int | PK | Unique interaction ID. |  |
| `constituent_id` | int | FK to constituents | Constituent (constituents). |  |
| `employee_id` | int | FK to employees |  |  |
| `opportunity_id` | int | FK to opportunities |  |  |
| `interaction_date` | date |  |  |  |
| `interaction_type` | varchar(30) |  |  |  |
| `subject` | varchar(150) |  |  |  |
| `outcome` | varchar(40) |  |  |  |
| `follow_up_date` | date |  |  |  |
| `fiscal_year` | smallint |  |  |  |
| `fiscal_quarter` | tinyint |  |  |  |
| `fiscal_period` | tinyint |  |  |  |

## opportunities

Solicitation pipeline: asks made to donors, with stage and expected amount.

Grain: one row per opportunity

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `opportunity_id` | int | PK | Unique opportunity ID. |  |
| `constituent_id` | int | FK to constituents | Prospect (constituents). |  |
| `officer_id` | int | FK to employees | Gift officer (employees). | 26 (290), 23 (270) |
| `campaign_id` | int | FK to campaigns | Campaign (campaigns). | 67 (198), 50 (102), 39 (71), 28 (58), 61 (53), 17 (46), 6 (32) |
| `fund_id` | int | FK to funds | Fund (funds). | 1 (348), 4 (198), 7 (5), 3 (2), 5 (2), 6 (2), 9 (2), 8 (1) |
| `stage` | varchar(30) |  | Pipeline stage (see the values). | Closed Lost (237), Closed Won (87), Cultivation (70), Qualified (50), Solicitation (49), Verbal Commitment (41), Identified (26) |
| `ask_amount` | decimal |  | Amount asked for. |  |
| `expected_amount` | decimal |  | Amount expected. |  |
| `created_date` | date |  |  |  |
| `expected_close_date` | date |  | Expected close date. |  |
| `closed_date` | date |  | Date closed. |  |
| `gift_id` | int | FK to gifts | Gift that resulted (gifts). |  |
| `fiscal_year` | smallint |  |  |  |
| `fiscal_quarter` | tinyint |  |  |  |
| `fiscal_period` | tinyint |  |  |  |

## pledges

Promises to give over time, with installments and any write-off.

Grain: one row per pledge

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `pledge_id` | int | PK | Unique pledge ID. |  |
| `constituent_id` | int | FK to constituents | Donor (constituents). |  |
| `campaign_id` | int | FK to campaigns | Campaign (campaigns). | 67 (75), 45 (25), 56 (25), 39 (24), 50 (22), 34 (18), 12 (14), 23 (14), 28 (8), 17 (7), 1 (6) |
| `fund_id` | int | FK to funds | Fund (funds). | 1 (163), 4 (75) |
| `pledge_date` | date |  | Date pledged. |  |
| `total_amount` | decimal |  | Total promised. |  |
| `installment_count` | smallint |  | Number of payments. | 12 (102), 3 (49), 5 (47), 4 (40) |
| `frequency` | varchar(20) |  | How often payments are due (see the values). | Annual (136), Monthly (102) |
| `first_due_date` | date |  |  |  |
| `status` | varchar(20) |  | Pledge status (see the values). | Active (158), Fulfilled (51), Written Off (29) |
| `written_off_amount` | decimal |  | Amount written off as uncollectible. |  |
| `written_off_date` | date |  |  |  |
| `fiscal_year` | smallint |  |  |  |
| `fiscal_quarter` | tinyint |  |  |  |
| `fiscal_period` | tinyint |  |  |  |

## Undocumented tables

`budgets`, `constituent_interests`, `constituent_relationships`, `cost_centers`, `dataset_info`, `fiscal_calendar`, `fiscal_periods`, `funders`, `geo_places`, `geo_postal_codes`, `geo_states`, `gift_acknowledgments`, `gift_batches`, `gl_journal_entries`, `grant_budgets`, `grant_deliverables`, `interests`, `job_titles`, `mobile_market_routes`, `mobile_market_stops`, `mobile_market_visits`, `pay_periods`, `payroll_records`, `program_sites`, `programs`, `proposals`, `recurring_gifts`, `salary_history`, `soft_credits`, `timesheet_allocations`, `vendors`, `volunteer_shifts`, `volunteers`
