Structuring a Survey Database scheme

Question

I'm building a database for a survey system.

We have employees that fill in a survey using categories that have questions. The employee can give him/herself a score (for example 6/10). Now we also have coaches which will fill out the same test for that employee.

I created the following database scheme but I'm not sure about the layout. For example:

A different approach is to get the coach_id in the answers table from the employees, since i'll be using roles anyway to check if a certain employee is a coach..

Entities

• clients (or companies) have employees assigned to them. All employees of a client (company) will have to make a scan (survey)
• users are general login-able entities, they have a username, password and have roles
• there's 2 roles employee and coach, a coach can fill in the survey for certain employees, so we have a score of the employee, and of the coach.
• a category has many questions
• answer (see example just down)
• a scan is a collection of categories and questions, so 1 client (company) can have many scans over the years.

Example Table for Answers

+------------+------------+---------+-----------+
| EmployeeID | QuestionID | CoachID | Answer    |
+------------+------------+---------+-----------+
|          1 |         10 | null    | 5/10      |
|          2 |         11 | null    | 8/10      |
|          3 |         12 | null    | 6/10      |
|          1 |         10 | 1       | 5/10      |
|          2 |         11 | 1       | 8/10      |
|          3 |         12 | 1       | 6/10      |
+------------+------------+---------+-----------+

Coaches

I have some users that are coaches, so when a coach_id is filled in in the answers table, we assume that a coach filled in this report. I'm not sure this is the way to go about that?

Employees

I'm using an employees table, they are a type of user.. But for the coaches I don't use a different model. Should I do this? Since a coach is also like an employee but with a different roles. I'm using roles for everything, but I'm just wondering if those employees/coaches tables are needed?

Thank you for your time!

Answer 1

Your data model doesn't need to perfectly encapsulate all your business logic, and in fact it generally neither can nor should. This is not only because business logic works by acting on already-structured data, but also because it is subject to change: if employees suddenly need to be able to use surveys to rate coaches, your application has to handle it, and if you've baked your permissions model into your schema you're looking at major revisions to the most fundamental level of your system. Not a good place to be.

When you're designing your schema, you need to be mindful of the rules you're going to impose on your data, but only in that you need to make it possible to execute them. Your principal concern should be expressing the underlying structure as elegantly as is reasonable: here, you have users. Some of them can fill out a survey only as themselves. Others may fill out a survey for any user, or possibly any user not in their own role; I'm not clear on that but either way it's a business rule, not a structural difference, so the distinction really doesn't matter.