States.



  • The recent WTF got me thinking about this problem I had awhile back (I
    can't remember if I posted about it here before or not).  At work
    we made this webform that asked for a person's name, address, and other
    standard information.  Well, come to find out, many people using
    this site were not from the US, so they were understandably a little
    peeved when all they could do was select a US state from a drop down
    list.



    In the process of internationalizing the website, I discovered that the
    State column in the table fed by this webform had a size of only two
    characters.  My idea was to add a Country column and to
    expand the State column to allow for non-abbreviated, non-US
    state/province/whathaveyou names.



    You wouldn't believe the unanimous opposition I recieved from everyone
    else at work about expanding the State column.  I tried and tried
    to get an explanantion as to why exactly it was such a bad idea, but I
    never got one that I was satisfied with.  And seeing as how I had
    only been at the company for a few months at that point, I decided not
    to press my luck and just agreed to do whatever they thought was best.



    What they thought was best was to add another column to the table, named
    "Intl_State."  Upon receiving data from the webform, a stored
    procedure examines the country parameter:  if it's the US, it
    inserts the State parameter into the State column and leaves the
    Intl_State column NULL.  Otherwise, it does the opposite.



    Now, I like my co-workers, so I'm not trying to make fun of them or
    anything like that.  But I still don't understand why my idea was such a horrible one.  What do you guys think?



  • I'd say your cow-orkers should go back to orking their cows, and let sane people like you look after things [;)]



  • @AJR said:

    I'd say your cow-orkers should go back to orking
    their cows, and let sane people like you look after things [;)]




    I wish this was possible!



    I've been put in a similar situation. I'm taking the stance of saying
    "we could do this", and maybe try to defend it (if it gets reasonably
    shot down, great, it wasn't a good idea to begin with), but if they
    oppose it for "just because" type reasons and the like, I just step
    back and let them fly the application straight into the ground. It's
    hard to get fired for doing what you're told.



    I'm not really sure if that's a good long term solution or not, but
    having to maintain and clean up someone else's mess does give us job
    security d:










  • How much code is floating around that assumes the state column is two
    characters long?  How many stored procedures reference the state
    column as varchar(2) or char(2)?  How many foreign keys point to
    the state table that would need to be altered?  How many stored
    procedures, code, etc,. reference the tables with the foreign keys?



    Changing a working system is usually bad.  By expanding the system
    and altering minor logical behavior you minimize the change to the
    system.  Eventually you get to a point with this method where
    things need to be regrouped and rewritten because the constant
    expansion has done serious damage to your application integrity and
    speed, but it's usually a ways off.




  • @bmschkerke said:

    How much code is floating around that assumes the state column is two
    characters long?  How many stored procedures reference the state
    column as varchar(2) or char(2)?  How many foreign keys point to
    the state table that would need to be altered?  How many stored
    procedures, code, etc,. reference the tables with the foreign keys?



    Changing a working system is usually bad.  By expanding the system
    and altering minor logical behavior you minimize the change to the
    system.  Eventually you get to a point with this method where
    things need to be regrouped and rewritten because the constant
    expansion has done serious damage to your application integrity and
    speed, but it's usually a ways off.






    The state field is only ever used for display type purposes.  So
    at the most I would have had to expand a few textboxes and labels here
    and there to accomodate state values larger than 2 characters. 
    But that had to be done anyway as the SPs were changed to return
    whichever state value, State or Intl_State, was not null.



    As far as SPs go, they were several that referenced the state column as
    varchar(2).  But my proposed change would have required changing
    that to varchar(whatever size we decided on), while their proposed
    change required us to add extra columns (we have Mailing_State and
    Legal_State) to a table as well as extra logic to figure out which state
    column to select/insert/update based on the country.



    Heh...state table.   That'd be nice. :)  We have no such
    table, just a state column in the tables that need one.  All
    validation to ensure only valid two-character state codes are entered
    is done in the application logic*.  But if you've read any of the
    "Payback Is Hell" posts on this site, you'd know that this is the least
    of my problems. :)



    *And it obviously isn't doing a good job of it.  I just did a
    "Select DISTINCT M_S from PeopleTable" where M_S is the Mailing_State (US
    states only) column, and I got back 87 rows.  A quick count of the
    state abbreviations listed on the
    USPS site
    yields 62 (didn't count that carefully though)
    distinct state abbreviations.


Log in to reply