Query

pilot.db — availability

SQL

CREATE TABLE availability  (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  trainer_id INTEGER NOT NULL REFERENCES trainers(id) ON DELETE CASCADE,
  weekday INTEGER NOT NULL,
  -- 0=So,
  1=Mo,
  ...,
  6=Sa
  start_time TEXT NOT NULL,
  -- 'HH:MM'
  end_time TEXT NOT NULL,
  recurring INTEGER NOT NULL DEFAULT 1    -- 1 = wiederkehrend,
  0 = einmaliges Sonderfenster,
  year_week TEXT NULL
)

+ Add column

Columns

Column Data type Allow null Primary key Actions
id INTEGER ✓ ✓ Rename | Drop
trainer_id INTEGER Rename | Drop
weekday INTEGER Rename | Drop
start_time TEXT Rename | Drop
end_time TEXT Rename | Drop
recurring INTEGER Rename | Drop
year_week TEXT ✓ Rename | Drop

Foreign Keys

Column Destination
trainer_id trainers.id

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_availability_trainer
  • trainer_id
  • weekday
 SQL 
CREATE INDEX idx_availability_trainer
ON availability(trainer_id, weekday)
Drop
idx_availability_week
  • trainer_id
  • year_week
 SQL 
CREATE INDEX idx_availability_week
ON availability(trainer_id, year_week)
Drop