Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2016
    Posts
    2

    Unanswered: Structuring a Survey Database scheme

    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:

    Click image for larger version. 

Name:	dbscheme.jpg 
Views:	0 
Size:	86.9 KB 
ID:	17220

    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..

    Click image for larger version. 

Name:	dbscheme2.jpg 
Views:	0 
Size:	87.8 KB 
ID:	17221

    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?

    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 |
    +------------+------------+---------+-----------+


    Thank you for your time!
    Last edited by notflip; 12-08-16 at 12:18.

  2. #2
    Join Date
    Oct 2007
    Posts
    154
    Provided Answers: 9
    I would think you would have the ID of who completed the survey(coach or employee) as the lead column, then the employeeID of who was being surveyed. You might want to go a little further down the list of Forums here and ask in the Design forum.

  3. #3
    Join Date
    Dec 2016
    Posts
    2
    Quote Originally Posted by DNance View Post
    I would think you would have the ID of who completed the survey(coach or employee) as the lead column, then the employeeID of who was being surveyed. You might want to go a little further down the list of Forums here and ask in the Design forum.
    Thank you for your answer! I'm trying to amigine how that db would look like
    So you would add one more table?

    Regards

  4. #4
    Join Date
    Oct 2007
    Posts
    154
    Provided Answers: 9
    no, I would have the answers table you displayed above and instead of coachID, I would call it SURVEY_TAKER_ID or something along those lines and it would contain who did the survey on the employeeID. In this manner you have what you wanted and you do not have to do AND OR logic with the coachID column

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •