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
Address1
,City1
,State1
,Address2
,City2
,State2
, etc.