I am having problems defining an address model to include one or more defined geographic areas. I want to be able to have an address linked to an alias or aliases of a geographical area or areas, perhaps for selling, analysis or work scheduling.
An example might help (I'm in the UK so the 'bits' of an address are different from those in the US) but my address is:
Line 1: Bagbury
Line 2: St. Anne's Hill
Country: United Kingdom
Postcode: EX23 0LT
I might want to group this address with others in Cornwall, or in Bude, or in the UK, or in EX23. So far, so trivial.
But some postal addresses don't have a town, others don't have a county, some have a district within a town, and some are in France and Germany and the US and Japan etc. where the address parameters are completely different. Did anybody say subtypes?
We might define the address to be in the South West Area, or in Joe's Service Area, or in the Sell More Widgets Now Campaign area, or in any permutation of them over time i.e. some may be removed, say, from Joe's Service Area and others added.
My initial idea was to give a name or names to the grouping Town, County, Country in a t_geographic_area with a many-to-many relationship to t_address. However, t_address should also be able to know its full postal address and we have a zero, one or many on each side of the intersection table.
I have found a model in Len Silverston's "The Data Model Resource Book" that seems to have some possibilities if I could determine how I can get it to work for multiples :-). It's as follows:
Address(Address ID (PK), Postal Code, Address 1, Address 2, Directions)
Geographic Boundary(Geo Code (PK), Name, Abbreviation)
County(County ID(PK),...) and so on for State and Country.
There are non-identifying optional 1..n relationships as follows:
Country to State, State to City, State to County, County to City.
County etc. are stated to be subtypes of Geographic Boundary (although they have PKs?).
And City (but not Geographic Boundary) has a non-identifying optional relationship with Address.
I'm sure the answer is simple, but at the moment I'm like a dog chasing its tail. Can anybody help me think this through?
Addresses are quite messy to deal with, so don't be put off by complex solutions. The addressing schemes I have seen are all country specific and few countries share the same address format.
To be "universal", you'll have to come up with a way to parse the address and separate it into component parts that can be stored from largest geographic area to smallest(Country, District/Province, State/County, City, Street, Apartment, etc.).
Once you can split the address for storage, associating similar addresses will be much easier.