#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    226
    Rep Power
    6

    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:

    dbscheme.jpg

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

    dbscheme2.jpg

    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!
    Last edited by notflip; December 8th, 2016 at 12:19 PM.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,732
    Rep Power
    4288
    this is extremely confusing, sorry

    do you have a specific question?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo