0

For a form such as

Is the time known? Yes or No

If Yes then asks time input.

Is the time flexible? Yes or No

If yes then asks num of hours input

How should the form submission be recorded in a database?

Should one record all 4 fields (2 booleans and 2 non-booleans in this case) in the database? Or just store 2 non-booleans and store the field as null if that field has a No value from the form?

Is it worth having a field in the database for the yes/no submission from a form? If so what would the value of the nonboolean field be if the existence of the field is No?

The said duplicate question doesn't address the question of storing booleans for a fields existence. It only discusses the representation of no data in a MySQL DB table field.

Qwerty
  • 323
  • 1
  • 6
  • 33
  • It's a lot more common to store it as nulls if it's optional rather than storing extra booleans since it takes less bookkeeping. – Benjamin Gruenbaum Apr 26 '15 at 00:25
  • 1
    Is there any value in having the boolean field? `null` usually means "no value here", so if all you need to store is the fact that there is no value then a `null` entry in the column seems to capture that. If the boolean field is simply repeating that fact, then you're essentially storing the same data twice. Keeping copies of the same data synchronized is historically difficult. – David Apr 26 '15 at 00:26
  • "is it worth having a field in the database for the yes/no submission from a form? " __no__ – Pedro Lobito Apr 26 '15 at 00:27
  • Right while the form can ask the question Yes or No question for user friendliness sake. Storing the same in DB is pointless when it can be stored as a value to mean YES or NULL to mean NO. – Qwerty Apr 26 '15 at 00:30
  • possible duplicate of [Should I use NULL or an empty string to represent no data in table column?](http://stackoverflow.com/questions/167952/should-i-use-null-or-an-empty-string-to-represent-no-data-in-table-column) – inquiryqueue Apr 26 '15 at 00:31
  • Actually that doesn't address the question of storing booleans for a fields existence. But what if the underlying field is hidden while the field's existence is set to No in the form and a user submits value for the hidden field? Obviously the client & server side validation should check for No and accordingly store in DB? – Qwerty Apr 26 '15 at 00:35
  • @Qwerty: Server-side validation can simply check for the existence of the nullable values and if the user decides to fiddle around with hidden fields, well, where's the problem? (The booleans map strictly, in both directions, to whether the value is null. They're just a UI convenience, nothing more.) – Nathan Tuggy Apr 26 '15 at 00:56

2 Answers2

1

In your case I would probably use a single coded column rather than multiple booleans. I'd define some codes (1=Time Not Known, 2=Time Known - Flexible, 3=Time Known - Not Flexible, etc.) and store their short (1,2,3,..) values in the database. This will reduce the number of fields and simplify lookup in the future. You can use a codes table to store these codes for reference purposes.

JP.
  • 5,536
  • 7
  • 58
  • 100
0

Only use the two values with the requirement that if the answer is yes to either question that the non-boolean response is mandatory.

kojow7
  • 10,308
  • 17
  • 80
  • 135