schema.yml

Employee:
  columns:
    id:
      type: integer
      primary: true
      autoincrement: true
    sf_guard_user_id:
      type: integer
    start_date:
      type: date
    position_id:
      type: integer
    department_id:
      type: integer
  relations:
    User:
      class: sfGuardUser
      foreignType: one
      foreignAlias: Employee
    Specialties:
      class: EmployeeSpecialty
      refClass: EmployeeSpecialtyRef
      local: employee_id
      foreign: employee_specialty_id
      foreignAlias: Employees
    Ranks:
      class: EmployeeRank
      refClass: EmployeeRankRef
      local: employee_id
      foreign: employee_rank_id
      foreignAlias: Ranks

Manager:
  inheritance:
    extends: Employee
    type: column_aggregation

Department:
  columns:
    id:
      type: integer
      primary: true
      autoincrement: true
    title:
      type: string(155)
    manager_id:
      type: integer
  relations:
    Manager:
      class: Manager
      local: manager_id
      foreign: id
      foreignType: one
      foreignAlias: managedDepartment
    Employees:
      class: Employee
      local: id
      foreign: department_id
      type: many

Rank:
  columns:
    id:
      type: integer
      primary: true
      autoincrement: true
    title:
      type: string(155)
    description:
      type: blob

Specialty:
  columns:
    id:
      type: integer
      primary: true
      autoincrement: true
    title:
      type: string(155)

Position:
  columns:
    id:
      type: integer
      primary: true
      autoincrement: true
    title:
      type: string(155)

Badge:
  columns:
    id:
      type: integer
      primary: true
      autoincrement: true
    title:
      type: string(155)

Review:
  columns:
    id:
      type: integer
      primary: true
      autoincrement: true
    date:
      type: date
    citizenship:
      type: boolean
      default: 0
    proficiency:
      type: boolean
      default: 0
    peer_review:
      type: boolean
      default: 0
    metrics:
      type: boolean
      default: 0
    comments:
      type: blob

Payout:
  columns:
    id:
      type: integer
      primary: true
      autoincrement: true
    achievement_id:
      type: integer
    status:
      type: enum
      values: [Paid, Unpaid]
    date:
      type: date

EmployeeSpecialtyRef:
  columns:
    id:
      type: integer
      primary: true
      autoincrement: true
    employee_id:
      type: integer
    employee_specialty_id:
      type: integer
  relations:
    Employee:
      class: Employee
      foreignType: many
      foreignAlias: SpecialtyRef
    EmployeeSpecialty:
      class: EmployeeSpecialty
      foreignType: many
      foreignAlias: EmployeeRef

EmployeeSpecialty:
  columns:
    id:
      type: integer
      primary: true
      autoincrement: true
    specialty_id:
      type: integer
    date_acquired:
      type: date
    level:
      type: integer
    notes:
      type: blob
  relations:
    Specialty:
      class: Specialty
      foreignType: one
      foreignAlias: EmployeeSpecialty

EmployeeRankRef:
  columns:
    id:
      type: integer
      primary: true
      autoincrement: true
    employee_id:
      type: integer
    employee_rank_id:
      type: integer
  relations:
    Employee:
      class: Employee
      foreignType: many
      foreignAlias: RankRef
    EmployeeRank:
      class: EmployeeRank
      foreignType: many
      foreignAlias: EmployeeRef

EmployeeRank:
  columns:
    id:
      type: integer
      primary: true
      autoincrement: true
    rank_id:
      type: integer
    date_acquired:
      type: date
    notes:
      type: blob
  relations:
    Rank:
      class: Rank
      foreignType: one
      foreignAlias: EmployeeRank

EmployeeBadge:
  columns:
    id:
      type: integer
      primary: true
      autoincrement: true
    employee_id:
      type: integer
    badge_id:
      type: integer