3 added 17 characters in body
source | link

Just answering your specific scenario.

I would create a table for employees and the common attributes they have. Name, DOB etc.

I would then create a table for thier skills or qualifications.

I would create a foreign key linking to the employee table and use a name/value pair. So you have something like:

Empid, qualification, value
1, drivers license,  commercial
2, first aid, level 1

You could add date fields to record things like when they gained the qualification, or when it expires.

The advantage of this approach is that an employee can have as many skills as you like without altering the table schema.

The disadvantage is that the query logic is a bit more difficult when trying to find employees with multiple, mandatory or missing skills than if the were in different columns. And it gets very difficult to enforce rules and constraints at database level. Instead you must rely upon your application logic to handle this.

You could easily extend this model by having a reference table of valid skills or a table describing positions and skill requirements.

The right answer for you will depend on many factors. And the result is almost always a compromise depending on how much time you have and how sophisticated the result needs to be.

My last comment is that a flexible design is desirable. Because the real world has a habit of being messy and not following dbas rules. Org charts change. People get promoted. 1 guy has 2 jobs. People resign and then come back. Management decide that your 4 level hierarchy now has 5 levels or a 2nd tier manager now reports to another 2nd tier manager.

The right answer for you will depend on many factors. And the result is almost always a compromise depending on how much time you have and how sophisticated the result needs to be.

Just answering your specific scenario.

I would create a table for employees and the common attributes they have. Name, DOB etc.

I would then create a table for thier skills or qualifications.

I would create a foreign key linking to the employee table and use a name/value pair. So you have something like:

Empid, qualification, value
1, drivers license,  commercial
2, first aid, level 1

You could add date fields to record things like when they gained the qualification, or when it expires.

The advantage of this approach is that an employee can have as many skills as you like without altering the table schema.

The disadvantage is that the query logic is a bit more difficult when trying to find employees with multiple, mandatory or missing skills than if the were in different columns. And it gets very difficult to enforce rules and constraints at database level. Instead you must rely upon your application logic to handle this.

You could easily extend this model by having a reference table of valid skills or a table describing positions and skill requirements.

The right answer for you will depend on many factors. And the result is almost always a compromise depending on how much time you have and how sophisticated the result needs to be.

My last comment is that a flexible design is desirable. Because the real world has a habit of being messy and not following dbas rules. Org charts change. People get promoted. 1 guy has 2 jobs. People resign and then come back. Management decide that your 4 level hierarchy now has 5 levels or a 2nd tier manager now reports to another 2nd tier manager.

Just answering your specific scenario.

I would create a table for employees and the common attributes they have. Name, DOB etc.

I would then create a table for thier skills or qualifications.

I would create a foreign key linking to the employee table and use a name/value pair. So you have something like:

Empid, qualification, value
1, drivers license,  commercial
2, first aid, level 1

You could add date fields to record things like when they gained the qualification, or when it expires.

The advantage of this approach is that an employee can have as many skills as you like without altering the table schema.

The disadvantage is that the query logic is a bit more difficult when trying to find employees with multiple, mandatory or missing skills than if the were in different columns. And it gets very difficult to enforce rules and constraints at database level. Instead you must rely upon your application logic to handle this.

You could easily extend this model by having a reference table of valid skills or a table describing positions and skill requirements.

My last comment is that a flexible design is desirable. Because the real world has a habit of being messy and not following dbas rules. Org charts change. People get promoted. 1 guy has 2 jobs. People resign and then come back. Management decide that your 4 level hierarchy now has 5 levels or a 2nd tier manager now reports to another 2nd tier manager.

The right answer for you will depend on many factors. And the result is almost always a compromise depending on how much time you have and how sophisticated the result needs to be.

2 added 541 characters in body; added 12 characters in body
source | link

Just answering your specific scenario.

I would create a table for employees and the common attributes they have. Name, DOB etc.

I would then create a table for thier skills or qualifications.

I would create a foreign key linking to the employee table and use a name/value pair. So you have something like: Empid, qualification, value 1, drivers license, commercial. 2, first aid, level 1

Empid, qualification, value
1, drivers license,  commercial
2, first aid, level 1

You could add date fields to record things like when they gained the qualification, or when it expires.

The advantage of this approach is that an employee can have as many skills as you like without altering the table schema.

The disadvantage is that the query logic is a bit more difficult when trying to find employees with multiple, mandatory or missing skills than if the were in different columns. And it gets very difficult to enforce rules and constraints at database level. Instead you must rely upon your application logic to handle this.

You could easily extend this model by having a reference table of valid skills or a table describing positions and skill requirements.

The right answer for you will depend on many factors. And the result is almost always a compromise depending on how much time you have and how sophisticated the result needs to be.

My last comment is that a flexible design is desirable. Because the real world has a habit of being messy and not following dbas rules. Org charts change. People get promoted. 1 guy has 2 jobs. People resign and then come back. Management decide that your 4 level hierarchy now has 5 levels or a 2nd tier manager now reports to another 2nd tier manager.

Just answering your specific scenario.

I would create a table for employees and the common attributes they have. Name, DOB etc.

I would then create a table for thier skills or qualifications.

I would create a foreign key linking to the employee table and use a name/value pair. So you have something like: Empid, qualification, value 1, drivers license, commercial. 2, first aid, level 1

You could add date fields to record things like when they gained the qualification, or when it expires.

The advantage of this approach is that an employee can have as many skills as you like without altering the table schema.

The disadvantage is that the query logic is a bit more difficult when trying to find employees with multiple, mandatory or missing skills than if the were in different columns. And it gets very difficult to enforce rules and constraints at database level. Instead you must rely upon your application logic to handle this.

You could easily extend this model by having a reference table of valid skills or a table describing positions and skill requirements.

Just answering your specific scenario.

I would create a table for employees and the common attributes they have. Name, DOB etc.

I would then create a table for thier skills or qualifications.

I would create a foreign key linking to the employee table and use a name/value pair. So you have something like:

Empid, qualification, value
1, drivers license,  commercial
2, first aid, level 1

You could add date fields to record things like when they gained the qualification, or when it expires.

The advantage of this approach is that an employee can have as many skills as you like without altering the table schema.

The disadvantage is that the query logic is a bit more difficult when trying to find employees with multiple, mandatory or missing skills than if the were in different columns. And it gets very difficult to enforce rules and constraints at database level. Instead you must rely upon your application logic to handle this.

You could easily extend this model by having a reference table of valid skills or a table describing positions and skill requirements.

The right answer for you will depend on many factors. And the result is almost always a compromise depending on how much time you have and how sophisticated the result needs to be.

My last comment is that a flexible design is desirable. Because the real world has a habit of being messy and not following dbas rules. Org charts change. People get promoted. 1 guy has 2 jobs. People resign and then come back. Management decide that your 4 level hierarchy now has 5 levels or a 2nd tier manager now reports to another 2nd tier manager.

1
source | link

Just answering your specific scenario.

I would create a table for employees and the common attributes they have. Name, DOB etc.

I would then create a table for thier skills or qualifications.

I would create a foreign key linking to the employee table and use a name/value pair. So you have something like: Empid, qualification, value 1, drivers license, commercial. 2, first aid, level 1

You could add date fields to record things like when they gained the qualification, or when it expires.

The advantage of this approach is that an employee can have as many skills as you like without altering the table schema.

The disadvantage is that the query logic is a bit more difficult when trying to find employees with multiple, mandatory or missing skills than if the were in different columns. And it gets very difficult to enforce rules and constraints at database level. Instead you must rely upon your application logic to handle this.

You could easily extend this model by having a reference table of valid skills or a table describing positions and skill requirements.