Schemas that speak: ER and class diagrams

Entities are THINGS your business tracks - written in CAPS by convention, one per table (or one per business concept, if you're modeling before the tables exist). A relationship line connects two of them, with a cardinality pair on each end and a verb label after the colon.

erDiagram
  CUSTOMER ||--o{ ORDER : places

The middle of the line picks the relationship style: -- is identifying (solid line - the child cannot exist without the parent, like an order line without its order) and .. is non-identifying (dashed line - the entities are related but independently viable). The full cardinality alphabet:

Notice the mirror: the crow's foot ({ or }) always points AT the entity it describes, so the same "many" symbol is written }o on the left end and o{ on the right end. If typing symbols feels cryptic, mermaid also accepts word aliases like one or more and zero or many - handy while learning, symbols once fluent.

An entity earns its place on the diagram when you show WHAT it holds. Open a block with braces and list type name pairs, one per line. Tag key columns with PK, FK, or UK, and hang a comment in quotes at the end of any line - the single best place to record data gotchas.

erDiagram
  CUSTOMER ||--o{ SUBSCRIPTION : signs
  CUSTOMER {
    int customer_id PK
    string email UK "lowercased at ingestion"
    string full_name
    date signed_up_on
  }
  SUBSCRIPTION {
    int subscription_id PK
    int customer_id FK
    string plan
    date ends_on "null while active"
  }

• Types are free text - int, string, timestamp, decimal, whatever your warehouse calls them. Mermaid doesn't validate; your reviewers do.
• PK / FK / UK = primary, foreign, unique key. A column can carry several, comma-separated: int customer_id PK, FK - exactly what bridge tables need.
• Quoted comments render right on the diagram. "null while active" has saved more analysts than any wiki page.

Here is Nova's core model: five entities, five relationships, every key marked. This is the artifact Demo 1 builds line by line - read it top to bottom and notice it narrates itself: customers sign subscriptions, place orders, generate events; campaigns target customers and get attribution credit on orders.

erDiagram
  CUSTOMER ||--o{ SUBSCRIPTION : signs
  CUSTOMER ||--o{ ORDER : places
  CUSTOMER ||--o{ EVENT : generates
  CAMPAIGN |o..o{ ORDER : "gets credit for"
  CAMPAIGN }o..o{ CUSTOMER : targets

  CUSTOMER {
    int customer_id PK
    string email UK "lowercased at ingestion"
    date signed_up_on
  }
  SUBSCRIPTION {
    int subscription_id PK
    int customer_id FK
    string plan
    date ends_on "null while active"
  }
  EVENT {
    string event_id PK
    int customer_id FK
    string event_type
    timestamp occurred_at
  }
  ORDER {
    int order_id PK
    int customer_id FK
    int campaign_id FK "null for organic orders"
    decimal amount
  }
  CAMPAIGN {
    int campaign_id PK
    string channel
    date launched_on
  }

Note the relationship styles doing real work: customer-to-order is identifying (--, an order is meaningless without its customer), while campaign links are non-identifying (.., orders exist happily without a campaign - that's what "null for organic orders" is telling you, in two places that now agree).

Real warehouses have hundreds of tables; ER diagrams have a working budget of roughly 8-12 entities before they stop being read. Three habits keep them useful:

• One diagram per domain - a "customer domain" ERD, a "billing domain" ERD - linked from a small index diagram, exactly like we split Nova's flowcharts in Session 2.
• Attributes only where they earn rent. Show full blocks for the 3-4 entities under discussion; leave the rest as bare named boxes for context.
• Model the business, not the database dump. Skip audit columns and ETL bookkeeping unless the diagram is ABOUT them. The reader wants the shape, not the DDL.

Almost every real relationship is one of three shapes. Learn them as idioms and you'll write them without thinking:

erDiagram
  TEAM ||--|{ MEMBER : has
  CUSTOMER |o--o| LOYALTY-CARD : holds
  CUSTOMER ||--o{ CAMPAIGN-TOUCH : receives
  CAMPAIGN ||--o{ CAMPAIGN-TOUCH : sends
  CAMPAIGN-TOUCH {
    int customer_id PK, FK
    int campaign_id PK, FK
    date touched_on
  }

• One-to-many - the workhorse. TEAM ||--|{ MEMBER: a team has one or more members, a member belongs to exactly one team. Ask "can it exist empty?" to pick |{ vs o{.
• Optional one-to-one - |o--o|. Both sides may exist alone; at most one pairing. Loyalty cards, parking spots, profile extensions.
• Many-to-many via bridge - customers receive many campaigns, campaigns hit many customers. Warehouses don't store many-to-many directly: a bridge entity (CAMPAIGN-TOUCH) with a composite key (PK, FK twice) resolves it into two clean one-to-manys - and usually grows useful facts of its own, like touched_on.

Choosing -- vs .. trips people up because it FEELS philosophical. It isn't. Ask two questions about the child entity:

• Can it exist without the parent? An EVENT without a CUSTOMER is noise; that relationship is identifying (--). An ORDER without a CAMPAIGN is just an organic order; non-identifying (..).
• Is the parent's key part of the child's identity? If the foreign key sits inside the primary key (like both columns of CAMPAIGN-TOUCH), that's identifying by definition.

When still unsure, default to non-identifying - it's the weaker claim, and a reviewer can strengthen it with evidence.

A class block lists attributes and methods - methods are just members with (). Prefix each member with a visibility marker: + public, - private, # protected, ~ package. Return types go AFTER the method: +run() CheckResult.

classDiagram
  class QualityCheck {
    +String name
    +String severity
    -int failCount
    #bool strictMode
    ~String ownerTeam
    +run(batch) CheckResult
    -log(message) void
  }

• Visibility is documentation of intent: + is the API your teammates call, - is internals free to change, # is the contract with subclasses.
• Suffixes for the fine print: $ marks a static member, * an abstract one - +read() Batch* reads as "subclasses must implement this".
• Don't inventory every field. A diagram class with 30 members is a screenshot of the code, not a model of it. Show what a caller or subclass needs to know.

Class relationships are drawn with six arrows, and the two diamonds are the ones interviews ask about. Composition (*--) means the part LIVES AND DIES with the whole; aggregation (o--) means the whole USES parts that outlive it.

Here is Nova's ingestion SDK - the code from Sessions 2-3's flowcharts, now as structure. Multiplicities ride the relationship in quotes, labels after a colon:

classDiagram
  class Connector {
    +String name
    #int batchSize
    +connect() bool
    +read() Batch*
  }
  class EventStreamConnector {
    +String topic
    +read() Batch
  }
  class CrmExportConnector {
    +String dropPath
    +read() Batch
  }
  class Pipeline {
    -List~Connector~ sources
    +run() RunReport
  }
  Connector <|-- EventStreamConnector
  Connector <|-- CrmExportConnector
  Pipeline o-- "1..*" Connector : reads from
  Pipeline *-- RunLog : owns

Read the diamonds: connectors are aggregated (o--) because they're registered from outside and survive any single pipeline run; the RunLog is composed (*--) because it is created by the run and meaningless without it.

Four refinements for when the SDK diagram grows up:

• Generics with tildes: angle brackets clash with mermaid's arrow syntax, so List<Connector> is written List~Connector~. Works in class names too: class Queue~T~.
• Annotations mark a class's kind on the first line of its body: <<interface>>, <<abstract>>, <<service>>. Pair <<interface>> with realization arrows (..|>) and your "implements" story draws itself.
• Namespaces group classes into labeled boxes: namespace Ingestion { class Connector } - the class-diagram cousin of Session 2's subgraphs.
• Notes pin commentary: note for Connector "one subclass per source system".