Comment On tblState

damn.... that looks like alot like a table that i created almost 4 years ago. i feel stupid every time i look at it. 8P

... I don't see the prob. We all know politicians. What exactly do you do if your local GovernAtor :-) decides that some other abbreviation matches their state better? These things do happen and boy do they cause alot of greif in applications.

I do see some probs though. Like state abbrev is only allowed 2 chars and the names in the different tables should match. And what on earth do you do when there are more than 2147483647 states? :-)

I dont see the problem with this. It may have been more appropriate for the state table's pK to be the abbreviation instead of an int.

Otherwise, using a separate state table enforces a realationship between a row having a state and a member of the state table (which may hold the state's full name and some other attributes). I've had to hunt down a crazy production problem where state abbreviations (the foriegn key)in tables with a state id (the abbreviation) were getting truncated somehow. This would have raised a foriegn key constraint error if the relationship was enforced. Somehow, someone forgot to include a relationship between the tables when moving a release to production.

It's all about enforcing data integrity on the database end.

I don't see what the big deal is. First of all, it's more efficient to us numbers as keys than strings. Second, state abriviations *do* change. When I was growing up, I lived in ILL. Now, I live in IL.

@Jim: Not to mention that it is MUCH faster making that INNER JOIN and ordering by the int than just ordering by the string.

..I don't think so.

I LOVE this forum, it's just more evidence that "I" can eventually find a job with the lousy A.S. I paid 30K for.....

What is it with people putting ugly abbreviations on tables to represent their type?

tblState - "tbl" because it's a table.  Foly huck, ya think?

strStateWhateverthehell - "str" because it's a string.

Is this all in a database named "db_crapschema" on the "sqlLocal" SQL server instance running on "srvrDB1SERVER", part of the "lanOurNetwork" network?

captcha = batman B-)

Anonymous:

What is it with people putting ugly abbreviations on tables to represent their type?

tblState - "tbl" because it's a table.  Foly huck, ya think?

strStateWhateverthehell - "str" because it's a string.

Is this all in a database named "db_crapschema" on the "sqlLocal" SQL server instance running on "srvrDB1SERVER", part of the "lanOurNetwork" network?

captcha = batman B-)

 

having a bad day?

This could marginally make sense if the developer had intentions of adding more columns to the table in the future (full state name, population, red/blue, abortion allowed etc.)

Joins are much faster on integers than on varchars / strings / chars / any other datatype...

It also makes it consistant, your FK's and PK's are always numbers....

Maybe in DBase days you'd have joined on strings but hey

Captcha: Ideo - like idiot...

What if you need to migrate data from one system to another... and instead of using the 2 char abbr you used a surrogate key. IL is state 30 on system a, and 50 on system b and oh btw, it is a fk to 20 other tables (as lookup tables are prone to be). I need to move records from those 20 other tables to and fro... Can you see the problem here?
And the inner join comment..that made me laugh 2 bytes for an abbr, 4 bytes for the integer id (admit it, you made it an int).. The whole 50 record lookup table is going to be cached in memory anyway!
and as a DBA, I want to do a quick query that shows me how many records per state:

select count(stateabbr)
from table
group by stateabbr


or

select count(b.stateabbr)
from table a
inner join statelookup b
on a.stateid = b.stateid
group by b.stateabbr

Come on!!

The table is broken because it fails to qualify the State by Country. Using an integer foreign key makes an enhancement for international use reasonable, using StateAbbrev as the key would make an upgrade much more difficult.

I know I'm a little late in all this, but I agree with you completely, I live in Perth, WA ... now you won't find that in the US, because in my case WA stands for Western Australia, not Washington ...