[thelist] DB design question

[Mike H]

I don't pretend to know enough about database design to make a comment on fields, joins, etc., but:

Each property in the US which has mail delivery -- or which might have mail delivery -- has an unique 11-digit ZIP code in the form
of XXXXX-XXXX-XX. This code identifies the delivering PO ("Town" or station), the route (area), the route sector(specific
street/block), the side of the street and the sequence of the building (lot, etc). If this number is furnished (It should be printed
on all automation-sorted mail pieces) it shouldn't matter what is entered in the string values of an address field, the mail should
be directed to the correct property.

So...the  complete ZIP code is unique down to the apartment number, which should also be part of the address when needed.

In large buildings the sector numbers may be as fine as single floors or specific mail rooms, box clusters, etc. In VERY large
buildings, more than one primary ZIP may be assigned.

Eleven digits yeilds 99,999,999,999 unique addresses.

 It is this information, shared by the Postal Service, phone companies and E911 managers, which drives accurate emergency response
programs all over the country. They map phone numbers against ZIPs against string addresses, and display it in real time.

There is a lot of software available to provide complete ZIP codes from databases of addresses. The Postal Service even has
commercial versions of their master lists, complete with regular updates, available in various subscription packages.

Please...I'm not defending the USPS on postage rates or their delivery record, but the system they use to locate places is great.



Mike H
ironmike at inav.net
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

>
>... but how is the database supposed to know that the address is the "real"
> primary key, if you use an autonumber as the primary key?
>
> ...and so the question is, what's to stop the table from having two rows with
> the exact same address?
>