I am working on a home service application with laravel and react where users can submit orders for different services.
When a user is going to submit an order, the application is going to ask him/her some questions about the service details (like how many pipes are broken, what time does he/she like the service to be done and so on.)
So I need to create a services table:
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(200) | NO | | NULL | |
+---------+--------------+------+-----+---------+----------------+
And an orders table:
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| service_id | int unsigned | NO | | NULL | |
| user_id | int unsigned | NO | | NULL | |
| address_id | int unsigned | NO | | NULL | |
+------------+--------------+------+-----+---------+----------------+
Then I have to create a service_questions table:
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| service_id | int unsigned | NO | | NULL | |
| type | varchar(30) | NO | | NULL | |
| name | varchar(200) | NO | | NULL | |
+------------+--------------+------+-----+---------+----------------+
Questions may or may not have options. So the type attribute can have multiple values, like: single_select, multi_select, timestamp (for dates), text, file and … .
So I need a service_question_options table:
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| uuid | uuid | NO | PRI | NULL | auto_increment |
| question_id | int unsigned | NO | | NULL | |
| content | varchar(255) | NO | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
I created a uuid field as identifier for this table so I would be able to store it in a varchar type field in the following table. For storing user’s answers to the questions, I create an order_answers table, like this:
+-------------+----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| order_id | int unsigned | NO | | NULL | |
| question_id | int unsigned | NO | | NULL | |
| option | varchar(255) | YES | | NULL | |
+-------------+----------------+------+-----+---------+----------------+
Now that I explained all the tables, I need to mention the problems that I encountered. First, I receive user’s answers in an array like this:
[
'first_question_id' => [
'an_option_id',
'another_option_id',
],
'second_question_id' => 'a_long_text',
'third_question_id' => 'a_timestamp',
...
]
As you can see above, because there are different types of questions, the users answers may include question option uuid, a text, a timestamp and so on. So my first problem is that I need to validate all the question ids and option ids that user has sent, but I think that takes a lot of reading from database and may take a long time.
My second problem is the option attribute field type in order_answers table. What field type should it have, so I can store different types of data in it (uuid, text, datetime, urls for files and …)? Is it the best way or should I create different columns for different types of user answers? for example:
+-------------+----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| order_id | int unsigned | NO | | NULL | |
| question_id | int unsigned | NO | | NULL | |
| option_id | uuid | YES | | NULL | |
| text | text | YES | | NULL | |
| datetime | timestamp | YES | | NULL | |
+-------------+----------------+------+-----+---------+----------------+
But, in this way, I will have lots of null cells in the table.
My third question is how should I read user orders from database, so I do not have to make lots of joins? Should I create a view?
Actually, a solution came to my mind that I’m not sure of. Lets assume we create the order_answers table like this:
+-------------+----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| order_id | int unsigned | NO | | NULL | |
| question | varchar(200) | NO | | NULL | |
| option | varchar(255) | YES | | NULL | |
+---------+---------------+----+-----+---------+-----------------------+
Then I receive questions and user answers content and not uuid or id. So I will not need to validate them and When reading from database, there is no need for any joins. Is it a good solution?
What is your best design for an application like this?