• Dave (unregistered)

    Yuriy is dead wrong...how are you supposed to store NaN in a char(2)?

  • (cs) in reply to snoofle
    snoofle:
    cpt:
    The three letter codes are Australian states (Australian Capital Territory, New South Wales, QueensLanD.
    A few years back, one of the projects I worked on had a "state" column in the db, and I noticed that it had way more than 50 distinct values in it. It was explained to me that since all US states were 2 letters, and that the business folks were now doing business internationally, that rather than change the db and everything that depended upon it, we'd just be assigning arbitrary 2 character combinations for each new region.

    What they didn't tell me about was the range of allowable characters that the auto-state-name-generation function was designed to use... all printable ASCII characters!

    It sounded reasonable until the auto-state-name-generation function eventually created new state codes like backslash-dot and dot-star, and then the value from the state column got parsed with a regex - it made for some interesting debugging sessions.

    That reminds me of a system on which I worked that had a field full of four-digit numbers. All values (0-9999) had been used, and there was so much brittle code build around the exact schema that there was supposedly no easy way to expand the field. However, the field just happened to be of type VARCHAR2(4). So, one of my colleagues implemented a behind-the-scenes Base 36 storage scheme using characters 0-9 and A-Z in the field.

    I guess it wasn't a complete WTF, except that I always thought the complexity of the "solution" was out-of-proportion with the need. I mean, I can see how such an approach might be a clever way to escape some really bad situation involving a mission-critical system. But I was skeptical of its applicability to this particular "accounting emergency."

    Of course, I could have (and did) said the same thing about a great many things we did for those folks.

  • Jim R (unregistered)

    NSW and QLD are Australian states, which DO have three-letter acronyms. Therefore, a state field of length 2 for software which might be used outside the United States, or -- more likely -- will be used to store addresses of customers/users/etc. who live outside the United States is just plain wrong! Perhaps the state field should be named StateOrProvince, and in any case, the manager in this case should get a clue that there's more to the world than the U.S. That same manager should become familiar with http://www.columbia.edu/kermit/postal.html. The takeaway from that web page is not to memorize the address formats for 178 different countries, it's simply to realize that designing data entry and data structures for addresses is not as simple as you'd think.

    By the way, I was born and bred in the U.S. and have always lived here, so this isn't the rant of a "foreigner."

  • dxk3355 (unregistered)

    What about ADO?

  • Capt Obvious (unregistered) in reply to parris
    parris:
    If Chareth wants to stop getting stabbed in the heart with an exploding salty lemon dagger, he's going to have to quickly learn to stop "getting people in trouble" via CC, especially his boss.

    Spot on! As soon as Yuriy is done getting reamed by his boss, he's going to go after Chareth like a heat-seeking missle. Calling out your superiors over nonsense like this isn't going to make you any friends.

  • dv (unregistered) in reply to snoofle
    snoofle:
    cpt:
    The three letter codes are Australian states (Australian Capital Territory, New South Wales, QueensLanD.
    A few years back, one of the projects I worked on had a "state" column in the db, and I noticed that it had way more than 50 distinct values in it. It was explained to me that since all US states were 2 letters, and that the business folks were now doing business internationally, that rather than change the db and everything that depended upon it, we'd just be assigning arbitrary 2 character combinations for each new region.

    What they didn't tell me about was the range of allowable characters that the auto-state-name-generation function was designed to use... all printable ASCII characters!

    It sounded reasonable until the auto-state-name-generation function eventually created new state codes like backslash-dot and dot-star, and then the value from the state column got parsed with a regex - it made for some interesting debugging sessions.

    TRWTF is they let you debug apps with regexes, when you obvy don't know nothing about it.

  • JamesQMurphy (unregistered) in reply to Capt Obvious
    Capt Obvious:
    parris:
    If Chareth wants to stop getting stabbed in the heart with an exploding salty lemon dagger, he's going to have to quickly learn to stop "getting people in trouble" via CC, especially his boss.

    Spot on! As soon as Yuriy is done getting reamed by his boss, he's going to go after Chareth like a heat-seeking missle. Calling out your superiors over nonsense like this isn't going to make you any friends.

    Then Chareth should find another job. He was correctly pointing out inconsistencies, he had examples to back himself up, and he did it politely. If Yuriy really is a good developer, he would not seek revenge, he would simply state why the decisions were made. Period.

    You know that if Yuriy's original schema was implemented as-is, it probably would have resulted in production problems, which would have promptly been dumped onto Chareth.

    Copying the boss? Well, it depends. Sometimes you have a boss who has technical knowledge, and might offer helpful insight (obviously not the case here).

  • (cs)

    Bitching about the data types isn't supposed to be TRWTF.

    It sounds like Chareth wrote the stored procedure and it was bad code. But it was NOT bad code due to the data types. He just wrote a bad SP with extraneous, unoptimized code.

    Then Yuriy re-wrote the stored procedure with good code (maybe not paying the most attention to proper data-types, but using valid data types that have been proven to work for his scenario).

    So Chareth's ego is beaten up and his panties are clearly in a bunch so deep it's triggering his gag reflex. He tries to find something wrong with Yuriy's code so he can point it out to the boss. The boss either sees it for a pointless narc attempt or being pointlessly nitpicky and shoots him down again.

  • Otto (unregistered)

    The boss is wrong on point 1. For one thing, the US does have three letter place codes. APO/FPO, for example, to send to the armed forces. Although the post office will accept AE/AP as the place codes in those cases as well.

    But if you ever decide to sell products to, say, Mexico, then you'd be well advised to have 4 letter place codes, since they do.

    The standard for state codes is 4 letters, since there's no place that exceeds that as far as I know. However, a smart programmer makes it 6. Just in case. What's 2 more chars?

  • (cs) in reply to Capt Obvious
    Capt Obvious:
    parris:
    If Chareth wants to stop getting stabbed in the heart with an exploding salty lemon dagger, he's going to have to quickly learn to stop "getting people in trouble" via CC, especially his boss.

    Spot on! As soon as Yuriy is done getting reamed by his boss, he's going to go after Chareth like a heat-seeking missle. Calling out your superiors over nonsense like this isn't going to make you any friends.

    Especially with a name like Yuriy. He's probably in the Russian mafia and won't take kindly to Chareth's taddle-tail attitude.

    Chareth: "This code sucks Yuriy, I don't know how you can program." Yuriy: "I don't know how you can walk." Chareth: "Wha?" crack crack

  • sadwings (unregistered)

    If the boss responded so fast, my guess is that Chareth (probably Sharath, but I'm only guessing) is a pain in the ass with a reputation.

    The real WTF is that this doesn't actually tell you the code differences that spawned the brew-ha-ha.

  • (cs)

    Cardinal Rule III: Bosses-bosses have been ratted on to their bosses before, so if you rate to your bosses-boss, chances are you're only going to get back a response that shows you how little you know, along with the pent up anger from someone who it's already happened to a million times!

  • (cs) in reply to Sakamura
    Sakamura:
    *HONK* Wrong!

    US does have 3-letter places. The different armies.

    The address for one of my friend is (replacing identifying info with clearly invalid values)

    ABC (DE) Homer J Simpson ABC-123 Unit 45678 FPO AP 96601-1234

    In our case, the code is FPO instead of CA for California...

    So I guess that boss doesn't want to handle marines. Such a jerk.

    Sorry, you're wrong. FPO, APO, etc. are part of the city field.

    Military "state" codes are AA (Armed Forces America), AE (Armed Forces Europe), AP (Armed Forces Pacific).

  • A Gould (unregistered) in reply to TallGuy
    TallGuy:
    Wait...what's Canada, then? A foreign country?!

    <ob.CanadianPatriotism> Look up - we're larger than you, and on top. </ob>

  • Capt Obvious (unregistered) in reply to JamesQMurphy

    [quote user="JamesQMurphy"][quote user="Capt Obvious] Spot on! As soon as Yuriy is done getting reamed by his boss, he's going to go after Chareth like a heat-seeking missle. Calling out your superiors over nonsense like this isn't going to make you any friends. [/quote]

    Then Chareth should find another job. He was correctly pointing out inconsistencies, he had examples to back himself up, and he did it politely. If Yuriy really is a good developer, he would not seek revenge, he would simply state why the decisions were made. Period.

    You know that if Yuriy's original schema was implemented as-is, it probably would have resulted in production problems, which would have promptly been dumped onto Chareth.

    Copying the boss? Well, it depends. Sometimes you have a boss who has technical knowledge, and might offer helpful insight (obviously not the case here). [/quote]

    You're right about the inconsistencies being an issue. Datatype consistency is important. But so is knowing how (and more importantly, when) to move an issue up the chain of command. Copying your boss' boss on something minor like this is like pointing your finger and yelling "See, see? I told you he's an idiot!"

    All it really tells Yuriy's boss is that Yuriy can't manage his team. Maybe that's true...but pointing it out in this fashion isn't going to help Chareth's cause any. My read on the situation is that Chareth is being juvenile and immature. This is the sort of thing that turns managers into overbearing, micro-managing asshats.

  • hymie! (unregistered) in reply to Sakamura
    Sakamura:
    *HONK* Wrong!

    US does have 3-letter places. The different armies.

    The address for one of my friend is (replacing identifying info with clearly invalid values)

    ABC (DE) Homer J Simpson ABC-123 Unit 45678 FPO AP 96601-1234

    In our case, the code is FPO instead of CA for California...

    HONK Wrong.

    FPO is the "city" AP is the "state"

  • Foobar (unregistered) in reply to hymie!
    hymie!:
    Sakamura:
    *HONK* Wrong!

    US does have 3-letter places. The different armies.

    The address for one of my friend is (replacing identifying info with clearly invalid values)

    ABC (DE) Homer J Simpson ABC-123 Unit 45678 FPO AP 96601-1234

    In our case, the code is FPO instead of CA for California...

    HONK Wrong.

    FPO is the "city" AP is the "state"

    Beat me to it

    http://www.usps.com/ncsc/lookups/abbreviations.html#states

  • Michael (unregistered) in reply to Rachael
    Rachael:
    I'm with Chareth on point (1). He says there are 3-letter states in the *existing data*, i.e. there are customers *already in the database* who live in the 3-letter Australian states he gives as examples. Yuriy's char[2] solution won't work for them.
    Yuriy's solution doesn't have to work for them, because the solution is only for US orders, which will only have US state codes, which will only be 2 letters
  • Andrew (unregistered) in reply to Former Junior Programmer
    Former Junior Programmer:
    TallGuy:
    Wait...what's Canada, then? A foreign country?!

    The 51st state.

    Almost, Lower Canada, now Ontario, was asked to join the revolution. It would have been the 14th state, not the 51st.

  • (cs)

    I thought companies are only required to charge sales tax on states where they have a physical presence. This would easily explain why they would have a table with worldwide entities so they could ship to them, but a stored procedure to calculate tax would not need to calculate tax for QLD. And someone already point out that military addresses have a two letter abbreviation ("AP" for example).

  • I walked the dinosaur (unregistered)

    The WTF is on Yuriry and his boss, not Chareth. I agree with what the other person typed about how he was "with Chareth". Also, someone else pointed out that there are ALREADY ZIPCODES with more than 2 characters in the database. The real WTF is the people who think Chareth is the WTF.

  • Andrew (unregistered) in reply to Michael
    Michael:
    Rachael:
    I'm with Chareth on point (1). He says there are 3-letter states in the *existing data*, i.e. there are customers *already in the database* who live in the 3-letter Australian states he gives as examples. Yuriy's char[2] solution won't work for them.
    Yuriy's solution doesn't have to work for them, because the solution is only for US orders, which will only have US state codes, which will only be 2 letters

    Everyone is missing the main SQL-design flaw here. The governments' state abbreviations should not be the primary keys.

    The application should use INTEGER primary keys to the STATES table. The table stores the states' names and abbreviations for human readers only.

    Then, only the STATES table has to change when new countries are added. The integer foreign keys referencing existing STATES will continue to be valid.

  • Marc B (unregistered) in reply to occasional reader
    occasional reader:
    I've seen stuff like this before - people realising that the world doesn't rotate around the USA - where the 'state' column in addresses are relaxed to include provinces of Canada and abbreviated regions and counties of other countries.

    Maybe the junior developer might have been correct in this - the tax calculation might have been flexible to be used for countries other than the US?

    I'm sure the senior developer is correct in that the US isn't planning to annex another state quite this minute :-)

    If they do, they'd probably assign a two letter abbreviation anyway, as in IQ for Iraq and CA for Canada (aka 'North Maine').

  • rumpelstiltskin (unregistered) in reply to Michael
    Michael:
    Yuriy's solution doesn't have to work for them, because the solution is only for US orders, which will only have US state codes, which will only be 2 letters

    Which goes to the root of the matter- Chareth is trying to solve a business problem. He is confused by technical details which do not matter in the context of the problem. That is why he is a junior developer. (Of course, I believe the application has some data validation somewhere, which will, for example, prevent using the SP to calculate a US sales tax on something sold in Australia, or prevent an order for a grazillion dollars, or whatever. Because all applications do. OK, maybe not. But Chareth's piece should not make it possible to do such things without even blinking, which is what it seems he was going for.)

  • JP (unregistered) in reply to ObiWayneKenobi

    Yeah, I'm seeing it right now. Our company promoted one of our customer support managers to be a Dev manager. Until then the only code she'd ever seen was on a kid's placemat at Denny's.

  • blah (unregistered) in reply to TallGuy
    TallGuy:
    Wait...what's Canada, then? A foreign country?!
    America's hat.
  • Bush (unregistered) in reply to Maurits
    Maurits:
    Bush:
    What about IQ and IN?

    IN is INdiana... IA is IowA. That leaves IR. (AF and NK are also available.)

    I believe I said IQ not IA. So, we'll have IQ and IR for Iran. I'm the decider, hehehe!

  • (cs) in reply to A Gould
    A Gould:
    TallGuy:
    Wait...what's Canada, then? A foreign country?!
    <ob.CanadianPatriotism> Look up - we're larger than you *snip* </ob>
    Ah, but for how long?
  • (cs)

    Were I interviewing both of them, based on this WTF, I'd hire Chareth and pass on Yuri.

  • (cs) in reply to Former Junior Programmer
    Former Junior Programmer:
    TallGuy:
    Wait...what's Canada, then? A foreign country?!

    The 51st state.

    [image]
  • Eric L (unregistered) in reply to parris

    No, if Chareth wants to stop getting stabbed in the heart, he just needs to get a grip on reality (which sadly is often missing in recent college grads).

  • (cs)

    TRWTF here is an able manager.

    Wait... there's something wrong with the universe IT'S THE HADRON COLLIDER! RUN!

    An actual WTF on a capable manager? Scary.

  • Foobar (unregistered) in reply to Paul
    Paul:
    Hang on everyone

    I think I'm pretty much with Chareth here

    The database already has data with the 'big' fields. The database stores states in a 10 character field (and already has data > 2 characters in it), and it stores 'subtotal' as NUMERIC (28,12)

    So, since the database ALREADY stores the data in the 'big' form, the complaint that it would use too much space for the stored procedure to use that form is pretty specious.

    Paul

    I just want to add that even if the article talked about the data in the DB it would still be specious for two reasons. 1) I’m pretty confident that most DBMS don’t allocate the maximum size of a field when the value is smaller than the Maximum size. 2) Even if the did it’s a pretty small amount, 4 bytes per row in MS SQL and 6 bytes in Oracle. This translates into 4MB to 6MB per million rows. This amount of disk space shouldn’t be a consideration in most applications.

    For thoes who care about how I came up with the numbers:

    SQL PRINT datalength(9999999999999999.999999999999 ) - datalength(99999999999.9999)

    Oracle SELECT VSIZE(9999999999999999.999999999999 ) - VSIZE(99999999999.9999) FROM dual

  • Les (unregistered) in reply to Andrew
    Andrew:

    Everyone is missing the main SQL-design flaw here. The governments' state abbreviations should not be the primary keys.

    The application should use INTEGER primary keys to the STATES table. The table stores the states' names and abbreviations for human readers only.

    The application calculates US taxes, so why TF should it care if some other country somewhere uses a different set of abbreviations for States? This code will never get called for Queensland, or for some former Soviet republic that uses three umlauts and a picture of a tractor as a state abbreviation.

    The idea of having an integer key for each state is just too much. The US Postal Office already gives us unique keys for each US state, so why complicate things?

    Almost anybody in the US knows that VA is Virginia. How many know that you've picked the number 12 for Virginia instead? How many know this at 3:00 AM on a Sunday when they are trying to debug your overcomplicated application?

  • Magus (unregistered) in reply to Michael

    Were Rachael's asterisks insufficient? That data is in the database, and we have no reason other than the boss's snarky note to assume that this method will somehow magically never hit them.

  • (cs)

    I think everyone is missing TRWTF here. The width of the datatype thing is just a red herring; it doesn't really matter one way or the other. TRWTF is that Chareth's code is so bad that it failed a code review three times. When another developer tries to set him straight, he can only focus on these insignificant details in an attempt to justify that his version is better.

    I'm sure if we had access to the stored procedures we'd see that Chareth's is a total mess and that this column width thing is a total non-issue.

  • (cs) in reply to A Gould
    A Gould:
    TallGuy:
    Wait...what's Canada, then? A foreign country?!

    <ob.CanadianPatriotism> Look up - we're larger than you, and on top. </ob>

    If God had wanted us to think Canada was bigger, he wouldn't have given us the Mercator projection, eh?

    • Rick
  • (cs) in reply to frustrati
    frustrati:
    Exception is Quebec, from within which Canada is an oppressive federation that has wrongfully annexed the proud and sovereign state of Quebec.
    Canada is my country. Quebec is where I live.
  • Calli Arcale (unregistered) in reply to Sakamura
    Sakamura:
    *HONK* Wrong!

    US does have 3-letter places. The different armies.

    The address for one of my friend is (replacing identifying info with clearly invalid values)

    ABC (DE) Homer J Simpson ABC-123 Unit 45678 FPO AP 96601-1234

    In our case, the code is FPO instead of CA for California...

    So I guess that boss doesn't want to handle marines. Such a jerk.

    FPO is analogous to a city, not a state. The state code is still the two-letter AP, as the previous poster indicated. There are actually three "state" codes for military addresses: AE (Europe/Canada/Mideast/Africa), AP (Pacific), and AA (Atlantic).

    There are two "city" codes: APO and FPO. APO is Army and Air Force, while FPO is Navy, Marines, and Coast Guard.

  • AreYouSerious (unregistered)

    There are obviously some people reading this that have never had to maintain code that was written for "U.S. only" software that eventually expanded into international markets. It's people like Yuriy and the boss that don't have the foresight to look at the 2 character state abbreviation and say "I know we are U.S. only right now, but what if we expand into international markets in the future?" Just do it right the first time. I would much rather be maintaining Chareth's code in the future.

  • Calli Arcale (unregistered)

    Additionally, the USPS has a complete list of US address abbreviations. It does not include Canadian or Mexican state codes, but it does include other non-state codes, such as DC (District of Columbia), American Samoa (AS), and so on. All are two-character.

  • (cs) in reply to AMerrickanGirl
    AMerrickanGirl:
    Sakamura:
    *HONK* Wrong!

    US does have 3-letter places. The different armies.

    The address for one of my friend is (replacing identifying info with clearly invalid values)

    ABC (DE) Homer J Simpson ABC-123 Unit 45678 FPO AP 96601-1234

    In our case, the code is FPO instead of CA for California...

    So I guess that boss doesn't want to handle marines. Such a jerk.

    Sorry, you're wrong. FPO, APO, etc. are part of the city field.

    Military "state" codes are AA (Armed Forces America), AE (Armed Forces Europe), AP (Armed Forces Pacific).

    From USPS ("Military" States): Armed Forces Africa AE

    Armed Forces Americas AA (except Canada)

    Armed Forces Canada AE

    Armed Forces Europe AE

    Armed Forces Middle East AE

    Armed Forces Pacific AP

    With the way things are going, maybe the Middle East needs it's own code... How about SH(it)

  • Zap Brannigan (unregistered) in reply to Les

    Can someone point me to an international schema for storing addresses? Most of the systems I've worked on were designed with the US in mind. Canadian address sort of work (not really). Mexico and the rest of the world, not supported at all. Is there an ISO or other standards committee address format that is international?

  • Scott Marlowe (unregistered)

    Intersting that at no point does it mention whether or not the money type is lossy or not. I'd think that would be more important than just its range.

  • Andrew (unregistered) in reply to Les
    Les:
    The application calculates US taxes, so why TF should it care if some other country somewhere uses a different set of abbreviations for States? This code will never get called for Queensland, or for some former Soviet republic that uses three umlauts and a picture of a tractor as a state abbreviation.

    The idea of having an integer key for each state is just too much. The US Postal Office already gives us unique keys for each US state, so why complicate things?

    Almost anybody in the US knows that VA is Virginia. How many know that you've picked the number 12 for Virginia instead? How many know this at 3:00 AM on a Sunday when they are trying to debug your overcomplicated application?

    The USPS state code is in the STATES table. A database sequence generated the number 12 for the row ('VA', 'Virginia'). The relation between state codes and keys is documented in the DB, and is not arbitrary!

    In fact, the STATES table, presented as a menu, prevents users from entering invalid state codes in the first place. The database FOREIGN KEY for ORDERS.STATE_ID validates the ORDERS record for you.

    All you have to do is join STATES to the ORDERS table to see the state code in the address. If you can't write such JOIN clauses, then you are not qualified to use SQL at all.

  • eric76 (unregistered)

    Calculating a sales tax merely by the state and/or zip code is broken.

    For example, in my zip code, there are two different sales tax rates depending on your location.

    In addition, depending on what you are selling, there may be sales taxes in some states but not others. For example, in some states, no sales taxes are applied to food items while in other states they are.

  • (cs)

    Wait, I thought Rule One was to be very careful when speaking to a small bald wrinkly old man....

  • JoeBillingsWorth (unregistered) in reply to Pecos Bill

    You couldn't be more wrong.

    http://www.postgresql.org/docs/8.3/interactive/datatype-money.html

  • JoeBillingsWorth (unregistered) in reply to Pecos Bill
    Pecos Bill:
    Bryan Kowalchuk:
    They are both wrong. All money calculations in SQL Server should be done using the money datatype...
    There are more software houses than just Microsoft. Most DBMS have a money data-type. Surprisingly , MySQL & PostgreSQL don't. I guess they feel the numeric type is sufficient.

    You couldn't be more wrong

    http://www.postgresql.org/docs/8.3/interactive/datatype-money.html

    (meant to quote the one I was referring to)

  • 3333 (unregistered)

    To avoid conflicting with existing states we need 3 alpha

    IRAQ = IRQ IRAN = IRN

Leave a comment on “Rule Number One”

Log In or post as a guest

Replying to comment #216754:

« Return to Article