# Big Bear Lake (city) data dictionary

Sample database `bigbear` 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).
12 of 61 tables are described. Undocumented tables are listed at the end.

## business_licenses

Business licenses issued to businesses, by year.

Grain: one row per business per license year

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `license_id` | int | PK | Unique license ID. |  |
| `business_id` | int | FK to businesses | Business (businesses). |  |
| `license_number` | varchar(14) |  | License number. |  |
| `license_year` | smallint |  | Year the license covers. | 2026 (859), 2027 (854), 2025 (834), 2024 (822), 2023 (803), 2022 (781) |
| `issue_date` | date |  | Date issued. |  |
| `expiration_date` | date |  | Expiration date. | 2026-06-30 (859), 2027-06-30 (854), 2025-06-30 (834), 2024-06-30 (822), 2023-06-30 (803), 2022-06-30 (781) |
| `annual_fee` | decimal |  | Fee for the year. |  |
| `paid_date` | date |  | Date the fee was paid. |  |
| `status` | varchar(12) |  | License status (see the values). | Expired (3908), Active (815), Unpaid (162), Closed (68) |
| `fiscal_year` | smallint |  |  |  |
| `fiscal_quarter` | tinyint |  |  |  |
| `fiscal_period` | tinyint |  |  |  |

## businesses

Businesses that hold a city license, with their category and location.

Grain: one row per business

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `business_id` | int | PK | Unique business ID. |  |
| `business_name` | varchar(120) |  | Business name. |  |
| `business_category` | varchar(40) |  | Category (see the values). |  |
| `naics_code` | char(6) |  | NAICS industry code. |  |
| `parcel_id` | int | FK to parcels | Location parcel (parcels). |  |
| `address_line1` | varchar(100) |  |  |  |
| `city` | varchar(60) |  |  |  |
| `state` | char(2) |  |  |  |
| `postal_code` | varchar(10) |  |  |  |
| `opened_date` | date |  | Date opened. |  |
| `closed_date` | date |  | Date closed, if closed. |  |
| `employee_count` | smallint |  | Employees. |  |
| `is_home_based` | bit |  | 1 if the business operates from a home. | False (784), True (132) |
| `is_seasonal` | bit |  |  |  |
| `county` | varchar(60) |  |  |  |
| `country` | char(2) |  |  |  |
| `latitude` | decimal |  |  |  |
| `longitude` | decimal |  |  |  |
| `geocode_quality` | varchar(24) |  |  |  |
| `full_address` | varchar(220) |  |  |  |

## cad_calls

Emergency dispatch calls: type, priority, the times for each step of the response, and the disposition.

Grain: one row per call

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `call_id` | int | PK | Unique call ID. |  |
| `incident_number` | varchar(14) |  | Incident number. |  |
| `call_type_id` | int | FK to cad_call_types | Type of call (cad_call_types). |  |
| `agency` | varchar(10) |  | Responding agency. | Sheriff (21574), Fire/EMS (8875) |
| `priority` | tinyint |  | Priority of the call (see the values). | 3 (13813), 2 (9456), 4 (4180), 1 (3000) |
| `received_at` | datetime2 |  | When the call was received. |  |
| `dispatched_at` | datetime2 |  | When a unit was dispatched. |  |
| `enroute_at` | datetime2 |  | When the unit left the station. |  |
| `onscene_at` | datetime2 |  | When the unit arrived on scene. |  |
| `cleared_at` | datetime2 |  | When the unit cleared. |  |
| `unit_id` | int | FK to cad_units | Unit that responded (cad_units). |  |
| `jurisdiction` | varchar(30) |  |  |  |
| `neighborhood_id` | int | FK to neighborhoods |  |  |
| `parcel_id` | int | FK to parcels |  |  |
| `address_text` | varchar(120) |  |  |  |
| `disposition` | varchar(24) |  | How the call ended (see the values). |  |
| `weather_snowfall_in` | decimal |  |  |  |
| `geocode_quality` | varchar(24) |  |  |  |
| `latitude` | decimal |  |  |  |
| `longitude` | decimal |  |  |  |
| `fiscal_year` | smallint |  |  |  |
| `fiscal_quarter` | tinyint |  |  |  |
| `fiscal_period` | tinyint |  |  |  |

## gl_accounts

The chart of accounts.

