CloudSketch AI Logo
FeaturesTemplatesPricingEnterpriseAboutContactLog in
🌙☀️
Log inStart free
Home/Templates/Energy & Utilities

⚡ Energy & Utilities · Database ERD

Utility Customer and Billing Schema

The core tables of a utility billing system: customers, premises, meters, meter readings, tariffs and bills.

More Energy & Utilities templates

Opening a template is free and never counts toward your daily diagrams. Asking the AI to change it counts as one.

Drawing diagram…

What this diagram shows

  • A premises can have several meters over its life
  • Readings belong to a meter, bills to an account
  • Tariffs are versioned with effective dates

Prompt used

Database schema for utility billing: customers own accounts; each account is for a premises; a premises has meters (with install and removal dates); meters have readings (actual or estimated); bills are generated per account per billing period using a tariff, with bill lines and payments.

Mermaid code
erDiagram
  CUSTOMER ||--o{ ACCOUNT : holds
  PREMISES ||--o{ ACCOUNT : "billed for"
  PREMISES ||--o{ METER : has
  METER ||--o{ READING : records
  ACCOUNT ||--o{ BILL : receives
  TARIFF ||--o{ BILL : "priced by"
  BILL ||--|{ BILL_LINE : contains
  BILL ||--o{ PAYMENT : "paid by"
  CUSTOMER {
    int id PK
    string name
    string phone
  }
  ACCOUNT {
    int id PK
    int customer_id FK
    int premises_id FK
    string status
  }
  PREMISES {
    int id PK
    string address
    string category
  }
  METER {
    int id PK
    int premises_id FK
    string serial_no
    date installed_on
    date removed_on
  }
  READING {
    int id PK
    int meter_id FK
    datetime read_at
    decimal kwh
    string read_type
  }
  TARIFF {
    int id PK
    string code
    decimal rate_per_kwh
    date effective_from
  }
  BILL {
    int id PK
    int account_id FK
    int tariff_id FK
    date period_end
    decimal amount
  }
  BILL_LINE {
    int id PK
    int bill_id FK
    string description
    decimal amount
  }
  PAYMENT {
    int id PK
    int bill_id FK
    decimal amount
    string channel
  }

Inside this diagram

An entity relationship diagram with 9 tables, 37 columns and 8 relationships.

Tables and their columns

  • CUSTOMER: id (PK), name, phone
  • ACCOUNT: id (PK), customer_id (FK), premises_id (FK), status
  • PREMISES: id (PK), address, category
  • METER: id (PK), premises_id (FK), serial_no, installed_on, removed_on
  • READING: id (PK), meter_id (FK), read_at, kwh, read_type
  • BILL: id (PK), account_id (FK), tariff_id (FK), period_end, amount
  • TARIFF: id (PK), code, rate_per_kwh, effective_from
  • BILL_LINE: id (PK), bill_id (FK), description, amount
  • PAYMENT: id (PK), bill_id (FK), amount, channel

Relationships

  • CUSTOMER holds ACCOUNT: each CUSTOMER has zero or more ACCOUNT, each ACCOUNT belongs to exactly one CUSTOMER
  • PREMISES billed for ACCOUNT: each PREMISES has zero or more ACCOUNT, each ACCOUNT belongs to exactly one PREMISES
  • PREMISES has METER: each PREMISES has zero or more METER, each METER belongs to exactly one PREMISES
  • METER records READING: each METER has zero or more READING, each READING belongs to exactly one METER
  • ACCOUNT receives BILL: each ACCOUNT has zero or more BILL, each BILL belongs to exactly one ACCOUNT
  • TARIFF priced by BILL: each TARIFF has zero or more BILL, each BILL belongs to exactly one TARIFF
  • BILL contains BILL_LINE: each BILL has one or more BILL_LINE, each BILL_LINE belongs to exactly one BILL
  • BILL paid by PAYMENT: each BILL has zero or more PAYMENT, each PAYMENT belongs to exactly one BILL

Make your own diagram like this

Describe your own system in plain English and the AI ER diagram generator draws it in seconds, in the same style as this template.

Related templates

Data FlowEnergy & Utilities

Smart Meter Data Flow

How readings from millions of smart electricity meters reach billing and customers: head-end system, meter data management and analytics.

FlowchartEnergy & Utilities

Power Outage Management Workflow

What a power utility does from the moment an outage is detected to restoring supply and closing the incident.

C4 ArchitectureProEnergy & Utilities

Smart Grid Architecture

The software behind a modern power grid: SCADA/EMS for transmission, ADMS for distribution, smart meters, DER management and the customer portal.

SequenceEnergy & Utilities

Utility Billing Sequence

How an electricity or water bill is produced and paid: meter reads, validation, tariff calculation, bill delivery and payment posting.

DeploymentProEnergy & Utilities

Solar and Wind Plant Monitoring

Where monitoring runs for a renewable energy portfolio: inverters and turbines at each plant, a plant controller, and the central monitoring cloud.

SankeyEnergy & Utilities

Electricity Generation Mix Sankey

How electricity flows from generation sources to the grid and on to consumer segments, including losses.