In a previous article I discussed how I would set up a table for all types of contacts for SQL Server. In this table, called Contacts, any type of contact could be stored, whether this be a supplier, a customer or whatever. The type of contact was stored in the look-up table ContactType. A foreign key on the Contacts table indicates the type of the contact. The contact table as of yet was a small, useless table, with nothing more than an automatic incrementing ID, combined with the column with the key in the ContactTypes table indicating the type of contact.
This table is obviously useless this way. Therefore, in this article we will augment the table by providing the possibility of adding addresses to a contact. Since a contact can have multiple addresses, there is at least a one-to-many relationship between the contact table and the address table. I am assuming that an address always belongs to exactly one contact. This way, if one contact changes his address, all other contacts residing on the same address will have likely to be changed also. Sometimes, this will be a nuisance, for instance when a family moves and you will have to update multiple records. But in case of a divorce, one contact is moving from the address, while the other contact is staying at the old address. Your client tool can manage multiple address changes for multiple contacts, if required, so this need not be a problem. There is a small overhead of redundant data, but you gain a lot of flexibility.
Since a contact can have different types of addresses, an additional look-up table is required, specifying the type of address. This follows the same principle of the contact types. The table will, just like the contact types table, contain only address types and, therefore, is called AddressTypes. The AddressTypes table only has two columns, the automatic incrementing identity column, called ID, and a column containing the description of the address type, called Description.
Summarizing, a contact can have multiple addresses, but only one type of address per contact. The table named Addresses is constructed of rows, each row representing exactly one address. There is no use for having an address without a contact. Therefore, you should put a uniqueness constraint on the two columns AddressTypeID and ContactID. Even stronger, you can set the primary key of the Addresses table on these two columns, making both columns not nullable. You can add the actual address information, by adding columns like Street, HouseNumber, PostalCode, Provence, TelephoneNumber, CellPhoneNumber, Country and so forth. When specifying the country, I suggest to again use a look-up table like AddressTypes. This time, create a two column table called Countries, consisting of the automatic incrementing identity column ID and the column Description, containing name of the country. Additional columns can be added, for instance a country code.
The table Addresses thus has to have a column called CountryID, with a foreign key relation to the table called Countries.
The actual SQL Server T-SQL will follow.