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 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

share|improve this question
    
I hearby present you with the Database Designer Good Citizen Award for not just putting the fields in the user table as Address1, City1, State1, Address2, City2, State2, etc. –  Jon of All Trades Oct 23 '13 at 15:56
    
Thanks, Jon! :) –  Laura Ritchey Oct 23 '13 at 19:40

1 Answer 1

up vote 1 down vote accepted

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).

share|improve this answer
1  
The requirements do specifically state that it is no more than two addresses (I asked the question specifically to clarify because I don't like limiting it if possible) - but what you said makes sense. It really isn't much more work at all - and I'd prefer to save work in the long run. I've debated on even just allowing more than two addresses and letting them tell me that they don't like it that way if they are stuck on only allowing the two addresses. I'm leaning towards following their requirements on the GUI / application side though and letting the database support more than two instead. –  Laura Ritchey Oct 23 '13 at 15:48

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.