MyDay database schema

PostgreSQL column types. MySQL has the same tables and keys, with BINARY(16) for ids and VARCHAR(32) for owner.

erDiagram
  statuses ||--o{ items : "status"
  items ||--o| schedules : "owner, item_id"
  schedules ||--o| repeats : "owner, item_id"
  repeat_kinds ||--o{ repeats : "kind"
  repeats ||--o| interval_repeats : "owner, item_id, kind"
  repeats ||--o{ item_weekday_repeats : "owner, item_id, kind"
  repeats ||--o| monthly_repeats : "owner, item_id, kind"
  repeat_units ||--o{ interval_repeats : "unit"
  weekdays ||--o{ item_weekday_repeats : "weekday"
  statuses {
    varchar status PK
  }
  items {
    varchar owner PK
    bytea id PK
    varchar status FK
    varchar title
    text description
    integer version
    double rank
  }
  schedules {
    varchar owner PK,FK
    bytea item_id PK,FK
    timestamp due_at
  }
  repeats {
    varchar owner PK,FK
    bytea item_id PK,FK
    varchar kind FK
  }
  repeat_kinds {
    varchar kind PK
  }
  interval_repeats {
    varchar owner PK,FK
    bytea item_id PK,FK
    varchar kind FK
    smallint every
    varchar unit FK
  }
  repeat_units {
    varchar unit PK
  }
  item_weekday_repeats {
    varchar owner PK,FK
    bytea item_id PK,FK
    varchar kind FK
    smallint weekday PK,FK
  }
  weekdays {
    smallint weekday PK
    varchar name
  }
  monthly_repeats {
    varchar owner PK,FK
    bytea item_id PK,FK
    varchar kind FK
    smallint every
    smallint month_day
  }