2

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.

1
  • For points 2 and 3, probably better to say where you'd add the the foreign key. "to" and "from" are ambiguous
    – gbn
    Dec 16, 2011 at 10:20

3 Answers 3

4

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.

3
  • What if (for example) I was looking at storing addresses against records in a user table, and addresses against records in a customer table Dec 16, 2011 at 10:47
  • @Matthew Steeples: there aren't shared addresses. I'd have UserAddress and CustomerAddress.
    – gbn
    Dec 16, 2011 at 10:52
  • @gbn - good point about recording the address types on the address. Dec 16, 2011 at 15:26
3

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
0

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.

2
  • I don't know how this could work; a ZIP code does not uniquely identify a street address. Perhaps Portuguese postal codes do, but ZIP codes do not. Jan 23, 2015 at 18:12
  • @JonofAllTrades In my case, brazilian postal codes. In bigger cities, yes, the code identifies uniquely the street - my street have two codes because is interrupted by an square. Some BIG buildings have it's own postal code (or CEP, which is the name used here). Jan 25, 2015 at 16:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.