Database schema for similar form array select inputs

I have a multi-step form with questions like that:

Skills:
    1- Writing:
	    1- Below Average
   		2- Average
    	3- Above Average
	2- Reading:
		1- Below Average
    	2- Average
    	3- Above Average

Development:
	1- Walking:
		1- Late
    	2- Normal
    	3- Early
	2- Speaking:
		1- Late
    	2- Normal
    	3- Early

For that, I created <select> elements with the question as the array index and the option as the value:

<!-- Skills -->
<label>Writing:</label>
<select name="skills[1]">
	<option value="1">Below Average</option>
	<option value="2">Average</option>
	<option value="3">Above Average</option>
</select>
<label>Reading:</label>
<select name="skills[2]">
	<option value="1">Below Average</option>
	<option value="2">Average</option>
	<option value="3">Above Average</option>
</select>


<!-- Development -->
<label>Walking:</label>
<select name="development[1]">
    <option value="1">Late</option>
    <option value="2">Normal</option>
    <option value="3">Early</option>
</select>
<label>Speaking:</label>
<select name="development[2]">
    <option value="1">Late</option>
    <option value="2">Normal</option>
    <option value="3">Early</option>
</select>

For example skills[1] 1 => Writing, skills[2] 2 => Reading

So the result post data would look like that:

$_POST['skills'] = [
    1 => 1,
    2 => 3
];

$_POST['development'] = [
    1 => 2,
    2 => 1
];

There are multiple questions with this structure ~ +10.

I'm thinking of 2 methods for the tables:

1- Create a separate table for each question like:

skills:

id | skill
1  | Writing

skills_options:

id | option
1  | Below Average

skills_option_skill: (Pivot table)

id | skill_id | option_id
1  |     1    |    1

The cons of that method are that I would have to create around 20 - 30 tables and then join all these tables to display the data.

2- Create 1 table for questions and options: questions:

id | question
1  | Skills
2  | Development
3  | Skills Options
4  | Development Options

selects:

id  |  select         | question_id
1   |  Writing        | 1
2   |  Reading        | 1
3   |  Walking        | 2
4   |  Speaking       | 2
5   |  Below Average  | 3
6   |  Average        | 3
7   |  Above Average  | 3
8   |  Late           | 4
9   |  Normal         | 4
10  |  Early          | 4

In this case, I don't know if it would be possible to display the data.

Which method is better or there is a better structure?

Swaz

3 years ago

Level 20

You could try something like this:

sections
id | name
-- | ----
1  | Skills
2  | Development
etc ...

categories
id | section_id | name
-- | ---------- | ----
1  | 1          | Writing
2  | 1          | Reading

3  | 2          | Walking
4  | 2          | Speaking
etc ...

options
id | name
-- | ----
1  | Below Average
2  | Average
3  | Above Average

4  | Late
5  | Normal
6  | Early
etc ...

category_option // (many to many relationship)
category_id | option_id
----------- | ---------
1           | 1
1           | 2
1           | 3

2           | 1
2           | 2
2           | 3

3           | 4
3           | 5
3           | 6

4           | 4
4           | 5
4           | 6
etc ...

answers
id | user_id | category_id | option_id
-- | ------- | ----------- | ---------
1  | 1       | 1           | 2
2  | 1       | 2           | 1
3  | 1       | 3           | 5
etc ...

Example Usage:

$sections = Section::with('categories.options')->get();

Please or to participate in this conversation.