[GLLUG] Storing other and none in a relational database

[Marshal Newrock]

Auto-increment doesn't mean you can't manually put a value in the id
column.  I would add "0 - None" to each table.  "Other" is more tricky
if you want to control the sort order.  If you're doing it strictly
alphabetically (other than "none"), you could put "other" in
lowercase.  Some locales will ignore case when sorting, though.
Alternatively, you could add a sort_order column.

As far as your concerns about invalid foreign keys, it would only be
invalid if you try to enter an id that doesn't exist in the referenced
table.

My suggestion would be to add "0 - None", which would make it easier to
check for None, and to have a sort_order column.

Marshal


On Tue, 31 Mar 2009 15:25:16 -0400
David Singer <david at ramaboo.com> 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.
> 
> David


-- 
Marshal Newrock
517-679-0699 x223
FreedomBI, LLC - http://www.freedombi.com
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 197 bytes
Desc: not available
Url : http://mailman.egr.msu.edu/mailman/public/linux-user/attachments/20090401/b36c1665/attachment.bin