[GLLUG] Storing other and none in a relational database

[David Singer]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman.egr.msu.edu/mailman/public/linux-user/attachments/20090331/2396c868/attachment.html