0

I am designing the following application: A job applcation system allowing for students to apply for jobs.

Basically companies can create custom forms that students who are interested in a job with their company, must fill out.

I have the following tables:

// this stores the name of the form that companies want students to fill out:

create table forms (
`id` INT(11),
`company_id` INT(11),
`name` varchar(255),
`date_added` date,
 PRIMARY KEY (`id`));


CREATE TABLE form_options
(
`id` INT(11) auto_increment,
`company_id` INT(11),
`form_id` INT(11),
`question` VARCHAR(255),
`active` int(2),
`position` int(4),
`type`  enum('ESSAY', 'QUESTION', 'YESNO', 'CHECKBOX', 'DROPDOWN', 'RADIO', 'FILE'),
`required` tinyint(1),
`data` text,
`date_added` date,
 PRIMARY KEY (`id`));

form_options stores each question that the company adds to a form (stored in forms table). So let us say that company A creates a form A and wants to ask the following question to students:

How would you describe yourself? A. nice B. responsible C. hardworking D. talented

the following row gets added to the forms table:

forms:

  • id: 1
  • company_id: 1
  • name: form A (name of the form)
  • date_added: 2011-12-28

and the following row gets added to the form_option table

  • id: 1
  • company_id: 1
  • form_id: 1
  • question: How would you describe yourself?
  • active: 1 (Is active question)
  • position: 1
  • type: RADIO (Company wants this to be a radio button type question)
  • required: 1 (YES, question is required.)
  • data: {a: nice; b: responsible; c: hardworking; d: talented} (this is a serialized array storing the possible options the student has for the question)

The problem is that there are many companies. When a student applies for a company, they have to fill out the form for that company in addition to the general form (that all students fill out). I am trying to figure out the best way to store the student's answers since each field is different. Like what the best table structure for storing their answers to the company forms would be considering a question could be an essay (TEXT), a radio button list type question (meaning the value is an integer or letter), etc. That means that the student's answer to a form question can be in the form of an essay (500 chars or more), a letter (as an answer to a radio button type question), or a line of text, etc

thanks!

Michael Durrant
  • 93,410
  • 97
  • 333
  • 497

2 Answers2

0

I don't know php so can't provide a full answer, but based on my experience in other areas and languages I would recommend calling form_options form_questions instead.

Then have a form_answers table that has the form_id and the question_id and the answer.

You'll also want to rename 'form' to something like application_form.

So with that you could have tables: 

application_form (id,name)
application_question (id,application_form_id)
application_answer (id,application_form_id, application_question_id)

I would actually DRY that up into: 

application_form (id,name)  
question (id, application_form_id)  
answer (id, question_id, application_form_id)

Careful with questioms and answers though.
Once you realize the full complexity and flexibility required you can easily end up needing to have a What mysql database tables and relationships would support a Q&A survey with conditional questions?

Community
  • 1
  • 1
Michael Durrant
  • 93,410
  • 97
  • 333
  • 497
0

A text field type would work fine for the answer. You can store nice text answers in the values of radios, checkboxes, and dropdowns.

answers

id (int)
student_id (int)
form_option_id (int)
answer (text)

essay, question: this is self-explanatory

yesno: this will be a radio im assuming, so just store the value of the radio option which can be 'yes' and 'no'

checkbox: store the value of the checkbox

dropdown: store the value of the dropdown

radio: store the value of the radio

file: store the path to the file

Galen
  • 29,976
  • 9
  • 71
  • 89