mardi 14 juin 2016

DB structure - form with dynamic number of options

I've been reading similar questions, but I think my case is a bit more complicated.

I have a form that register items. These items may have options with sub-options (checkboxes and radio buttons):

  • The number of checkboxes and radio buttons may decrease/increase but the real pain to design a good structure is for the checkboxes, as these must have (at least I think so) a fixed name column for each one.

  • The case for radio buttons is easier as I just assign an id to each one (and save the names in a different table).

My current DB structure is simple (between parenthesis is the table/column name):

  • The items table (item) have columns of type integer (to save the id of the radio buttons).
  • Another table for the checkboxes (item_option), with columns of type integer (1 if checked, 0 if unchecked). And 1 PK column (item_id) that points to the PK column (id) of the items table.
  • And tables (again item_option) for the names of the radio buttons with a PK column (id) that points to the option column (is this understandable? Sorry for my bad english).

I think a different table containing the sub-options is better than put all the columns in the main table, right?

So, the radio buttons are stored in the main table (1 column per option) and the checkboxes in a separeted table (1 table per option):

Items table:

+-----+----------+----------+
| id  | Option_1 | Option_2 |
+-----+----------+----------+
| 123 | 3        | 1        |
+-----+----------+----------+
| 456 | 2        | 3        |
+-----+----------+----------+
| 789 | 1        | 2        |
+-----+----------+----------+

item_option_3 table (this would be needed to know which ones are checked):

+--------------+--------------+--------------+---------+
| Sub_Option_1 | Sub_Option_2 | Sub_Option_3 | item_id |
+--------------+--------------+--------------+---------+
| 1            | 0            | 1            | 123     |
+--------------+--------------+--------------+---------+
| 1            | 1            | 0            | 456     |
+--------------+--------------+--------------+---------+
| 0            | 1            | 1            | 789     |
+--------------+--------------+--------------+---------+

item_option_1-2 table (this would be used to print the names):

+-----------+--------------+--------------+
| option_id | name         | name_es      |
+-----------+--------------+--------------+
| 1         | Sub_Option_1 | Sub_Opción_1 |
+-----------+--------------+--------------+
| 2         | Sub_Option_2 | Sub_Opción_2 |
+-----------+--------------+--------------+
| 3         | Sub_Option_3 | Sub_Opción_3 |
+-----------+--------------+--------------+

What kind of structure do I need to spawn these sub options (checkboxes) dynamically?

Aucun commentaire:

Enregistrer un commentaire