In today's exercise we want to model an internal web application for a company.
Management and supervisors want to do employee interviews twice a year. Before each interview, each employee is assessed by himself and his supervisor independently.
There are around 20 statements in which employees have to be assesed. Each statement belongs to one of -currently- five topics. For example the topic "Way of working" contains the statements
-
You are working independently.
-
You are working structured.
Each of these twenty statements can be valued by choosing a numerical value between 1 (does not apply) and 5 (applies fully) and allows a comment.
The company uses Microsoft Azure Cloud.
Additional questions
- How could users be authenticated?
- Where could the web application be deployed?
- What database management system would you use and why?
- How could be the numerical value range for an assessment be forced?
- What would happen if an employee is valued by two supervisors?
Solution
- Users can be authenticated with help of OAuth.
- The application could be deployed on internal servers, a service inside the Azure cloud or any other server on the internet.
- You can choose whatever database management system you want :-)
- The numerical value for the value column of an assessment can be enforced through a database constraint or inside the application's business logic. You can also combine both methods.
- Having two supervisors makes the schema more complex:
- You could add a second column to the interview table, named interview.supervisor_2_id int (NULL).
- A better solution would be removing the interview.supervisor_id column and adding a new table supervisor_in_interview which contains a row for each supervisor of an interview. This would allow you to have any number of supervisors. Your business logic is responsible that atleast one supervisor is assigned.
Schema
Table user {
id integer [primary key, increment]
// OAuth can be used for authentication, userPrincipalName can be used as username
// @see https://docs.microsoft.com/en-us/graph/api/user-get?view=graph-rest-1.0&tabs=http
userPrincipalName string(255) [unique, not null]
// no password is needed if we are using OAuth and login via Azure
}
// twice a year, for each employee a new row will be inserted
Table interview {
id integer [primary key, increment]
// we don't use user_id and user_2_id. instead we want to fall back to the user's role
employee_id integer [not null, ref: > user.id]
supervisor_id integer [not null, ref: > user.id]
// this is the date when the interview is planned; could be also just be a DATE type
planned_at datetime [not null]
// this is the date, when the interview has happened; DATE could be also a valid data type
interviewed_at datetime [null]
}
// for each interview, there are 40 rows insides this table (20 rows from the employee's view and 20 rows from the supervisor's view)
Table assessment {
id integer [primary key, increment]
interview_id integer [not null, ref: > interview.id]
// in this table it's not relevant if the assessment has been done by an employee or supervisor role
user_id integer [not null, ref: > user.id]
// an assessment is based upon a statement
statement_id integer [not null, ref: > statement.id]
// the value is between 1 and 5; this definition would also allow 0 or 6; we could enforce the values with a database constraint or inside the application's logic
value integer(1) [not null]
// comment is optional
comment text [null]
}
Table statement {
id integer [primary key, increment]
// "You are working independently"
content text [not null]
// a statement belongs to a topic
topic_id integer [null, ref: > topic.id]
}
// even if there are only 5 topics named, this should be its own table
Table topic {
id integer [primary key, increment]
// "Way of working"
name char(255) [not null, unique]
}