Dependent Foreign Keys

Post #100,722 by ChrisR 5/7/03 10:58:46 AM Reply	Dependent Foreign Keys If you're wondering why state_idnum is in coverage_county, I can't tell you. But whenever we add/delete/update coverage we have to keep everything in sync manually, because MySQL (as of the version this was written for) doesn't do triggers or foreign key constraints Well, the drill down would be you have 50 states and then within those 50 states you have n-counties. Not all county names are unique, so you may get a Smith county in both Texas and New Jersey. Anyhow, you'd segregate the two if you want to enforce a rule that says any selected county must be available for the selected state. Also helps when you want to restrict user input of counties based on the state input.
Post #100,724 by drewk 5/7/03 11:05:16 AM Reply	Can't happen, each county is unique We're not using county name, we're using countycode. eg: Rockingham County New Hapmshire is '01NH'. So countycode is a sufficient identifier even without the state field. The only reason to still have the state field is that the state is the last two charachters of the countycode instead of the first. So joins on that would not be able to use the index. We plan on changing to a new 5-digit countycode where the state will be the first two. Then we will be able to drop the state field entirely. === Implicitly condoning stupidity since 2001.
Post #100,726 by ChrisR 5/7/03 11:15:42 AM Reply	Still a function of integrity How do you ensure that the countycd is consistent with the statecd? Of course, if the statecd is truly embedded within the countycd, then the best course of action is to eliminate the statecd column, and just use a lookup table that maps county to state.
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!