Tables whose sole purpose is to indicate a subset of another table

Question

There is an employees table in my database; there may be several types of employees, one of which is medical workers. The database should also describe the many-to-many relationship between healthcare providers and what competencies they have.

Is it possible to create a medical_employees table only with an id column whose sole purpose is to indicate which employees are doctors? The id column has a foreign key constraint that refers to the employees table. The following code 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) ); 

Answer 1

Why do we need a separate table. Why not just create a BIT/Boolean field to say IsMedical and set it to TRUE for medical workers in the employee table, for example

 /* Defines a generic employee */ CREATE TABLE employees ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, IsMedical BIT(1) ); 

So, tell me if you want all health workers from the employee table; you just need to make one filter in WHERE , saying WHERE IsMedical = true . Whereas if you go into a separate table, you must execute the INNER JOIN table with the medical_employees and employees table, which, in my opinion, will be more expensive and unnecessary.

Answer 2

+1 for a response from @Rahul, another alternative is to create an attribute in the employee table. Although I would not use BIT, because there are errors in this data type. Just use BOOLEAN or TINYINT.

But the way you have it, creating a second table has the following advantage: medical_employees_competences implicitly limited to referring only to medical professionals. He cannot refer to someone if they are not in this table.

Another way to provide this restriction is to make the foreign key as follows:

 CREATE TABLE employees ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL IsMedical BOOLEAN DEFAULT 0, KEY (id, IsMedical) ); /* A many-to-many relation between medical employees and their competences. */ CREATE TABLE medical_employees_competences ( id INT PRIMARY KEY AUTO_INCREMENT, IsMedical BOOLEAN DEFAULT 1, /* only put 1 in this column */ medic_id INT NOT NULL, competence_id INT NOT NULL, FOREIGN KEY (medic_id, IsMedical) REFERENCES medical_employees(id, IsMedical), FOREIGN KEY (competence_id) REFERENCES medical_competences(id) ); 

Now you can achieve the same limitation that you can only refer to healthcare providers using the second table.

Answer 3

Yes, this is normal, this is a simple relational idiom, and this is what you should do. (You can search for SQL subtypes and supertypes.)

If you have shared subtyping, for example, other types of employees in which an employee can be of only one type, there are SQL idioms to limit this to be as declarative as possible. This may include a constant-type discriminator column in the supertype that describes which single subtype should have its identifier. ( IDEF1X idiom.) There is also an idiom associated with this type discriminator, also in subtypes, at one time avoiding further non-declarative restrictions. For the first, see (Answer) How to implement referential integrity in subtypes . (An explanation of the former, albeit degrading to the latter.) For the latter, see (Conference Document) Foreign superbooks and constant links .