- Feature Articles
- CodeSOD
- Error'd
-
Forums
-
Other Articles
- Random Article
- Alex's Soapbox
- Announcements
- Best of…
- Best of Email
- Best of the Sidebar
- Bring Your Own Code
- Coded Smorgasbord
- Mandatory Fun Day
- Off Topic
- Representative Line
- News Roundup
- Editor's Soapbox
- Software on the Rocks
- Souvenir Potpourri
- Sponsor Post
- Tales from the Interview
- The Daily WTF: Live
- Virtudyne
Admin
Wrong again. The two-letter code is what is known as a Natural Key. Adding a numerical value for each state is known as a Surrogate Key. You should never create a Surrogate Key when you have a perfectly good Natural Key. Either way, you still have to join to the State table (to get the full name of the State, and to verify that a given State exists). If you use the Natural Key, your code is much easier to maintain, and your application is easier to support.
Admin
Admin
That sounds great, but it doesn't apply in all cases. The system on which I presently work has alphanumeric keys formed by concatenating several unrelated fields from the record in order to generate a unique key. We have no control over the source data, so we're pretty much stuck with it. However, once it's in the DB, if we do lookups on this long (natural) string PK field, performance suffers. For a sufficiently large data set, the surrogate int key yields much better performance (in the downstream part of the system).
The nature of the data AND how it will be used should be considered together when making the choice of implementation.
Admin
Chareth obviously wasn't getting it. Yuriy tossed together a quick solution to try to help him understand what he was doing wrong. Chareth's response was a childish attempt to make himself look good and Yuriy look bad, AND involved the supervisor in a truly technical discussion.
The boss responded correctly. Discuss the technical inconsistencies all you want, but the point was that the "defects" Chareth pointed out were inconsequential at best. Chareth got told to STFU, politely even.
No matter how good you think you might be, there's a time and place where you should know your role. Yuriy is a senior developer for a reason. Chareth is a junior developer FOR A REASON. Even if Yuriy is wrong, Chareth should have built the code according to his recommendations and then deal with it later. Instead, he throws a virtual temper tantrum. Nice one, Chareth...good luck getting out of that junior role any time soon.
Admin
It sounds like they have expanded internationally, which is why ACT and QLD are there. But they can't tax internationally so the tax stored procedure doesn't need to care about anything except US states.
Admin
The sad thing is that there will never be a shortage of Chareths.
Admin
Mail for each of these "states" goes through a certain office in the US. If you ever need to enter an APO or FPO address in a form which forces you to choose the abbreviation for one of the 50 states, you should choose the state that that office is in. For APO AE, it's NY.
Admin
Ah! The old "don't understand joking"/"too pedantic" trick! Well played!
Admin
America's hat.
Admin
Actually, if all the data that the query needs is in the primary key, then you don't even need to go load the actual record out of the database. So, it's a trade-off between index size, and how that affects performance in the context of the queries you run.
Conversely, if you have a primary key already, and you want to write fast queries, try to not use fields that are not part of the primary key. And there are cases where adding a second field to a perfectly good primary key can actually improve performance (but reduce maintainability).
Admin
/facepalm
Besides, everyone knows the REAL best way to do this would be to partition the customer table into 50 state-specific customer tables: tCustomer_1 for Alabama tCustomer_49 for Wyoming
Just like an old employer used to.
Admin
Also, the name of the stored procedure could be 'spCalculateUSSalesTax', and whether or not the address is in a US state is handled before calling. From what I've seen even Canada's sales taxes differ in their handling. So once you are in and know it's a state you are dealing with using two characters shouldn't matter.
Admin
Even more wrong. The code is not easier to maintain, or support. You have required that the application does not allow states or provinces outside the USA. You assume that the states need no maintainance.
The unnatural integer key allows more states to be added. That's the issue. How can we most easily upgrade the application to use more states?
Granted, If the application specified only United States addresses, then I happily would use 'VA'. It's not wrong to follow specs.
Admin
Even if they ship to Australia, they probably don't collect sales tax.
Admin
Yeah. And I bet you never compared actual performance. You just assumed that the performance would be better with the surrogate key, Chareth. Now get back to fixing fonts.
Admin
No, as was ALREADY pointed out, FPO is the city; AP is the state. Jerk. I see someone else pointed out that it was already pointed out too.
Admin
The only rule #1 I care about is from my days playing X-Wing:
Rule #1: Don't get shot. Rule #2: Shoot them. Rule #3: Never forget that rule #1 comes before rule #2.
Admin
I don't normally say this -- in fact, I've never said it -- but this is a truly lame WTF. It has all the requirements for impedimentia: (1) We don't get to see Chareth's code, even in approximation; as pointed out by various enlightened commenters. (2) The original database looks, to my untutored eyes, to be a piece of indefensible DDL garbage. (3) Yuriy could do with a slap across the face. (4) Nobody sane says "we'll never export (or expand) the system," so the designer of the Sp database was clearly a bit-head. (5) "Please note that existing data has many states like ACT, NSW, QLD etc which are all 3 characters." (6) There is no point six. (7) Everything else.
As a result, all this one does is allow people to prattle on, more or less entertainingly, about SQL Server, money fields, two character state abbreviations (as a non-Republican in 2000, I truly believed that Florida's abbreviation should have been changed to "FU"), and managers. Plus make many, many absurd deductions about the details that were left out of the story.
Not so much anonymised as vaporised, this WTF, isn't it?
Admin
Agreeing that this is pretty lame. Some people can write efficient stored procedures (junior), others can recognise potential problems before they occur and present solutions in a concise way to management (senior). Perhaps Yuriy and Cherath should swap jobs!
Admin
I thought it was America's hat?
Admin
Admin
Oh, and the country that I live in doesn't even have states. Fwiw, a real address handling database will be more complicated than you most probably realise. We are talking about handling all sorts of abbreviations and localisation.
Admin
Admin
tCustomer_0 tCustomer_50 tCustomer_3.14159 tCustomer_no_sir_pie_are_round
And of course, we still have the District of Columbia, Samoa, the Marshalls... etc.
http://www.usps.com/ncsc/lookups/abbr_state.txt
Admin
Why, that's awfully normal of you.
Admin
not really; my solution is fairly simple: for the countries where I know the rules, I validate and store address info accordingly (still sort of complex unless you're okay with not being to query by subfield). For other countries, I don't validate and state as much - "you'd better make sure the address is valid"
Admin
They might have to rethink that currency field if they decide to do business in Zimbabwe.
Admin
Admin
Dang! That $100 could have bought me... one gallon of gas!
Admin
he might be right about one thing,though. Those 3 letter states are from Australia.
So am I, and I am sick of sites which insist on me entering a US state code without any other options, even n/a.
Admin
Of course not.
Admin
Admin
I agree with Chareth if this is an international application. The fact Chareth says "Please note that existing data has many states like ACT, NSW, QLD etc which are all 3 characters." suggests that it is used internationally.
I do get a little annoyed with the US-centric attitude (being in Australia). If this is an absolutely positively US-only application it is ok, otherwise it is in fact the boss who is the WTF.
Admin
Don't talk to bald-headed wrinkly things...
Admin
The Real WTF (TM) is why they are writing a stored procedure to calculate taxes when they are in fact using Microsoft Dynamics AX (referred in the mail just as AX) which, among other things, can do it just fine.
Coincidentally, Microsoft Dynamics AX uses a varchar(10) field to store state codes and numeric(28, 12) fields to store amounts.
Admin
Admin
To be fair, it said that existing_data had 3 letter codes in there for the "states" in Canada.
I'd say that's a WTF. And I'd think that any business would want to be able to handle octillion dollar orders, though it might be some time before they get any... :-)
Admin
What do you really gain from this? Yes, you'll be all set when we rename one of the states. Thank goodness you'll only have to update that data in one place when that happens. Following that logic, you should probably make another table that has all the ZIP codes in it too. Yeah, that would be good! Ooh, and area codes, that way you can split the phone numbers and not store that data redundantly. BTW, don't worry about the performance impacts of the multi-thousand row queries where the devs call dbo.StateLookup(sStateID int) on every row because they can't or won't write the query with multiple aliased left joins to bring in the states table each time. You can just get a faster server. Thank goodness you saved all that space in the database.
Grr.
End rant.
The practical way is to arbitrarily decide some size, like varchar(10), and use the States table to populate the select box for it. If you want to enforce data integrity then you can add a foreign key constraint back to the states table. Doing it this way takes 30 extra seconds of work to set up, and saves a minute or more each time someone writes a query to touch the data.
Normalization is like salt. Too little and too much are both bad.
-eg
Admin
That is the real WTF. There are only six Australian states (plus two territories plus a few external ones) yet we need three letter abbreviations!
I once ordered something from the US and they thought the QLD was part of the postal code.
Admin
One "new" ZWD is 10,000,000,000 "old" ZWD so it's not so much an issue now :) (1 USD is about 65 ZWD now)
Admin
huh? United STATES of America, ... state=state status=status. Maybe a US ting but first thought of state is always te regional type of state.
Admin
Then there is a "real real" WTF! You don't even need state codes. They can be implied from the ZIP code. You can make a table of ZIP code to States very easily. Use the first 3 digits of the ZIP code and index a table of states, and go from there. The problem is if you go "international" and it gums up the works. Countries (you can use 2 or 3 letter abbreviations), and each has wierd postal codes. Some are both letters AND numbers.
Admin
As someone in Australia (which is where those three character states codes are) I am frequently p*ssed at US based web services which don't cater for international addresses properly. Good on him for standing up for catering for existing data. I'd have to agree with his boss that the size of the fields for $ values was a bit too picky though. even if they were using Yen I don't think they'd run into that limit too often.
Admin
ACT, NSW and QLD are all Australian states.
Admin
"3. In some countries (such as the Phillipines) you need about $1,000,000 to buy a loaf of bread - in that case having a $1 trillion limit would make sense. Although why 12 decimal places??? " Yes, but the US company would not likely be making contracts or order in Philipino currency. Most international orders were made in USD but many now in EUR now too.
Admin
Re: Rule Number One 2008-09-10 11:26 • by Zap Brannigan Rincewind: Doesn't "State" refer to status? I.E. Act = Active vice one of the United States. And doesn't this indicate the bosses boss doesn't have a clue? I see the confusion, this like our ERP. We store state abbreviation and status in the same column. It keeps our database schema simple.
wtf?
TopCodr, is that you?
Admin
You guys have my sympathy in having to deal with such an overly-complex tax system. Catering for taxes at so many levels, i.e. federal/state/city etc, and also with the variations and exceptions at those levels, must be a real headache.
Just for comparison, here in Australia, add 10% GST and you're done.
We used to have state taxes, but they got abolished when the federal brought in the universal GST. We never had city taxes. Maybe they are the real WTF.
Admin
Why do you need keys? Is the database locked up? And why foreign keys? Can't a local locksmith help you? You sure try to complicate things.
Admin
Thanks to FDR being such a wuss at the end of WWII, we do not collect sales tax from foreign countries.
So even though Australia has three letter state abreviations, no need to process any of that data in any compute_US_sales_tax procedure.
Admin
Don't worry - the same ISO group that fast-tracked M$ OOXML is working on a schema for addresses (or is that a scheme to get your address? I forget.) that is guaranteed to work as well as OOXML...