Grain: one row per account

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `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). | Revenue (20), Expense (19), Asset (3), Fund Balance (1), Liability (1) |
| `category` | varchar(40) |  | Reporting category. |  |
| `parent_account_id` | int | FK to gl_accounts |  |  |

## gl_journal_lines

Debit and credit lines of the city's general ledger entries, tagged by fund, department and project.

Grain: one row per journal line

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `line_id` | int | PK | Unique line ID. |  |
| `entry_id` | int | FK to gl_journal_entries | Journal entry (gl_journal_entries). |  |
| `account_id` | int | FK to gl_accounts | Account (gl_accounts). |  |
| `fund_id` | int | FK to funds | Fund (funds). | 1 (6534), 8 (1714), 9 (941), 7 (499), 3 (419), 2 (314), 4 (232), 5 (174), 10 (174), 6 (130) |
| `department_id` | int | FK to departments | Department (departments). |  |
| `project_id` | int | FK to capital_projects | Capital project (capital_projects). |  |
| `debit` | decimal |  | Debit amount. |  |
| `credit` | decimal |  | Credit amount. |  |

## invoices

Bills the city has received from its vendors (accounts payable).

Grain: one row per invoice

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `invoice_id` | int | PK | Unique invoice ID. |  |
| `vendor_id` | int | FK to vendors | Vendor (vendors). |  |
| `invoice_number` | varchar(30) |  | Vendor's invoice number. |  |
| `invoice_date` | date |  | Invoice date. |  |
| `due_date` | date |  | Due date. |  |
| `paid_date` | date |  | Date paid. |  |
| `amount` | decimal |  | Invoice amount. |  |
| `account_id` | int | FK to gl_accounts | Account charged (gl_accounts). |  |
| `fund_id` | int | FK to funds | Fund charged (funds). | 1 (2640), 8 (624), 9 (209), 7 (174), 4 (70), 3 (61), 2 (18), 10 (10) |
| `department_id` | int | FK to departments |  |  |
| `project_id` | int | FK to capital_projects |  |  |
| `grant_id` | int | FK to grants |  |  |
| `status` | varchar(20) |  | Invoice status (see the values). | Paid (3734), Open (60), Overdue (12) |
| `description` | varchar(200) |  |  |  |
| `fiscal_year` | smallint |  |  |  |
| `fiscal_quarter` | tinyint |  |  |  |
| `fiscal_period` | tinyint |  |  |  |

## parcels

Land parcels in the city, identified by assessor's parcel number (APN): address, land use and building facts.

Grain: one row per parcel

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `parcel_id` | int | PK | Unique parcel ID. |  |
| `apn` | varchar(16) |  | Assessor's parcel number. |  |
| `address_line1` | varchar(100) |  | Street address. |  |
| `city` | varchar(60) |  |  |  |
| `state` | char(2) |  |  |  |
| `postal_code` | varchar(10) |  |  |  |
| `street_id` | int | FK to streets |  |  |
| `neighborhood_id` | int | FK to neighborhoods | Neighborhood (neighborhoods). | 8 (1565), 10 (921), 3 (905), 6 (864), 9 (780), 2 (726), 12 (631), 1 (587), 11 (542), 5 (486), 4 (379), 7 (257) |
| `zone_id` | int | FK to zoning_districts | Zoning district (zoning_districts). | 1 (3245), 4 (1178), 3 (1153), 8 (777), 7 (762), 2 (645), 9 (388), 6 (213), 5 (211), 10 (71) |
| `owner_id` | int | FK to property_owners | Owner (property_owners). |  |
| `land_use` | varchar(30) |  | Land use category (see the values). | Single Family Residence (3514), Condominium (1231), Vacant Residential Lot (1138), Commercial Retail (570), Lodging (509), Townhome (435), Multi-Family (2-4 units) (313), Restaurant or Bar (313), Office or Services (259), Multi-Family (5+ units) (160), Mixed Use (130), Institutional or Public (71) |
| `lot_sqft` | int |  | Lot size in square feet. |  |
| `year_built` | smallint |  | Year the building was built. |  |
| `bedrooms` | tinyint |  |  |  |
| `bathrooms` | decimal |  |  |  |
| `building_sqft` | int |  | Building size in square feet. |  |
| `is_owner_occupied` | bit |  | 1 if the owner lives on the parcel. | False (7336), True (1307) |
| `miles_to_lake` | decimal |  |  |  |
| `elevation_ft` | smallint |  |  |  |
| `wildfire_zone` | varchar(12) |  |  |  |
| `has_water_service` | bit |  |  |  |
| `county` | varchar(60) |  |  |  |
| `country` | char(2) |  |  |  |
| `latitude` | decimal |  |  |  |
| `longitude` | decimal |  |  |  |
| `geocode_quality` | varchar(24) |  |  |  |
| `full_address` | varchar(220) |  |  |  |

## permits

Building and other permits: type, parcel, valuation, fee and status.

Grain: one row per permit

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `permit_id` | int | PK | Unique permit ID. |  |
| `permit_number` | varchar(14) |  | Permit number. |  |
| `permit_type_id` | int | FK to permit_types | Permit type (permit_types). |  |
| `parcel_id` | int | FK to parcels | Parcel (parcels). |  |
| `neighborhood_id` | int | FK to neighborhoods |  |  |
| `applicant_type` | varchar(24) |  | Who applied (see the values). | Contractor (3063), Owner (2155), Architect or Engineer (369), Tenant (257) |
| `description` | varchar(200) |  |  |  |
| `valuation` | decimal |  | Declared project value. |  |
| `square_feet` | int |  |  |  |
| `applied_date` | date |  | Date applied for. |  |
| `issued_date` | date |  | Date issued. |  |
| `finaled_date` | date |  | Date the work was signed off. |  |
| `expires_date` | date |  |  |  |
| `status` | varchar(12) |  | Permit status (see the values). | Finaled (5261), Expired (237), Withdrawn (186), Cancelled (86), Issued (61), In Review (13) |
| `fee_assessed` | decimal |  | Fee charged. |  |
| `fee_paid` | decimal |  | Fee paid. |  |
| `reviewer_employee_id` | int | FK to employees |  |  |
| `latitude` | decimal |  |  |  |
| `longitude` | decimal |  |  |  |
| `fiscal_year` | smallint |  |  |  |
| `fiscal_quarter` | tinyint |  |  |  |
| `fiscal_period` | tinyint |  |  |  |

## service_requests

Resident requests to the city, such as potholes or illegal dumping, from submission to closure.

Grain: one row per request

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `request_id` | int | PK | Unique request ID. |  |
| `request_number` | varchar(14) |  | Public request number. |  |
| `type_id` | int | FK to service_request_types | Kind of request (service_request_types). |  |
| `channel` | varchar(12) |  | How it was submitted (see the values). | Phone (5735), Mobile app (4748), Web form (3730), Email (1742), Staff (1646), Counter (1019) |
| `submitted_at` | datetime2 |  | When it was submitted. |  |
| `parcel_id` | int | FK to parcels | Parcel it relates to (parcels). |  |
| `street_id` | int | FK to streets |  |  |
| `neighborhood_id` | int | FK to neighborhoods | Neighborhood (neighborhoods). |  |
| `address_text` | varchar(120) |  |  |  |
| `description` | varchar(200) |  |  |  |
| `reporter_type` | varchar(20) |  |  |  |
| `reporter_postal_code` | varchar(10) |  |  |  |
| `priority` | tinyint |  | Priority (see the values). | 3 (9788), 4 (4728), 2 (2512), 1 (1592) |
| `status` | varchar(12) |  | Request status (see the values). | Closed (16019), Duplicate (1377), Referred (845), Cancelled (293), Open (77), In Progress (9) |
| `assigned_employee_id` | int | FK to employees | Employee assigned (employees). |  |
| `acknowledged_at` | datetime2 |  | When the city acknowledged it. |  |
| `closed_at` | datetime2 |  | When it was closed. |  |
| `resolution` | varchar(60) |  |  |  |
| `duplicate_of_request_id` | int | FK to service_requests | Earlier request this duplicates, if any. |  |
| `satisfaction` | tinyint |  | Satisfaction score given at closure, if any. | (blank) (15878), 2 (603), 3 (581), 1 (556), 5 (513), 4 (489) |
| `storm_id` | int | FK to storm_events | Storm event it relates to (storm_events). |  |
| `geocode_quality` | varchar(24) |  |  |  |
| `latitude` | decimal |  |  |  |
| `longitude` | decimal |  |  |  |
| `fiscal_year` | smallint |  |  |  |
| `fiscal_quarter` | tinyint |  |  |  |
| `fiscal_period` | tinyint |  |  |  |

## tot_filings

Monthly transient occupancy tax (TOT) returns filed by short-term rental operators.

Grain: one row per operator per month

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `filing_id` | int | PK | Unique filing ID. |  |
| `tot_operator_id` | int | FK to tot_operators | Operator (tot_operators). |  |
| `period_month` | date |  | Month the return covers. |  |
| `due_date` | date |  | Due date. |  |
| `filed_date` | date |  | Date filed. |  |
| `available_nights` | smallint |  |  |  |
| `occupied_nights` | smallint |  | Nights occupied. |  |
| `gross_receipts` | decimal |  | Gross rental receipts. |  |
| `exempt_receipts` | decimal |  | Receipts exempt from the tax. |  |
| `taxable_receipts` | decimal |  | Receipts subject to the tax. |  |
| `tax_rate` | decimal |  | Tax rate. | 0.1 (50960), 0.12 (27039) |
| `tax_due` | decimal |  | Tax owed. |  |
| `penalty` | decimal |  | Late penalty. |  |
| `paid_date` | date |  |  |  |
| `paid_amount` | decimal |  | Amount paid. |  |
| `status` | varchar(16) |  | Filing status (see the values). | Filed on time (70195), Late (5937), Audit adjusted (1396), Delinquent (370), Overdue (101) |
| `fiscal_year` | smallint |  |  |  |
| `fiscal_quarter` | tinyint |  |  |  |
| `fiscal_period` | tinyint |  |  |  |

## utility_bills

Monthly water bills: meter readings, usage, charges and payment status.

Grain: one row per bill

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `bill_id` | int | PK | Unique bill ID. |  |
| `account_id` | int | FK to utility_accounts | Utility account (utility_accounts). |  |
| `bill_date` | date |  | Bill date. |  |
| `service_from` | date |  | Start of the billed period. |  |
| `service_to` | date |  | End of the billed period. |  |
| `billing_days` | smallint |  |  |  |
| `read_previous` | int |  | Previous meter reading. |  |
| `read_current` | int |  | Current meter reading. |  |
| `usage_ccf` | decimal |  | Water used, in hundred cubic feet. |  |
| `read_type` | varchar(10) |  | How the reading was taken (see the values). | Actual (185231), AMI (71909), Estimated (6481) |
| `leak_flag` | bit |  | 1 if the usage looks like a leak. | False (262434), True (1187) |
| `base_charge` | decimal |  |  |  |
| `usage_charge` | decimal |  |  |  |
| `surcharge` | decimal |  |  |  |
| `late_fee` | decimal |  |  |  |
| `total_amount` | decimal |  | Total billed. |  |
| `due_date` | date |  |  |  |
| `paid_date` | date |  |  |  |
| `paid_amount` | decimal |  | Amount paid. |  |
| `status` | varchar(12) |  | Bill status (see the values). | Paid (257314), Open (4475), Written off (1536), Delinquent (296) |
| `fiscal_year` | smallint |  |  |  |
| `fiscal_quarter` | tinyint |  |  |  |
| `fiscal_period` | tinyint |  |  |  |

## work_orders

Crew work orders for city assets and streets, from the request that started them to completion.

Grain: one row per work order

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `work_order_id` | int | PK | Unique work order ID. |  |
| `work_order_number` | varchar(14) |  | Work order number. |  |
| `request_id` | int | FK to service_requests | Service request that started it (service_requests). |  |
| `asset_id` | int | FK to assets | Asset ID the crew worked on. |  |
| `street_id` | int | FK to streets |  |  |
| `neighborhood_id` | int | FK to neighborhoods |  |  |
| `department_id` | int | FK to departments | Department (departments). | 5 (6556), 6 (5971) |
| `work_type` | varchar(40) |  | Type of work (see the values). |  |
| `is_preventive` | bit |  | 1 for scheduled preventive work. | True (7159), False (5368) |
| `crew` | varchar(24) |  |  |  |
| `priority` | tinyint |  | Priority (see the values). | 4 (7697), 3 (3160), 1 (1098), 2 (572) |
| `status` | varchar(12) |  | Order status (see the values). | Completed (12394), Cancelled (95), Open (26), Scheduled (9), In Progress (3) |
| `opened_date` | date |  | Date opened. |  |
| `scheduled_date` | date |  |  |  |
| `completed_date` | date |  | Date completed. |  |
| `labor_hours` | decimal |  | Labor hours. |  |
| `labor_cost` | decimal |  | Labor cost. |  |
| `material_cost` | decimal |  | Material cost. |  |
| `equipment_cost` | decimal |  |  |  |
| `storm_id` | int | FK to storm_events |  |  |
| `latitude` | decimal |  |  |  |
| `longitude` | decimal |  |  |  |
| `fiscal_year` | smallint |  |  |  |
| `fiscal_quarter` | tinyint |  |  |  |
| `fiscal_period` | tinyint |  |  |  |

## Undocumented tables

`agenda_items`, `asset_types`, `assets`, `budgets`, `cad_call_types`, `cad_units`, `capital_projects`, `code_cases`, `code_violation_types`, `council_meetings`, `council_members`, `council_votes`, `dataset_info`, `departments`, `employees`, `facilities`, `fiscal_calendar`, `fleet_maintenance`, `fleet_vehicles`, `funds`, `geo_places`, `geo_postal_codes`, `geo_states`, `gl_journal_entries`, `grants`, `inspections`, `job_classes`, `neighborhoods`, `parcel_assessments`, `pay_periods`, `payroll_records`, `permit_types`, `positions`, `property_owners`, `property_transfers`, `service_request_types`, `short_term_rentals`, `snow_operations`, `snow_plow_routes`, `special_events`, `storm_events`, `street_condition_surveys`, `streets`, `tot_operators`, `utility_accounts`, `vendors`, `water_rates`, `weather_daily`, `zoning_districts`
