Physical Address Schema

database-designschema

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.

Best Answer

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.

Related Question