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.