Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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 '11 at 10:20
    
Thanks @gbn, I've updated it now –  Matthew Steeples Dec 16 '11 at 10:46
add comment

3 Answers 3

up vote 2 down vote accepted

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.

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

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.

share|improve this answer
add comment

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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