CakePHP 3: How to create Job Interview system?

#1

I need to make job interview system, where each job can have an indefinite number of questions.

The author can ask questions and provide possible answers as radio buttons, or allow the applicant to enter himself answer in textarea.

System example

Jobs -> Interviews (Q&A)
Jobs -> Applications <- Users
Interviews (Q&A) <-> Applications

Can someone give advice on this, how to create a database?

#2

Answering this question vastly exceeds the reasonable scope of questions and answers in most contexts. Even moreso in the context that this site is for help and discussion specific to CakePHP while your question is far more generic and specific to database design.

Assuming you know how to create a database and tables, and know how to set up primary indices and know about normal design, the best I can offer is:

“indefinite” number of questions implies you will need a questions table to hold all the questions entered by the author. This table would have a column with the foreign key to the interviews table which basically groups all questions for a single interview and can include date and other information… and a field to hold the text of the question. If one question only ever gets used once for a single applicant, then you can store your answers as a column in the questions table. Otherwise, you need a separate answers table that references the question_id (and the applicant_id)

If you needed the ability to reuse the questions in different interviews then you wouldn’t use the interview_id foreign key anymore, and instead would create a bridge table with two foreign key columns to reference both the interview and the question. You’d also have to create a separate table to hold the answers and reference back to the interview AND question tables.

It gets a little more complicated if you want to set up multiple choice answers to each question because these answers would also have to live in a table (‘answers’) and these would have to include a question_id foreign key. Again, if these multiple choice answers need to be reused, you have to abstract them out and use another bridge table.

Finally you’d probably want to include a “question_type” field in the questions table to identify whether the answer is going to be text or multiple choice.

There are nuances and variations to this that could change the number of tables and where different keys and fields live. It’s simply impossible without being intimately involved in the design to make a proper recommendation for database design.

1 Like
#3

@calzone Thank you for your reply to topics. Yes, question and theme is more related to database design, but applications will be developed in CakePHP3.

I use CakePHP since version 1.1, and have no problem to create database tables and their relationships, but here the first time I have a more complicated task.

Each job post can have their questions written by the author (agent) and give a different type of answer, once predetermined, sometimes not. A similar system is seen on some dating and freelancers apps.

Example:

Q1
A1: Yes, No (radio)

Q2
A2: Yes, No, Maybe (radio)

Q3
A3: 1,2,3,4,5 (select)

Q4
A4: textarea

I have to go deeper into this task to find a solution, and will be shared with the CakePHP community.
Any suggestions welcome.
Thanks.