school.dbml

Table school {
  id integer [pk]
  name varchar(25) [not null]
  street_name varchar(25)
  city varchar(25)
  postcode varchar(10)
  google_map_link varchar(50)
  telephone varchar(15)
  email varchar(50) [not null, unique]
  hours varchar(25)
  rating varchar(25)
}

Table person {
  id integer [pk]
  first_name varchar(20) [not null]
  last_name varchar(20) [not null]
  email varchar(50) [unique] // optional
}

Table staff {
  id integer [pk]
  person_id integer [ref: < person.id, not null]  // 1-many
  school_id integer [ref: < school.id, not null]  // 1-many
}

Table department {
  id integer [pk]
  name varchar(50) [not null]
  school_id integer [ref: < school.id, not null]  // 1-many
}

Table job {
  id integer [pk]
  job_title varchar(50) [not null]
  department_id integer [ref: < department.id, not null] // 1-many
}

Table staff_job {
  staff_id integer [ref: <> staff.id, not null]  // many-many
  job_id integer [ref: <> job.id, not null]  // many-many

  indexes {
    (staff_id, job_id) [pk]  // Composite pk
  }
}

Table tutor_group {
  id integer [pk]
  group_code varchar(3) [not null, unique]
}

Table governor {
  id integer [pk]
  person_id integer [ref: < person.id, not null]
  job_id integer [ref: < job.id, not null]
  school_id integer [ref: < school.id, not null]
  start_date date [not null]
  end_date date [not null]
}

Table student {
  id integer [pk]
  person_id integer [ref: < person.id, not null]  // 1-many
  school_id integer [ref: < school.id, not null]  // 1-many
  tutor_group_id integer [ref: < tutor_group.id, not null]  // 1-many
  year_group integer [not null]
}

Table subject {
  id integer [pk]
  subject varchar(50) [not null]
}

Table student_subject_choice {
  student_id integer [ref: < student.id, not null]
  subject_id integer [ref: < subject.id, not null]

  indexes {
    (student_id, subject_id) [pk]  // Composite pk
  }
}