11-16-15, 11:42 #1Registered User
- Join Date
- Nov 2015
Unanswered: Best way to track geographies that apply to a record
I should let you know up front that I'm fairly new to access and learning as I go. I've used some basic VBA with success, but I'm hoping to limit the coding if possible.
The project I'm working on is a database that tracks a wide variety of research sources. i.e. court records, business information sources, media archives, etc. My goal is to have each record contain the geography that the source covers (City, State/Province, Country), where the user selects State or Country from a predetermined list. The problem I'm running into is that a source may cover multiple states or countries. I have looked at using multi-value fields, but the consensus seems to be that this is poor database design. I'm wondering if you all have thoughts on what the best way to approach this would be.
Should I store all of the countries in one field or multiple? What is a user friendly way to input that data?
I realize this is a general question. Any ideas on how I should go about this are appreciated. I can post my database if it would help.
Also, I am using Access 2007.
11-16-15, 13:48 #2Moderator
Provided Answers: 14
- Join Date
- Mar 2009
Refrain from using multi-value fields. That's not only poor database design, that's no database design at all as that violates the normal forms which are the foundation on which the concept of relational database is built (see: http://www.bkent.net/Doc/simple5.htm, http://www.studytonight.com/dbms/dat...malization.php or https://en.wikipedia.org/wiki/Database_normalization). The "normal" way to associate several rows in one table to several rows in another table (many-to-many relationship) uses a junction table. See: http://www.tomjewett.com/dbdesign/db...e=manymany.php and http://en.tekstenuitleg.net/articles...ship-in-access.Have a nice day!
11-17-15, 11:55 #3Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
require users to specify countries
and states as separate entities
you can use the ISO coutnry codes (Either 2 or 3 digit versions)
beware of using country specific terminology in desioging the db and or the forms
the US has states as a major subdivision, Canada has provinces, the UK has counties
the US has ZIP Code which has little or no geographic meaning (especailly int he exceptions to the overall theory), the UK has a very specific geographical meaning. A UK postcode ties an address to a very limited area.
be carefull of meaning. a ZIP code is specific tot he states, the UK & Canada use Postcodes, France uses Cedex... essentailly ther are all the same thing, but it will pee people off referring to coutnry specific terminology and or styles. dont' even go anywhere near phone numbering stylesI'd rather be riding on the Tiger 800 or the Norton