Construction Project Database Schema

erDiagram
  PROJECT ||--|{ SITE : has
  PROJECT ||--|{ WORK_PACKAGE : "divided into"
  SUBCONTRACTOR ||--o{ WORK_PACKAGE : executes
  SITE ||--o{ MATERIAL_DELIVERY : receives
  MATERIAL ||--o{ MATERIAL_DELIVERY : "delivered as"
  SITE ||--o{ DAILY_LOG : records
  WORK_PACKAGE ||--o{ RA_BILL : "billed via"
  RA_BILL ||--o{ PAYMENT : "paid by"
  PROJECT {
    int id PK
    string name
    decimal budget
  }
  SITE {
    int id PK
    int project_id FK
    string address
  }
  WORK_PACKAGE {
    int id PK
    int project_id FK
    int subcontractor_id FK
    string name
    decimal budget
  }
  SUBCONTRACTOR {
    int id PK
    string name
    string trade
  }
  MATERIAL {
    int id PK
    string name
    string unit
  }
  MATERIAL_DELIVERY {
    int id PK
    int site_id FK
    int material_id FK
    decimal quantity
    date delivered_on
  }
  DAILY_LOG {
    int id PK
    int site_id FK
    date log_date
    int manpower
    string weather
  }
  RA_BILL {
    int id PK
    int work_package_id FK
    decimal measured_value
    string status
  }
  PAYMENT {
    int id PK
    int ra_bill_id FK
    decimal amount
    date paid_on
  }