Database modeling ‐ refactor - JU-DEV-Bootcamps/ERAS GitHub Wiki

Approach

Certainly, we need a better normalization and keep clear our Database structure, so we started from beginning taking the approach that our application tries to store results for a survey.

First look

We have this basic diagram that represents a survey structure.

Flexibility

As our stake holders mention they want a flexible structure that allows different formats of surveys, to accomplish this requirement we added N:N relationship between Surveys and Section, this result in a new table to hold both ids.

History

In order to have the possibility of history for our data we added a new table SurveyInstace that represents when a student filled a survey.

Circular dependency

Breaking the circular dependency to have a better normalization in our database to avoid redundacy.

Translate to our Domain

Now that we have clear ideas, we can translate our first approach to our Domain, in order to keep the consistency across the entire application.

Conclusion

As a result of our approach, we have a normalized and easy to understand ERM Diagram.

Resources

The diagram was build using the tool dbdiagram.io. Please use the following code to edit the Diagram if its needed.

TABLE polls {
  id integer [primary key]
  name varchar
  version varchar
  uuid varchar unique
  created_date datetime
  modified_date datetime
}
 
TABLE variables {
  id integer [primary key]
  component_id integer
  name varchar
  created_date datetime
  modified_date datetime
}
 
TABLE components {
  id integer [primary key]
  name varchar
  created_date datetime
  modified_date datetime
}
 
TABLE poll_variable {
  id integer [primary key]
  poll_id integer
  variable_id integer
  created_date datetime
  modified_date datetime
}
 
TABLE students {
  id integer [primary key]
  full_name varchar
  email varchar
  uuid varchar
  created_date datetime
  modified_date datetime
}

TABLE student_details {
  id integer [primary key]
  student_id integer unique
  enrolled_courses integer
  graded_courses integer
  time_delivery_rate integer
  avg_score decimal
  courses_under_avg decimal
  pure_score_diff decimal
  standard_score_diff decimal
  last_access_days integer
  created_date datetime
  modified_date datetime
}
 
TABLE cohorts {
  id integer [primary key]
  name varchar
  course_code varchar unique
  start_date date
  end_date date
  created_date datetime
  modified_date datetime
}
 
TABLE student_cohort {
  id integer [primary key]
  score decimal
  notes varchar
  student_id integer
  cohort_id integer
  created_date datetime
  modified_date datetime
 
}
 
TABLE answers {
  id integer [primary key]
  risk_level integer
  answer_text varchar
  poll_instance_id integer
  poll_variable_id integer
  created_date datetime
  modified_date datetime
}
 
TABLE poll_instances {
  id integer [primary key]
  student_id integer
  uuid varchar
  created_date datetime
  modified_date datetime
}
 
 
Ref: variables.component_id > components.id
 
Ref: poll_variable.poll_id < polls.id
 
Ref: poll_variable.variable_id < variables.id
 
Ref: poll_instances.student_id > students.id
 
Ref: answers.poll_variable_id > poll_variable.id
 
Ref: answers.poll_instance_id > poll_instances.id
 
Ref: student_details.id - students.id
 
Ref: student_cohort.student_id > students.id
 
Ref: student_cohort.cohort_id > cohorts.id