# Field Operations (utility) data dictionary

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

## Assets

Field assets such as poles, transformers and meters, with age and condition.

Grain: one row per asset

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `AssetID` | int | PK | Unique asset ID. |  |
| `AssetType` | nvarchar(12) |  | Kind of asset (see the values). | Utility Pole (327), Meter (293), Transformer (221), Hydrant (201), Valve (166), Pipe Segment (154), Pump (71), Breaker (67) |
| `RegionID` | int | FK to Regions | Region (Regions). | 2 (269), 3 (258), 4 (251), 5 (251), 6 (249), 1 (222) |
| `Manufacturer` | nvarchar(80) |  | Manufacturer. |  |
| `InstallDate` | date |  | Date installed. |  |
| `ReplacementCost` | decimal |  | Estimated cost to replace. |  |
| `Condition` | nvarchar(8) |  | Condition rating (see the values). | Good (786), Fair (434), Poor (216), Critical (64) |

## CustomerAccounts

Customer accounts that receive the utility bills.

Grain: one row per account

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `AccountID` | int | PK | Unique account ID. |  |
| `CustomerName` | nvarchar(60) |  | Account holder. |  |
| `RegionID` | int | FK to Regions | Region (Regions). | 1 (865), 4 (858), 2 (850), 5 (813), 6 (813), 3 (801) |
| `RateClass` | nvarchar(11) |  | Rate class the account is billed under. | Residential (4027), Commercial (685), Municipal (182), Industrial (106) |
| `ConnectDate` | date |  | Date service was connected. |  |
| `Email` | nvarchar(120) |  | Email. |  |
| `Phone` | nvarchar(20) |  | Phone. |  |

## Departments

Departments and their cost centers.

Grain: one row per department

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `DepartmentID` | int | PK | Unique department ID. | 1 (1), 2 (1), 3 (1), 4 (1), 5 (1), 6 (1), 7 (1), 8 (1), 9 (1), 10 (1) |
| `DepartmentName` | nvarchar(21) |  | Department name. | Customer Service (1), Electric Operations (1), Engineering (1), Finance (1), Fleet and Facilities (1), IT Services (1), Meter Services (1), Procurement (1), Safety and Compliance (1), Water Operations (1) |
| `CostCenter` | int |  | Cost center code used for reporting. | 1000 (1), 1010 (1), 1020 (1), 1030 (1), 1040 (1), 1050 (1), 1060 (1), 1070 (1), 1080 (1), 1090 (1) |

## Employees

Field and office employees.

Grain: one row per employee

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `EmployeeID` | int | PK | Unique employee ID. |  |
| `FullName` | nvarchar(60) |  | Name. |  |
| `Email` | nvarchar(120) |  |  |  |
| `DepartmentID` | int | FK to Departments | Department (Departments). | 9 (51), 3 (50), 7 (44), 4 (41), 2 (39), 6 (39), 8 (38), 10 (37), 5 (35), 1 (26) |
| `RegionID` | int | FK to Regions | Home region (Regions). | 5 (76), 4 (75), 1 (65), 6 (65), 3 (63), 2 (56) |
| `JobTitle` | nvarchar(16) |  | Job title. | Field Technician (88), Lineworker (75), Meter Reader (61), Analyst (39), Clerk (39), Engineer (36), Supervisor (25), Dispatcher (19), Planner (18) |
| `HireDate` | date |  | Start date. |  |
| `HourlyRate` | decimal |  | Pay per hour. |  |

## Materials

Material catalog: what can be used on a job, and its unit cost.

Grain: one row per material

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `MaterialID` | int | PK | Unique material ID. |  |
| `Category` | nvarchar(14) |  | Material category. | Hardware (54), Safety (51), Chemicals (49), Fittings (41), Wire and Cable (37), Meters (35), Pipe (33) |
| `Description` | nvarchar(400) |  | What the material is. |  |
| `UnitOfMeasure` | nvarchar(4) |  | Unit it is counted in. | box (68), each (60), ft (60), lb (59), gal (53) |
| `UnitCost` | decimal |  | Cost per unit. |  |

## MonthlyBills

Monthly bills sent to customer accounts for usage.

Grain: one row per bill

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `BillID` | int | PK | Unique bill ID. |  |
| `AccountID` | int | FK to CustomerAccounts | Account billed (CustomerAccounts). |  |
| `BillDate` | date |  | Bill date. | 2025-01-01 (5000), 2025-02-01 (5000), 2025-03-01 (5000), 2025-04-01 (5000), 2025-05-01 (5000), 2025-06-01 (5000), 2025-07-01 (5000), 2025-08-01 (5000), 2025-09-01 (5000), 2025-10-01 (5000), 2025-11-01 (5000), 2025-12-01 (5000) |
| `UsageUnits` | int |  | Units of usage billed. |  |
| `AmountBilled` | decimal |  | Amount billed. |  |
| `DaysToPay` | int |  | Days the customer took to pay. |  |
| `PaymentMethod` | nvarchar(8) |  | How the customer paid (see the values). | Auto-Pay (22761), Online (19329), Check (8307), Cash (4830), Phone (4773) |

## PurchaseOrders

Purchase orders placed with vendors, with approval and status.

Grain: one row per purchase order

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `POID` | int | PK | Unique PO ID. |  |
| `VendorID` | int | FK to Vendors | Vendor (Vendors). |  |
| `DepartmentID` | int | FK to Departments | Department that placed the order. | 1 (737), 9 (718), 3 (711), 10 (706), 6 (703), 2 (694), 5 (694), 4 (690), 8 (677), 7 (670) |
| `RegionID` | int | FK to Regions | Region. | 3 (1209), 5 (1192), 6 (1181), 2 (1160), 4 (1133), 1 (1125) |
| `ApprovedBy` | int | FK to Employees | Employee who approved the PO. |  |
| `PODate` | date |  | Date of the PO. |  |
| `Amount` | decimal |  | Order amount. |  |
| `Status` | nvarchar(9) |  | PO status (see the values). | Closed (4184), Received (1431), Approved (796), Cancelled (370), Draft (219) |

## Regions

Service regions.

Grain: one row per region

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `RegionID` | int | PK | Unique region ID. | 1 (1), 2 (1), 3 (1), 4 (1), 5 (1), 6 (1) |
| `RegionName` | nvarchar(12) |  | Region name. | Central (1), Coastal (1), Desert (1), Mountain (1), North Valley (1), South Bay (1) |
| `State` | nvarchar(2) |  | State the region is in. |  |

## ServiceInterruptions

Outages: when a region lost service, how long it lasted and how many customers were affected.

Grain: one row per interruption

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `EventID` | int | PK | Unique event ID. |  |
| `RegionID` | int | FK to Regions | Region affected (Regions). | 2 (564), 5 (523), 3 (499), 6 (499), 4 (498), 1 (417) |
| `AssetID` | int | FK to Assets | Asset that failed (Assets). |  |
| `StartDate` | date |  | When the outage began. |  |
| `DurationMinutes` | int |  | How long it lasted. |  |
| `CustomersAffected` | int |  | Customers without service. |  |
| `Cause` | nvarchar(19) |  | Cause of the outage (see the values). | Equipment Failure (942), Weather (709), Planned Maintenance (519), Vegetation (286), Animal Contact (236), Vehicle Accident (187), Unknown (121) |

## VendorInvoices

Invoices from vendors against purchase orders.

Grain: one row per vendor invoice

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `InvoiceID` | int | PK | Unique invoice ID. |  |
| `POID` | int | FK to PurchaseOrders | Purchase order (PurchaseOrders). |  |
| `InvoiceDate` | date |  | Invoice date. |  |
| `Amount` | decimal |  | Invoice amount. |  |
| `DaysToPay` | int |  | Days taken to pay. |  |
| `Disputed` | bit |  | 1 if the invoice is disputed. | False (8556), True (444) |

## Vendors

Vendors that supply materials and services.

Grain: one row per vendor

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `VendorID` | int | PK | Unique vendor ID. |  |
| `VendorName` | nvarchar(80) |  | Vendor name. |  |
| `Category` | nvarchar(19) |  | Category of goods or services. | Contract Labor (24), Safety Equipment (21), Tools (21), Pipe and Fittings (20), Vehicles and Fleet (19), IT and Software (17), Chemicals (15), Electrical Supplies (13) |
| `PaymentTerms` | nvarchar(6) |  | Payment terms. | Net 30 (79), Net 45 (39), Net 60 (20), Net 15 (12) |
| `State` | nvarchar(2) |  | State. | CA (92), NV (20), TX (17), OR (12), AZ (9) |
| `Phone` | nvarchar(20) |  | Phone. |  |

## WorkOrderMaterials

Materials used on each work order.

Grain: one row per material per work order

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `LineID` | int | PK | Unique line ID. |  |
| `WorkOrderID` | int | FK to WorkOrders | Work order (WorkOrders). |  |
| `MaterialID` | int | FK to Materials | Material used (Materials). |  |
| `Quantity` | int |  | Quantity used. |  |

## WorkOrders

Field work orders: the job, the asset it's on, who it's assigned to, priority and labor.

Grain: one row per work order

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `WorkOrderID` | int | PK | Unique work order ID. |  |
| `AssetID` | int | FK to Assets | Asset worked on (Assets). |  |
| `RegionID` | int | FK to Regions | Region (Regions). | 3 (4437), 2 (4402), 5 (4207), 4 (4199), 6 (4158), 1 (3597) |
| `DepartmentID` | int | FK to Departments | Department that owns the job (Departments). | 6 (2588), 1 (2545), 5 (2530), 8 (2516), 10 (2508), 2 (2497), 3 (2478), 7 (2475), 4 (2442), 9 (2421) |
| `AssignedTo` | int | FK to Employees | Employee assigned (Employees.EmployeeID). |  |
| `WorkType` | nvarchar(22) |  | Type of work (see the values). | Repair (6984), Preventive Maintenance (6315), Inspection (5468), Replacement (2440), Emergency (2044), Installation (1749) |
| `Priority` | nvarchar(9) |  | Priority of the job (see the values). | Medium (11231), Low (7470), High (5057), Emergency (1242) |
| `OpenedDate` | date |  | Date the order was opened. |  |
| `DaysToComplete` | int |  | Days taken from opening to completion. |  |
| `Status` | nvarchar(11) |  | Order status (see the values). | Completed (19442), Open (2498), In Progress (2037), Cancelled (1023) |
| `LaborHours` | decimal |  | Hours of labor. |  |
| `LaborCost` | decimal |  | Labor cost. |  |
| `Notes` | nvarchar(400) |  | Free-text notes from the crew. |  |
