The database I'm designing has an employees
table; there can be multiple types of employees, one of which are medical employees. The database needs to also describe a many-to-many relation between medical employees and what competences they have.
Is it okay to create a table medical_employees
with only an id
column, whose only purpose is to specify which employees are medics? The id
column has a foreign key constraint that references the employees
table. The code below should make my question clearer:
/* Defines a generic employee */
CREATE TABLE employees (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL
);
/* Specifies which employees are medics */
CREATE TABLE medical_employees (
id INT NOT NULL,
FOREIGN KEY (id) references employees(id);
);
/* Specifies what competences a medic can have */
CREATE TABLE medical_competences (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL
);
/* A many-to-many relation between medical employees and
their competences. */
CREATE TABLE medical_employees_competences (
id INT PRIMARY KEY AUTO_INCREMENT,
medic_id INT NOT NULL,
competence_id INT NOT NULL,
FOREIGN KEY (medic_id) REFERENCES medical_employees(id),
FOREIGN KEY (competence_id) REFERENCES medical_competences(id)
);
Why a separate table for that. Why not just create a
BIT/Boolean
field sayIsMedical
and set that toTRUE
for medical employees inemployee
table likeThat way, say if you want to get all medical employees from
Employee
table; you will just have to do a single filter inWHERE
condition sayingWHERE IsMedical = true
. Whereas, if you go by a separate table then you will have perform aINNER JOIN
withmedical_employees
andemployees
table which I believe would be more costly and unnecessary.