Sql-server – Should I extract / normalize the mailing address despite requirements limiting number / type of addresses

normalizationschemasql server

I currently am working on a contacts application (similar to Outlook contacts combined with eVites and LinkedIn). The requirements for the application are to limit the addresses (and phone / email addresses) to two – primary and secondary.

Normally, I would extract the addresses into their own table with a key back to the main profile along with the address type in the address table. However, with the specific limitation of the number of addresses, I am considering having the addresses in their own table – with a foreign key from the profile table to the address table (one foreign key for primary and one for secondary).

Essentially, my question is:

Should I prepare for possible changes in the future to allow for the capability of more than two addresses in the future – or work within the limitations of the current requirements to specifically limit (via the database schema) which types and how many addresses can be associated?

Thank you for your help.

My two proposed schemas are as follows (please forgive the formatting – having trouble with the limitations of the WYSIWG editor):

First Schema:

NOTE: code / constraints / triggers would limit it to the primary / secondary rather than the database schema

Profile

(profile information fields)

Address

  • AddressId
  • ProfileId
  • AddressTypeId
  • Street Address
  • City
  • State
  • Zip

AddressType

  • AddressTypeId
  • AddressType

Second Schema

Limitations are through the profile schema

Profile

  • …
  • PrimaryAddressId
  • SecondaryAddressId

Address

(may be further normalized)

  • AddressId
  • Street Address
  • City
  • State
  • Zip

Best Answer

Always bet on requirements changing, or being reinterpreted, at some point in the future.

Does the requirements documentation explicitly state that there are exactly two or just that there should be up to one primary and up to one secondary? If the latter then what could be meant is there could be many addresses of which up to one is the primary and up to one is the secondary and you should allow for more addresses.

Even if not, I would still err on the side of addresses being separate entities if you need to support more than one for any other given entity. The extra work now should be minimal, if you need to emulate the non-normal-form for the application logic you can by various means, and when the requirements change later your job will be a lot easier (and you can either impress the client/devs with your foresight or keep shtum and charge them as if the change request does require significant structural alterations in the DB).

Related Question