CODEWITHSUNDEEP
mysqlsqldatabase
user6313136
user6313136

Reputation: 345

Most efficient way to design an employee with multiple employers

CREATE TABLE IF NOT EXISTS `default_schema`.`Employee` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(200) NOT NULL,
  `_last_name` VARCHAR(200) NOT NULL,
  `employee_id` INT(11) NULL,
  `employer_id` INT(11) NOT NULL,
  PRIMARY KEY (`id`),

CREATE TABLE IF NOT EXISTS `default_schema`.`contractor` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `contractor_name` VARCHAR(200) NOT NULL,
  `contract_number` VARCHAR(20) NOT NULL,
  `contract_term` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id`),

An 'employee' can have many different employers ('contractor'). So what would be the best way to show this in a database? Create a few fields in 'employee' like 'Employer1', 'Employer2, etc. Or create a third table to describe the connection between an employee and employer

Upvotes: 1

Views: 371

Answers (2)

spencer7593
spencer7593

Reputation: 108430

We typically resolve a many-to-many relationship by introducing a third table, called by various names... relationship table, junction table, link table, et al.

As an example:

CREATE TABLE `employment` 
( employee_id   INT(11) NOT NULL  COMMENT 'PK, FK ref employee.id'
, contractor_id INT(11) NOT NULL  COMMENT 'PK, FK ref contractor.id'
, PRIMARY KEY (employee_id, contractor_id)
, UNIQUE KEY employment_UX2 (contractor_id, employee_id)
, CONSTRAINT FK_employment_contractor FOREIGN KEY contractor_id REFERENCES contractor(id) 
, CONSTRAINT FK_employment_employee   FOREIGN KEY employee_id   REFERENCES employee(id)
)

To represent a relationship between "employee" and "contractor", we insert a row to this table.

Sometimes, the relationship itself may have attributes. In this example, there might be some dates we want to keep track of (contracted, start, termination). There might be a status ("negotiated", "in progress", "completed", ...)

Upvotes: 3

Bill the Lizard
Bill the Lizard

Reputation: 405815

New fields in the employee table won't scale. You'd have to know in advance the maximum number of employers an employee can have (and that's guaranteed to change). It's better to model this as multiple rows for an employee, each one representing a different employer. This is best done in a third table that you use to join employees and employers, since an employer can also have multiple employees.

Upvotes: 2

Related Questions