Booking data model

Trace foreign keys between freight shipments, quotes, bookings and loads.

Shippers, shipments, quotes, bookings, drivers, carriers, loads, and stops tables with typed columns, primary and foreign keys, and connecting relationship lines.

Reference diagram from Archdocs. See the YAML below for this template's contents.

A booking can have several loads, each with its own stops and assigned carrier. This fictional freight data model shows those relationships alongside shippers, shipments and quotes.

Adapt the model

Rename the tables and columns to match your schema. Each row can carry a type and constraints such as pk, fk or notNull. Relationships connect column IDs, such as Shipment.shipper_id and Shipper.id.

Update the relationship endpoints when you rename a column. A foreign key row and its relationship line are separate declarations in this YAML.

This diagram describes a schema. It does not create tables, run migrations or check a live database.

Template YAML

Open in the editor Start free

apiVersion: archdocs/v1
title: Bookings data model
nodes:
  - id: Shipper
    type: erd-entity
    title: shippers
    at:
      x: 0
      y: 0
    rows:
      - label: id
        valueType: uuid
        pk: true
      - label: legal_name
        valueType: varchar(200)
        notNull: true
      - label: billing_email
        valueType: citext
        notNull: true
      - label: credit_terms_days
        valueType: smallint
        notNull: true
      - label: credit_limit_cents
        valueType: bigint
      - label: created_at
        valueType: timestamptz
        notNull: true
  - id: Shipment
    type: erd-entity
    title: shipments
    at:
      x: 400
      y: 0
    rows:
      - label: id
        valueType: uuid
        pk: true
      - label: shipper_id
        valueType: uuid
        fk: true
        notNull: true
      - label: reference_no
        valueType: varchar(40)
        unique: true
      - label: commodity
        valueType: text
        notNull: true
      - label: weight_kg
        valueType: numeric(10,2)
        notNull: true
      - label: equipment
        valueType: equipment_type
        notNull: true
      - label: pickup_after
        valueType: timestamptz
        notNull: true
      - label: deliver_before
        valueType: timestamptz
        notNull: true
  - id: Quote
    type: erd-entity
    title: quotes
    at:
      x: 800
      y: 0
    rows:
      - label: id
        valueType: uuid
        pk: true
      - label: shipment_id
        valueType: uuid
        fk: true
        notNull: true
      - label: lane
        valueType: varchar(64)
        notNull: true
      - label: linehaul_cents
        valueType: bigint
        notNull: true
      - label: fuel_cents
        valueType: bigint
        notNull: true
      - label: status
        valueType: quote_status
        notNull: true
      - label: expires_at
        valueType: timestamptz
        notNull: true
  - id: Booking
    type: erd-entity
    title: bookings
    at:
      x: 1200
      y: 0
    rows:
      - label: id
        valueType: uuid
        pk: true
      - label: quote_id
        valueType: uuid
        fk: true
        unique: true
        notNull: true
      - label: po_number
        valueType: varchar(40)
      - label: status
        valueType: booking_status
        notNull: true
      - label: booked_at
        valueType: timestamptz
        notNull: true
      - label: cancelled_at
        valueType: timestamptz
  - id: Driver
    type: erd-entity
    title: drivers
    at:
      x: 0
      y: 420
    rows:
      - label: id
        valueType: uuid
        pk: true
      - label: carrier_id
        valueType: uuid
        fk: true
        notNull: true
      - label: full_name
        valueType: varchar(120)
        notNull: true
      - label: phone
        valueType: varchar(20)
      - label: cdl_state
        valueType: char(2)
        notNull: true
  - id: Carrier
    type: erd-entity
    title: carriers
    at:
      x: 400
      y: 420
    rows:
      - label: id
        valueType: uuid
        pk: true
      - label: legal_name
        valueType: varchar(200)
        notNull: true
      - label: mc_number
        valueType: varchar(12)
        unique: true
        notNull: true
      - label: dot_number
        valueType: varchar(12)
        unique: true
        notNull: true
      - label: safety_rating
        valueType: safety_rating
      - label: insured_until
        valueType: date
        notNull: true
  - id: Load
    type: erd-entity
    title: loads
    at:
      x: 800
      y: 420
    rows:
      - label: id
        valueType: uuid
        pk: true
      - label: booking_id
        valueType: uuid
        fk: true
        notNull: true
      - label: carrier_id
        valueType: uuid
        fk: true
      - label: status
        valueType: load_status
        notNull: true
      - label: rate_cents
        valueType: bigint
      - label: eta
        valueType: timestamptz
      - label: delivered_at
        valueType: timestamptz
  - id: Stop
    type: erd-entity
    title: stops
    at:
      x: 1200
      y: 420
    rows:
      - label: id
        valueType: uuid
        pk: true
      - label: load_id
        valueType: uuid
        fk: true
        notNull: true
      - label: sequence
        valueType: smallint
        notNull: true
      - label: kind
        valueType: stop_kind
        notNull: true
      - label: facility_name
        valueType: varchar(200)
      - label: appointment_at
        valueType: timestamptz
      - label: arrived_at
        valueType: timestamptz
relationships:
  - from: Shipment.shipper_id
    to: Shipper.id
    cardinality: many-to-one
  - from: Quote.shipment_id
    to: Shipment.id
    cardinality: many-to-one
  - from: Booking.quote_id
    to: Quote.id
    cardinality: one-to-one
  - from: Load.booking_id
    to: Booking.id
    cardinality: many-to-one
  - from: Load.carrier_id
    to: Carrier.id
    cardinality: many-to-one
  - from: Driver.carrier_id
    to: Carrier.id
    cardinality: many-to-one
  - from: Stop.load_id
    to: Load.id
    cardinality: many-to-one