CODEWITHSUNDEEP
database-designuser-experience
mpen
mpen

Reputation: 283093

Dropdowns with "other" option

Every time I get a spec that calls for a dropdown with an "Other (specify below)" option I groan because they're such a pain to implement.

  1. What's the best way to store this information in the database? Is it better to use two fields, one an integer foreign key to the value in a different table (including "Other"), and second string field to store the "Other" value if it's chosen? Or just store the chosen option as a string regardless (this also makes fetching the value back out of the database much easier).
  2. Interface-wise, do you use a regular old <select> and pop up a hidden <input type="text"/> if "Other" is chosen (maybe via some jQuery hackery), or use some kind of combox widget? The "filtering" version of this widget is the most user-friendly one I've found, but the implementation doesn't look very good (2000 lines, and it has bugs).

As an example, let's say I have a table called vehicles. It lists all the vehicles that are registered on my site. It contains stuff like, year, make, model, mileage.

Let's say I have another table that holds all the makes for users to pick from: Honda, Toyota, BMW, etc.

But my "makes" table might not be up to date, so I want to allow users to enter their own if it isn't listed. In this scenario they would choose "Other Make" from the drop-down, and then type in the make manually.

How would you represent this in the vehicles table?

a. As one field, make which might be a varchar(255). i.e., if they choose an existing make, it will simply be copied into this field
b. As two fields: make_id and make_other. The first holding a key to the makes table, and the 2nd is only filled in if they choose "other".

A jQuery solution to showing/hiding the "Other" textbox:

$('select>option').filter(function() {
    return !!$(this).data('other');
}).each(function() {
    $(this).closest('div.row').next().toggle($(this).is(':selected'));
    $(this).parent().on('change', function() {
        if($(':selected',this).data('other')) {
            $(this).closest('div.row').next().show().find('input').focus();
        } else {
            $(this).closest('div.row').next().hide().find('input').val('');
        }
    });
});

Just add data-other="1" to the "Other (please specify)" <option>. Assumes your inputs are laid out in <div class="row"> -- modify for other layouts.

Upvotes: 1

Views: 439

Answers (1)

fujy
fujy

Reputation: 5264

I prefer the first choice of storing the information in the database for the following reasons:

  • It's better for me to keep all data in the database
  • most of the time the customer will ask to make it changeable, actually they would ask for some screen to allow them to change ( add, remove, update ) these information
  • It's an easy mission to load most ( if not all ) this information in the cache on the system startup, and hence I don't need to make a call to the database to load all the data

And for how to store in the Database I would prefer the foreign column technique, and I won't use a second column to store the value for two reason

  • First, most of the time I will load the Other table in the cache, so I don't worry about database performance issue
  • For the scenario where the customer want to update the Other table values, I don't have to pass on all tables that stores the values and change it.

For some situation I won't mind using String primary key for the Other table, and hence I would store the value directly and keep the Other table for description and any related information. since for most cases the size of the table would be very small compared to core tables in the system (employee, users, carts, products, ... etc). However I should guarantee that the string values would be very rarely updated (countries table for example).

Upvotes: 1

Related Questions