Staging-Management-System Database

The SMS Database consists of three schemas for each of the individual main services that contain our business logic. Each of the schemas is interconnected within their own service and do not have any contact with any other schema inside it.

Survey Service Schema

The survey service schema consists of eight tables; responses, editors, survey history, junction_survey_questions, answers, surveys, questions, and question type.

Responses

The Id acts as the primary key for the table and answers_id and survey_id acts as a foreign key to the answers and survey table respectively.

Editors

The Id acts as the primary key for the table and survey_id acts as a foreign key to the answers table.

Survey_History

The Id acts as the primary key for the table and survey_id acts as a foreign key to the survey table.

Junction Survey Questions

The Id acts as the primary key for the junction table and survey_id and question_id acts as a foreign key to the survey table and question table respectively.

Answers

The Id acts as the primary key for the table and question_id acts as a foreign key to the questions table.

Surveys

The Id acts as the primary key for the table and has no foreign keys.

Questions

The Id acts as the primary key for the table and question_type_id acts as a foreign key to the question_type table.

Question Type

The Id acts as the primary key for the table and the table has no foreign key.

User Service Schema

The user service consists of six tables; user_cohorts, cohorts, sms_users, addresses, status_history, status.

User Cohorts

The composites keys for this table are sms_user_id and cohort_id which also act as foreign keys for the sms_users table and cohort table respectively.

Cohorts

The Id acts as the primary key for the table and trainer_id, address, cotrainer_id acts as foreign keys for the trainer table, address table, and sms_user table respectively.

Status History

The Id acts as the primary key for the table and the table has no foreign key.

SMS Users

The Id acts as the primary key for the table and training address, personal_address, and user_status act as foreign keys for addresses and status table respectively.

Addresses

The Id acts as the primary key for the table and the table has no foreign key.

Status

The Id acts as the primary key for the table and the table has no foreign key.

Interview Service Schema

The interview service consists of six tables; interview, interview_feedback, associate_input, client, feedback_status, interview_format.

Interview

The Id acts as the primary key for the table and the table has no foreign key.

Interview_Feedback

The Id acts as the primary key for the table and feedback_status acts as the foreign key to the feedback_status table.

Associate Input

The Id acts as the primary key for the table and interview format and proposed format act as foreign keys to the interview_format table.

Client

The Id acts as the primary key for the table and the table has no foreign key.

Feedback Status

The Id acts as the primary key for the table and the table has no foreign key.

Interview Format

The Id acts as the primary key for the table and the table has no foreign key.