Wide Primary Keys

[William D. Bartholomew]

I'm working on a system that is very address-centric and detection of
duplicate addresses is very important. As a result we have broken
addresses down into many parts (DDL below, but I've left out some
reference tables for conciseness), these being state, locality, street,
street number, and address. The breakdown is roughly consistent with
Australian addressing standards, we're working on finalising this.

Because we carry the primary key down each of the levels, this has
resulted in our address table having a very wide primary key (around
170 characters). We refer to addresses from a number of other tables
and although my instinct is to use this natural key in the other tables
I wonder if we should just put a unique index on the natural key,
create a surrogate primary key and use it in the other table. Any
thoughts?
CREATE TABLE dbo.States (
StateID varchar (3) NOT NULL ,
StateName varchar (50) NOT NULL ,

CONSTRAINT PK_AddressStates PRIMARY KEY NONCLUSTERED
(
StateID
)
)

CREATE TABLE dbo.Localities (
Locality varchar (46) NOT NULL ,
StateID varchar (3) NOT NULL ,
Postcode char (4) NOT NULL ,

CONSTRAINT PK_Localities PRIMARY KEY NONCLUSTERED
(
Locality,
StateID,
Postcode
),

CONSTRAINT FK_AddressLocalities_AddressStates FOREIGN KEY
(
StateID
) REFERENCES dbo.States (
StateID
)
)

CREATE TABLE dbo.Streets (
StreetName varchar (35) NOT NULL ,
StreetTypeID varchar (10) NOT NULL ,
StreetDirectionID varchar (2) NOT NULL ,
Locality varchar (46) NOT NULL ,
StateID varchar (3) NOT NULL ,
Postcode char (4) NOT NULL ,

CONSTRAINT PK_Streets PRIMARY KEY CLUSTERED
(
StreetName,
StreetTypeID,
StreetDirectionID,
Locality,
StateID,
Postcode
),

CONSTRAINT FK_Streets_Localities FOREIGN KEY
(
Postcode,
Locality,
StateID
) REFERENCES dbo.Localities (
Postcode,
Locality,
StateID
)
)

CREATE TABLE dbo.StreetNumbers (
StreetName varchar (35) NOT NULL ,
StreetTypeID varchar (10) NOT NULL ,
StreetDirectionID varchar (2) NOT NULL ,
Locality varchar (46) NOT NULL ,
StateID varchar (3) NOT NULL ,
Postcode char (4) NOT NULL ,
StreetNumber varchar (15) NOT NULL ,
BuildingName varchar (100) NOT NULL ,

CONSTRAINT PK_StreetNumbers PRIMARY KEY CLUSTERED
(
StreetName,
StreetTypeID,
StreetDirectionID,
Locality,
StateID,
Postcode,
StreetNumber
),

CONSTRAINT FK_StreetNumbers_Streets FOREIGN KEY
(
StreetName,
StreetTypeID,
StreetDirectionID,
Locality,
StateID,
Postcode
) REFERENCES dbo.Streets (
StreetName,
StreetTypeID,
StreetDirectionID,
Locality,
StateID,
Postcode
)
)

CREATE TABLE dbo.Addresses (
StreetName varchar (35) NOT NULL ,
StreetTypeID varchar (10) NOT NULL ,
StreetDirectionID varchar (2) NOT NULL ,
Locality varchar (46) NOT NULL ,
StateID varchar (3) NOT NULL ,
Postcode char (4) NOT NULL ,
StreetNumber varchar (15) NOT NULL ,
AddressTypeID varchar (6) NOT NULL ,
AddressName varchar (20) NOT NULL ,

CONSTRAINT PK_StreetNumberPrefixes PRIMARY KEY CLUSTERED
(
StreetName,
StreetTypeID,
StreetDirectionID,
Locality,
StateID,
Postcode,
StreetNumber,
AddressTypeID,
AddressName
),

CONSTRAINT FK_Addresses_StreetNumbers FOREIGN KEY
(
StreetName,
StreetTypeID,
StreetDirectionID,
Locality,
StateID,
Postcode,
StreetNumber
) REFERENCES dbo.StreetNumbers (
StreetName,
StreetTypeID,
StreetDirectionID,
Locality,
StateID,
Postcode,
StreetNumber
)
)

[DM Unseen]

William,

the most difficult issue is that each country has it's own scheme, and
most ppl here prob do not know the australian one(maybe you should
explain it so we can validate your model).

e.g. what is a locality? a city town or village? Does a whole city has
exactly 1 postal code (this is implied in your model).

Can you deduce a street from a postal code or vice versa or both? Again
your model suggests this. Realise this contradicts locality constraint!

You have a table for addresses and streetnumbers, but this is
superfluous, if you have all streetnumbers why would you need extra
addresses? your current schema shows that you can have 2 different
addresses referencing the same streetnumber (which is in fact the
*real* address in my opinion), is this by design?

Pls elaborate fon your model first

DM Unseen

[William D. Bartholomew]

I replied the other day but it seems to have vanished. I apologise if
anyone is getting this for the second time. Addressing your points
individually:

e.g. what is a locality? a city town or village? Does a whole city has
exactly 1 postal code (this is implied in your model).
A locality is really a delivery area. In cities it is a suburb and in
towns the whole town is likely to be a locality. For example,
Indooroopilly (a suburb of Brisbane, Queensland) has the post code
4068. Boonah (a town in Queensland) has the post code 4310.
Can you deduce a street from a postal code or vice versa or both? Again
your model suggests this. Realise this contradicts locality constraint!
The order if this key was incorrect, it should be:

Locality,
StateID
Postcode
You have a table for addresses and streetnumbers, but this is
superfluous, if you have all streetnumbers why would you need extra
addresses? your current schema shows that you can have 2 different
addresses referencing the same streetnumber (which is in fact the
*real* address in my opinion), is this by design?

A street number is for example:

123 Fake Street, Springfield...
45 Fake Street, Springfield...

Whereas an address is:

Unit 5, 123 Fake Street, Springfield...
Shop 12A, 123 Fake Street, Springfield...
Level 14, 45 Fake Street Springfield...

So this is correct, there are multiple addresses within a street
number. The overall hierarchy is:

State (Queensland)
+Locality (Indooroopilly, Queensland 4068)
++Steet (Station Road, Indooroopilly, Queensland 4068)
+++Street Number (1 Station Road, Indooroopilly, Queensland 4068)
++++Address (Unit 1, 1 Station Road, Indooroopilly, Queensland 4068)

[DM Unseen]

OK this makes it clear,

It seems your model is not correct, you need to realise what *defines*
a postal code (it seems a locality defines a postal code, btw in
Holland a street,city and house number define a postal code, and a
postal code and an number define an adress)

It should be

State (Queensland)

+Locality (Indooroopilly, Queensland 4068) PK Postal Code, PK
(Locality,StateID)
++Steet (Station Road, 4068) PK (Street, Postal Code)
+++Street Number(1,Station Road, 4068) PK(Street, Postal Code, Number)
++++Address (Unit 1, 1 Station Road, 4068) PK(all fields)

the question is do you want to be exaustive, if not you can skip street
and streetnumber tables as well

DM Unseen