At 07:34 PM 26/11/2003 -0500, you wrote:

>While this isn't exactly a Firebird question, it does pertain to a Firebird
>database -
>
>I need a way to process new sales orders, matching them with previous
>orders shipped to the same address. Doing a direct comparison of address
>does not work because of situations like this:
>
>123 S. Main St
>123 South Main St.
>
>That is just the tip of the iceberg on how addresses can mean the same
>thing but appear different.
>
>I'm looking at a couple different solutions:
>
>1. Use a soundex-type algorithm to generate an 'address code', and compare
>this new code.
>2. Implement a UDF that makes use of some commercial address verification
>products.
>
>I'm wondering whether anyone has tackled this particular problem with any
>success, and what that solution turned out to be?

I always break out customer and supplier addresses into a 1:many relation
from the customer table. It's an optional relation (won't exist until it is
actually entered) and order entry operators have to enter an address if
it's not already there or needs changing. Addresses each have an Address
ID and a FK to the Customer (or Supplier) table and they have an Address
Type (Billing, Delivery, etc.) and a status.

At order entry, the operator either picks an address, modifies an existing
one or adds a new one. If it's an override address, it still goes into the
address table with a status of "override".

You can extract the addresses you currently have. Any that are
near-duplications (like your example) will show up in use and can be
deleted the first time they are encountered. That will ensure that you
don't accidentally delete addresses you needed to keep.

You may have more extractions to do to link your history with Address
IDs. You may need to store an Address History intersection table if past
practice has been to hard-wire customer addresses in order headers and not
to store them in the customer structure.

heLen