[GLLUG] Fw: Storing other and none in a relational database

[Rocky Lichen]

--- On Tue, 3/31/09, David Singer <david at ramaboo.com> wrote:

> From: David Singer <david at ramaboo.com>
> Subject: [GLLUG] Storing other and none in a relational database
> To: "GLLUG" <linux-user at egr.msu.edu>
> Date: Tuesday, March 31, 2009, 12:25 PM
> 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.
> 
> David
> 
> 

If your database properly handles requests of the form "SELECT field1, field2 FROM mytable ORDER BY field3;" you could add a sort_order column to your brands table. Set it to default to some intermediate value, assign none a low value and other a high value.

Now add "SORT ON sort_order" to the end of your drop down select and change only the ordering of what is returned.