My application will have to reference addresses. Street info is going to be saved with my primary objects however the relaxation must be saved seperately to lessen redundancy. How must i store/retrieve Zip fasteners, metropolitan areas and states? Here are a few of my ideas.

single table solution (cant do associations)

[locations] locationID locationParent (FK for locationID - for condition records) locationName (city, condition) locationZIP


two tables (with associations, FK constraints, ref integrity)

[condition] stateID stateName

[city] cityID stateID (FK for condition.stateID) cityName local zip code


three tables

[condition] stateID stateName

[city] cityID stateID (FK for condition.stateID) cityName

[zip] zipID cityID (FK for city.cityID) zipName


I Quickly read into ZIP codes amd the way they are designated. They are not particularly associated with metropolitan areas. Some metropolitan areas have several ZIP (ok will still work) however, many Zip fasteners have been in several city (oh snap) plus some other Zip fasteners (very couple of) have been in several condition! Several Zip fasteners aren't even just in exactly the same condition because the address they fit in with whatsoever. Appears Zip fasteners are created for company route identification plus some remote places would be best offered by publish offices in neighboring metropolitan areas or states.

Does anybody are conscious of a great (not perfect) solution that can take this into account to reduce discrepencies because the database develops?

There's really some database(having a single table) the USPS creates each year with ZIP codes and condition and areas and condition/county codes. I'd consider it. I've an (outdated) copy from it. The schema is fairly simple:



Local zip code nvarchar(5) not null

CITY nvarchar(50) null

Condition nvarchar(2) null

STATECODE nvarchar(50) null

COUNTY nvarchar(50) null

COUNTYCODE nvarchar(50) null

(see below)

edit: Also, I'd let your customers to include a brand new local zip code(with city and the like) because zipcodes are now being added constantly..

http://www.usps.com/ncsc/addressinfo/addressinfomenu.htm

edit: Really, I suppose I am wrong. I do not come with an official copy of the database.. I downloaded among their sample files as well as their schema appears quite complex.

I'm not sure if you are internationalizing your application, however the general construct is, having a one-to-many relationship using the following item:

Country
Region (condition/province)
City

That's usually sufficient to have the ability to filter your computer data inside a significant way. Believe me about this: you won't want to enter into the technical issues of geographic land division.

To have an address, keep data above plus home address, postal code (worldwide version of zipcode), etc. lower towards the resolution you'll need. I only say resolution since you could split the address area into such things as apartment number, street number, street title, street direction, etc. -- but that data might be determined by the place, and so i would avoid doing that if you are planning to internationalize your application. Only a home address area is enough 99.99% of times.

It is dependent on whether data integrity, normalization is much more important, or performance.

However, for many programs, what you want is really a household. So, these details ought to be saved individually out of your customer, so that you can represent multiple clients living in the same household.

Your family should have the home address, apt, city, condition/province, country, zip/postal code.

I wouldn't bother to normalize this by including merely a mention of city (which will be a key since there might be multiple metropolitan areas with similar title), however, you should store another table with metropolitan areas, nations, postal codes only for validation and integrity reasons.

I'd put these foreign secrets as fields inherited.

I have labored with marketing databases, and developed an AI system for creating customer secrets and household secrets for householding reasons for any bank, which was one of the greatest issues. For analysis reasons, we wanted to aggregate accounts towards the customer level, and clients towards the household level. So that your representation should support this for future analytical reasons.