Physical Address Schema

Question

I'm looking at adding addresses to an existing database, and am trying to work out the advantages and disadvantages of the following methods (and if there are any I've overlooked).

1. Store the address inline with the source record
2. Have an address table and add a foreign key to the entity
3. Have an address table with a foreign key that links to the entity

I'd like to steer clear from storing it inline for normalisation reasons, but I'm unsure as to which of the other options to go for. It's possible at some point down the line that we want additional entity types to have addresses on them, which makes option 2 look like the best one. This option (as far as I can see) has the potential to leave unlinked data around though.

Answer 1

Short answer:

Either option 1 or option 2:

• In-line (one address per source record)
• Child table with an FK linked to source record (allows >1 address per source)

Longer...

A separate address table will normally have an FK to some parent record.
Each address will maybe have a type (billing, delivery, etc)

Addresses are not shared or reusable or finite: that is, each address for a source entry has little in common with other entries.

• different source records wouldn't have shared addresses (eg many-many in the middle between source and address)
• separate address table with FK(s) can't have multiple parents either
• having the FKs on source records to some address table would be unusual.

Finally, consider how you use addresses. You add or update them. You don't look for an existing address and link to that. Each address is per source only.

Answer 2

A person or entity can potentially have multiple addresses, so you want a foreign key on the address table against the person record, something like:

create table Party (
       PartyID       int not null  -- primary key
       ,Surname       varchar (50)
       ,FirstName     varchar (50)
       [...]
)
go

alter table Party
  add constraint PK_Party
      primary key (PartyID)
go

create table [Address] (
       AddressID     int not null  -- primary key
      ,PartyID       int not null  -- fk reference to party
      ,AddressLine1  varchar (50)
      ,AddressLine2  varchar (50)
      [...]
      ,IsCurrent     char (1)      -- however you do true/false
)
go

alter table [Address]
  add constraint PK_Address
      primary key (AddressID)
go

alter table [address]
  add constraint FK_Address_Party
      foreign key (PartyID)
      references Party
go

Answer 3

If you have business rules based on address, you would need to create an addressing table with ZIP codes, and getting the street address (is what Google came for the portuguese word "logradouro" which is the terminology used by our postal service) from it using the ZIP code.

It allows for an address info much more useful than simply letting user filling the street address directly.