[GLLUG] Storing other and none in a relational database

[charles at bityard.net]

On Tue, March 31, 2009 3:25 pm, David Singer wrote:
> I have a bit of a problem with an application I am designing. I was
> hopping
> someone on the list might have a brilliant idea for a work around.
>
> *Overview
> *My application lets users enter data in the form of drop down boxes (html
> select). These are generated from a database table where the value of each
> option is the primary key of the table and the value displayed to the user
> is the name column. For example this is the table of brands.
>
> brand_id           name
> 1                      Apple
> 2                      Dell
> 3                      Gateway
> 4                      IBM
> etc...
>
> This part is simple enough. The code reads the database and generates the
> html markup to make the drop down select. The problem is I want to add the
> option to select None or Other. I have tried a few solutions but none seem
> to work well.
>
> *Option #1
> *I could add none and other to my brands table like this:
> brand_id (pk)     name
> 1                      Apple
> 2                      Dell
> 3                      Gateway
> 4                      IBM
> 5                      Other
> 6                      None
>
> The problem with this approach is I always want None (if present)
> displayed
> first and Other (if present) displayed last. The only way I can figure to
> do
> this is with some overly complex SQL or some post database data
> reordering.
> More problematic though is with other tables I am going to have different
> values for None and Other (the primary key is an autonumber). These values
> need to be consistant so that client side javascript can promt the user
> for
> additional information if Other is selected.
>
> *Option #2
> *I could leave the table alone and add in None and Other before the data
> is
> presented to the user. I could then use constants like None =  2147483646
> and Other = 2147483647 (values that will never resonably be reached).This
> aproach makes client side scripting easier however it also means storing
> invalid data as foriegn keys in my table. It also seems unelagent somehow.
> For example my computers table would look like this.
>
> computer_id (pk)           brand_id (fk)              name
> 1                                  1                              Macbook
> 2                                  4                              Thinkpad
> 3                                  2147483647              Homemade
> Computer
>
> (assuming the user selected other as a brand for #3).
>
> *Option #3
> *Store None and Other as negative numbers. This would still mean invalid
> forigne keys but it has the added benifit that an additional table could
> be
> created for other values like this:
>
> other_id                 name
> 1                           None
> 2                           Other
> 3                           Davids Workshop
>
> On the client side when someone selected other from a drop down list
> javascript could be used to replace it with a text box and then whatever
> the
> user entered could be added to the others table and stored as a negative
> number in the computers table. That way any negitive forign key would be
> redirected to the others table. its a huge hack but i think ti would work
> though it would mean doing some things in PHP rather than SQL so
> preformance
> would probebly be limited (or very complex sql).
>
> Any other ideas? I don't really like any of these solutions they are just
> what iv come up with so far.

The most straightforward way to handle it in the code is to only present
the "more info" fields whenever the word 'Other' is returned from the
query (or selected in the drop-down box). That way you don't have to do
anything special in the database.

You can also use constants in your programming language to identify the
primary key value (PHP shown):

define('NONE', 1);
define('OTHER', 2);

Either way, I recommend inserting the 'None' and 'Other' records
immediately after the table is created so that you always know the primary
key value of those two records.

If you want to be overly pedantic, you can add a third column to the
table, a boolean called 'more_info'. When it's 1, the user needs to enter
more info. It's a little inefficient though, because only one record in
the whole table will ever be 0.

Using hacks in a field with AUTO_INCREMENT set will eventually cause
immense grief. For example, if you use mysqldump to back up the database,
it will preserve the sequence of the records but not the values in the
auto-increment field.

Charles
-- 
http://bityard.net