Jim, in Australia, a locality can have more than one postcode and more 
than one name. In any case, it's not my database design, it's from the 
Australian Government Geocode team (GNAF). I have flattened some of it 
because some address lookup queries took minutes to run so I had to add 
things like street name into the address details table so I could have a 
secondary index on street name lookup. The address details table 
contains over 14 million records.

On 3/12/2016 12:57 AM, Jim Moseley wrote:
> Tom,
>
> My sql is rusty, so I'll let Liz & Larry talk about that.  My concern is
> with your design.
>
> It seems like your Alias table has columns other than just an alias key
> pointing back to the Locality table, like Alias Post Code.  This doesn't
> seem correct - Locality should be the only place for those fields.  If a
> user wants to specify their own Alias Locality, have them add 2 rows, 1
> to Locality and one to Alias.
>
> This would then solve your 'where can I get the Post Code from' issue.
>
>> If the user entered an alias locality and the locality match is on the
>> alias table (IN clause), how can I pick up other fields, such as postal
>> code which may be different to the main locality postal code, in the
>> alias table and put them in the query result? I.e. how do I know which
>> alias record was matched in the IN clause?
> ]
> HTH,
> Jim Moseley


-- 
To send me an email, remove the spaces.