Suggestion: tblCountry - countryID
tblStateProvince - stateProvinceID - countryID tblCountyDistrict - countyDistrictID - stateprovinceID tblZip - zipID - stateProvinceID This is to maintain your lookup data. For the position: tblPosition - positionID tblPositionZip - zipID - positionID The tblPositionZip is your merge or bridge table to show what the coverage area is for a single position. The lookup information for the country to state/province to county/district to zip is to make it easier to search in your application for the right zip codes to add. You can add all the zip codes for a given country, state/province, state/district or just a single zip, but your queries will be easier to maintain. Teddy On 8/25/06, Ben Nadel <[EMAIL PROTECTED]> wrote: > > Maybe you could go with a join on persons to countries and a join on > persons > to zip: > > Contact_zip_jn > -contact_id, > -zip_id > > contact_country_jn > -contact_id > -country_id > > > That way, you can categorize them in both ways with out having them to > overlap. Depends on how complicated you queries need to be. Like, if a > person is joined to a country, do they ALSO have to be joined to all those > zip codes? Or is it ok for the application to store only explicitly set up > joins? > > ....................... > Ben Nadel > www.bennadel.com > > > -----Original Message----- > From: Richard Dillman [mailto:[EMAIL PROTECTED] > Sent: Friday, August 25, 2006 1:55 PM > To: CF-Talk > Subject: SOT: Cross Joins and lists and forms oh my! > > In my endevors to build this table structure for my Fostercare site we > have > a list of County Contacts spacific to each county. I have a cross join > table to match county ID's, Contacts, County Rolls and weather they > recieve > emails. Of course now there is a snag and a spacific county wants to > split > up their county by zipcode. > > I do have a zipcode table with Zip, county, city, state, area code, > daylight saveings time, and Time Offset. > > the long and short of it is i have > 1032 zipcodes > 93 Counties > 10 Positions > > If i had to store a person who is over say 3 counties (which is 200+ > zipcodes) how might i best go about inserting him and selecting the > appropriate zipcodes? Same for someone who is over the North East > Quardrant > of a spacific County (just 5 zipcodes) > > Im leaning twards a Stored prcedure that will include all zips in a county > and letting them select a whole county, and/or showing div to show > checkboxes for all the zips in that county should they want a partial. > > What would any of you do in this case? > > </rant> > > -- > -- > Richard Dillman > [EMAIL PROTECTED] > (317) 916-8341 > > "I think it's T double-E double-R double-R double-I double-F double-I > double-C, C, C." - the Goose in 'Charlotte's Web' > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251072 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
