Maintaining one's own employee profile is omnipresent for many employees. It documents previous project experience as well as the technologies and skills used.
The profile is particularly important in the consulting industry: For the acquisition of new orders, proof of the skills of individual people can be provided. Evidence is provided by z. B. the employee profiles can be anonymized and exported as PDF files.
The following fictitious project is about designing an administration for employee profiles. This is similar to the existing portals such as Xing and LinkedIn.
Customers of a consulting company initiate projects. Especially in large companies or corporations, the project is not assigned directly to the company, but to one or more departments of the company.
An employee can be assigned to one or more projects. Each project assignment is associated with a role (e.g. "Junior Consultant" or "Software Architect"), a period and the skills required for this role.
All roles and skills should be managed centrally and selected when they are assigned. A project assignment can be done for a dedicated department within the customer's company.
To ensure customer protection when exporting, it should be possible to configure for each company whether the company name is displayed. If this option is deactivated, however, the company's branch should be used.
Solution
Schema
Table branch {
id integer [primary key, not null, unique]
name char(255) [not null, unique]
}
// customer = company
Table customer {
id integer [primary key, not null, unique]
name char(255) [not null, unique]
// fallback to the company's branch if this is true
is_private boolean [not null, default: "true"]
// we assume that a customer belongs always to exactly one branch
branch_id integer [not null, ref: > branch.id]
}
// for this exercise we ignore the fact that departments can have subordinated departments
Table department {
id integer [primary key, not null, unique]
name char(255) [not null, unique]
customer_id integer [not null, ref: > customer.id]
}
Table project {
id integer [primary key, not null, unique]
name char(255) [not null, unique]
// a project belongs to a customer
customer_id integer [not null, ref: > customer.id]
}
// roles and skill should be managed globally
Table role {
id integer [primary key, not null, unique]
name char(255) [not null, unique]
}
Table skill {
id integer [primary key, not null, unique]
name char(255) [not null, unique]
// we could use an enum to differentiate between tech, soft-skills and so on
}
// projects can belong to one or more departments
Table project_in_department {
id integer [primary key, not null, unique]
project_id integer [not null, ref: > project.id]
department_id integer [not null, ref: > department.id]
}
Table project_participation {
id integer [primary key, not null, unique]
begin_at date [not null]
// nullable, because the employee can still be participating in this project
end_at date [null]
employee_id integer [null, ref: > employee.id]
role_id integer [not null, ref: > role.id]
project_id integer [not null, ref: > project.id]
// optionally, a participation can be only for a specific department
department_id integer [null, ref: > department.id]
}
// track the skills an employee has with the participation in a project
Table skill_in_project_participation {
id integer [primary key, not null, unique]
project_participation_id integer [not null, ref: > project_participation.id]
skill_id integer [not null, ref: > skill.id]
}
Table employee {
id integer [primary key, not null, unique]
email char(255) [not null, unique]
// more metadata goes here
}