lundi 17 août 2020

How to store multiple checkbox data into the database?

Suppose you have a police application form, written on Java (Android) and a police officer will be filling it on an Android tablet.

Along with a lot of common stuff (name, address, age, etc.) you get into the point of having the next question:

What kind of violence did you suffer?

  • Physical
  • Psychological
  • Sexual
  • Family
  • Other (which one?)

The question will be presented with checkboxes, an extra text input for the 'Other' checkbox and the police officer can select any, none or many of them.

What would be the best way to store this data in the database?

  1. As single column values: PhysicalViolence, PsychologicalViolence, SexualViolence, FamilyViolence, OtherViolence, assign true or false (or 1 or 0) to their values and another column called OtherViolenceSpecific:
+--------------+-------------------+------------------------+-----------------+-----------------+----------------+------------------------+
| PoliceFormID |  PhysicalViolence |  PhysiologicalViolence |  SexualViolence |  FamilyViolence |  OtherViolence |  OtherViolenceSpecific |
+--------------+-------------------+------------------------+-----------------+-----------------+----------------+------------------------+
|           10 |                 1 |                      1 |               0 |               0 |              0 |                        |
|           11 |                 1 |                      1 |               0 |               1 |              1 |  VIOLENCE IN WORKPLACE |
+--------------+-------------------+------------------------+-----------------+-----------------+----------------+------------------------+
  1. Create a second table (TypeOfViolence) with the 5 values, then create a another table to connect both tables:
+----+----------------+
| Id |    Violence    |
+----+----------------+
|  1 |  Physical      |
|  2 |  Psychological |
|  3 |  Sexual        |
|  4 |  Family        |
|  5 |  Other         |
+----+----------------+
+--------------+-------------+
| PoliceFormID |  ViolenceID |
+--------------+-------------+
|           10 |           1 |
|           10 |           2 |
|           11 |           1 |
|           11 |           2 |
|           11 |           4 |
|           11 |           5 |
+--------------+-------------+

Where do I store the other specific violence value?

  1. Store the words in a single column as space separated values:
+--------------+--------------------------------------+------------------------+
| PoliceFormID |            TypeOfViolence            |      OtherViolence     |
+--------------+--------------------------------------+------------------------+
|           10 |  PHYSICAL PSYCHOLOGICAL              |                        |
|           11 |  PHYSICAL PSYCHOLOGICAL FAMILY OTHER |  VIOLENCE IN WORKPLACE |
+--------------+--------------------------------------+------------------------+

These answers are not going to be used to sort anything and the options (e.g. types of violence) are not expected to get modified in the near future. The police forms would need to be updated constantly from the same app. Also, on this form there are at least 5 questions of this kind with multiple answers.

Currently I'm using the last option, but I was reading that this breaks the normal form so I was thinking about using the second one but it seems like too much work just for this because then the Android application and the web service behind it would need to send data for many tables and multiple tables would need to be updated just to update one form.

I was thinking about using the first one, it doesn't break any normalize norm and does not require to update multiple tables, just one.

Is the last option (currently implemented) really a bad idea?




Aucun commentaire:

Enregistrer un commentaire