Query

pilot.db — cards

SQL

CREATE TABLE cards  (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  trainer_id INTEGER NOT NULL REFERENCES trainers(id) ON DELETE CASCADE,
  student_id INTEGER NOT NULL REFERENCES students(id) ON DELETE CASCADE,
  card_type TEXT NOT NULL,
  -- z. B. '10er'
  total_units INTEGER NOT NULL,
  -- z. B. 10
  used_units INTEGER NOT NULL DEFAULT 0,
  price_cents INTEGER NOT NULL,
  -- 67500 = 675,
  00 €
  purchase_date TEXT NOT NULL,
  -- ISO-8601 date
  expires_date TEXT,
  -- ISO-8601 date,
  NULL = unbegrenzt
  status TEXT NOT NULL DEFAULT 'active',
  -- 'active' | 'exhausted' | 'expired' | 'cancelled'
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
)

+ Add column

Columns

Column Data type Allow null Primary key Actions
id INTEGER ✓ ✓ Rename | Drop
trainer_id INTEGER Rename | Drop
student_id INTEGER Rename | Drop
card_type TEXT Rename | Drop
total_units INTEGER Rename | Drop
used_units INTEGER Rename | Drop
price_cents INTEGER Rename | Drop
purchase_date TEXT Rename | Drop
expires_date TEXT ✓ Rename | Drop
status TEXT Rename | Drop
created_at TEXT Rename | Drop

Foreign Keys

Column Destination
student_id students.id
trainer_id trainers.id

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_cards_student student_id SQL 
CREATE INDEX idx_cards_student
ON cards(student_id)
Drop
idx_cards_trainer_status
  • trainer_id
  • status
 SQL 
CREATE INDEX idx_cards_trainer_status
ON cards(trainer_id, status)
Drop