CODEWITHSUNDEEP
ruby-on-rails-3database-design
Ben Gartner
Ben Gartner

Reputation: 14923

Database design mapping employees to positions on a job shift

I'm designing a database for an employee scheduling system. Each shift has M different roles that must be filled and each of N employees is qualified for an combination of those M different roles.

The naive design is to put M boolean flags into each employee record. Does this require me to constrain the number of roles that can be supported at design time or do modern databases have a way to avoid the potentially unbounded MxN table?

Upvotes: 1

Views: 312

Answers (1)

natedavisolds
natedavisolds

Reputation: 4295

How about something like:

Roles
  id
  name

Employee
  id
  name

Qualifications
  id
  employee_id
  role_id

Shifts
  id 
  role_id
  date
  shift_number

EmployeeShifts
  id
  shift_id
  employee_id

Upvotes: 2

Related Questions