Don't even need a lookup table

Post #100,730 by drewk 5/7/03 11:18:53 AM Reply	Don't even need a lookup table The 2-letter state abbreviation is always the last two characters of the countycode. Like I said, the only reason to maintain state as a seperate field is that a join on `RIGHT( countycode, 2 )` can't use the index. === Implicitly condoning stupidity since 2001.
Post #100,735 by tseliot 5/7/03 11:46:48 AM Reply	Maybe it's just me But I've always found JOINs (or any other structural operations) which are dependent upon string parsing to be rather inelegant (read: expensive, not portable, etc.). If you want to mash related values into a single value in order to perpetuate the user's perception that computers are cryptic, or to reduce typing, be my guest. But I would either place the state values in the county table (assuming there are no other referential constraints among state and county) and do a lookup, or create an intermediate view of county+state for what is rapidly turning out to be a "management-level" (read: data warehousing) query. Many fears are born of stupidity and ignorance - Which you should be feeding with rumour and generalisation. BOfH, 2002 "Episode" 10
Post #100,736 by admin 5/7/03 11:51:52 AM Reply	Agreed. A separate state field should be kept for the queries. Data encoded in a key is a really, really bad idea. Even if the state code is in the county code, it should be treated as a separate piece of data that just happens to be in the county code. Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #100,740 by drewk 5/7/03 12:35:35 PM Reply	That's how I've already set it up Dropping the state field entirely was just a possibility. I can't remember if it was MySQL or Postgres, but one of them allows you to define an index on a portion of a string. Then joins against that same portion -- ie: LEFT( countycode, 2 ) -- would be as fast as against a separate field. === Implicitly condoning stupidity since 2001.
Post #100,742 by admin 5/7/03 12:41:23 PM Reply	It's not a matter of speed. Embedded data is a bad idea. Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #100,744 by drewk 5/7/03 12:43:59 PM Reply	Generally I agree But in this case we're going to start using the oficial county codes from the USPS, and the data is already there. I wouldn't advocate creating it that way, but since we're going to be getting the data that way it seems reasonable. === Implicitly condoning stupidity since 2001.
Post #100,757 by admin 5/7/03 2:35:22 PM Reply	Even so. You put the official county code in one column, and extract the state for the other column. Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #103,049 by tablizer 5/22/03 2:00:35 AM Reply	I agree with Malraux on this one The county code could change in the future and no longer have state embedded in it. Best to keep an explicit State column around rather than parse the county ID to get State. "Smart ID's" are dangerous things. ________________ oop.ismad.com

Welcome to IWETHEY!