Logical Schema Diagram

This sample is a logical schema diagram for a small support-desk application on PostgreSQL. It shows each table with its columns and data types, the primary and foreign keys that link them, and the indexes and constraints that make the common queries fast and the data correct.

1. The Diagram

Support Desk: Logical Schema (PostgreSQL) app_user PK user_id BIGINT UK email VARCHAR(255) display_name VARCHAR(100) role VARCHAR(20) created_at TIMESTAMPTZ ticket PK ticket_id BIGINT FK requester_id BIGINT FK assignee_id BIGINT NULL subject VARCHAR(200) status VARCHAR(20) priority SMALLINT created_at TIMESTAMPTZ ticket_comment PK comment_id BIGINT FK ticket_id BIGINT FK author_id BIGINT body TEXT created_at TIMESTAMPTZ ticket_tag PK,FK ticket_id BIGINT PK,FK tag_id INT tag PK tag_id INT UK name VARCHAR(50) requests assigned has writes Indexes UNIQUE ix_user_email ON app_user(email) ix_ticket_status ON ticket(status, created_at) ix_ticket_assignee ON ticket(assignee_id) ix_comment_ticket ON ticket_comment(ticket_id) UNIQUE ix_tag_name ON tag(name) CHECK status IN ('open','pending','solved') CHECK priority BETWEEN 1 AND 4 ON DELETE CASCADE: ticket -> comment, ticket_tag

Unlike a conceptual ER diagram, this one is close to DDL: every column has a type, and keys and indexes are explicit.

  1. app_user holds everyone: customers and agents, told apart by role. email has a unique key so it can be used to log in.
  2. ticket has two foreign keys to app_user. requester_id is mandatory (bars at the user end); assignee_id is nullable (circle), because new tickets are unassigned.
  3. ticket_comment belongs to one ticket and one author. Its ticket_id index makes "load the thread" fast.
  4. ticket_tag (highlighted) is the junction table for the many-to-many between tickets and tags; its primary key is the pair of foreign keys.
  5. The green box lists indexes. ix_ticket_status is composite, matching the main agent query: open tickets, oldest first.
  6. The grey box lists CHECK constraints and cascade rules that the lines alone cannot show.

2. How to Read It

Logical Schema Notation table_name PK primary_key_col BIGINT FK foreign_key_col BIGINT UK unique_col VARCHAR(50) plain_col TEXT NULL PK = primary key (yellow name) FK = foreign key, points at a PK UK = unique constraint type on right; NULL marks nullable 1 to 0..N 0..1 to 0..N index list

Table boxes list columns top-down with key markers on the left and types on the right. Relationship lines use crow's foot endings; where it helps, a line leaves the box next to the FK column it represents. Indexes and constraints go in side boxes so the tables stay readable.

3. Variant: Conceptual View

Same Schema, Conceptual Level User Ticket Comment Tag requests has tagged Many-to-many Ticket-Tag is still one line here; the logical model adds ticket_tag.

The conceptual view hides columns and implementation detail, which suits business conversations. The logical view adds attributes, keys and types; a physical view would add storage details such as partitions and tablespaces. Keep both: the simple one for discussions, the detailed one for code review.

4. Pros, Cons, and When to Use

Pros

Cons

When to use

When not to use

5. As Diagrams-as-Code

Mermaid erDiagram with types and keys:

erDiagram
    app_user ||--o{ ticket : requests
    app_user |o--o{ ticket : "assigned to"
    ticket   ||--o{ ticket_comment : has
    app_user ||--o{ ticket_comment : writes
    ticket   ||--o{ ticket_tag : tagged
    tag      ||--o{ ticket_tag : labels
    app_user {
        bigint user_id PK
        varchar email UK
        varchar role
    }
    ticket {
        bigint ticket_id PK
        bigint requester_id FK
        bigint assignee_id FK "nullable"
        varchar status
        smallint priority
    }
    ticket_tag {
        bigint ticket_id PK, FK
        int tag_id PK, FK
    }

Mermaid has no index syntax; keep indexes in DDL or a DBML indexes { } block.