One address table with different address types?

[scion111]

For maintaining address info, I currently have 3 tables, called
Contacts, Addresses and AddressTypes. Here are the fields in each table
(note: I'm not showing fields which do not pertain to this question):

Contacts:
Contact ID
First Name
Last Name
Contact Type ID

Addresses:
Address ID
Address Type ID
Contact ID
Address 1
Address 2
City
State
Zip

AddressTypes:
Address Type ID
Address Type

The primary key of the Contacts table Is Contact ID. The primary key
of the AddressTypes table Is Address Type ID. The primary key of the
Addresses table is a composite key based on Contact ID and Address Type
ID. I have a one to many relationship between the contacts table and
the Addresses table. I also have a one to many relationship between the
AddressTypes table and the Addresses table. Address types could be
home, work, mailing, etc.

I'm using the composite key to prevent a contact from having one
address type applied to two different addresses (i.e., the contact can
not have two addresses with the address type of 'home').

I'd like to add another table for Vendors, and utilize the same
Addresses table to store their address information. I could accomplish
this, I assume, by creating a one to many relationship between the
Contacts table and the Addresses table utilizing the Contact ID and
Address ID, and also creating a one to many relationship between the
Vendor ID (in my new table) and the Address ID in the Addresses table.
This would then eliminate the need for the Contact ID field in the
Addresses table.

However, if I remove the Contact ID field from the Addresses table, I
can't figure out how to prevent a contact or a vendor from being
allowed to have multiple addresses with the same address type (which I
accomplished before by using the composite primary key as explained
above).

I'm probably missing something simple, and I hope I've explained this
adequately. Any help or suggestions would be greatly appreciated!

 

[KARL DEWEY]

Why not just have an address type of "Vendor"?

 

[scion111]

That won't help. The idea of the AddressTypes table is to store
information about the various types of addresses that Contacts (and
hopefully Vendors) can have. Here's an example:

Contact ID AddressType ID Address Type Description
1 1 Home
1 2 Work
1 3 Mailing

My composite key (Contact ID + AddressTypeID) prevented a contact from
having 2 addresses designated as "Mailing".

 

[Smartin]

For maintaining address info, I currently have 3 tables, called
Contacts, Addresses and AddressTypes. Here are the fields in each table
(note: I'm not showing fields which do not pertain to this question):

Contacts:
Contact ID
First Name
Last Name
Contact Type ID

Addresses:
Address ID
Address Type ID
Contact ID
Address 1
Address 2
City
State
Zip

AddressTypes:
Address Type ID
Address Type

The primary key of the Contacts table Is Contact ID. The primary key
of the AddressTypes table Is Address Type ID. The primary key of the
Addresses table is a composite key based on Contact ID and Address Type
ID. I have a one to many relationship between the contacts table and
the Addresses table. I also have a one to many relationship between the
AddressTypes table and the Addresses table. Address types could be
home, work, mailing, etc.

I'm using the composite key to prevent a contact from having one
address type applied to two different addresses (i.e., the contact can
not have two addresses with the address type of 'home').

I'd like to add another table for Vendors, and utilize the same
Addresses table to store their address information. I could accomplish
this, I assume, by creating a one to many relationship between the
Contacts table and the Addresses table utilizing the Contact ID and
Address ID, and also creating a one to many relationship between the
Vendor ID (in my new table) and the Address ID in the Addresses table.
This would then eliminate the need for the Contact ID field in the
Addresses table.

However, if I remove the Contact ID field from the Addresses table, I
can't figure out how to prevent a contact or a vendor from being
allowed to have multiple addresses with the same address type (which I
accomplished before by using the composite primary key as explained
above).

I'm probably missing something simple, and I hope I've explained this
adequately. Any help or suggestions would be greatly appreciated!

Could a vendor be a type of Contact? (What are you describing in Contact
Type ID?)

 

[scion111]

No, Vendors cannot appear in the Contacts table. My Contacts table
only describes those who can be either a Client or a Prospect. I
prefer to keep my Vendor information in a separate table.

 

[Smartin]

OK I hear what you are saying, but since I still don't know what
"Contact Type ID" is all about, I still wonder if vendors could be
included here?

If not, someone much more brilliant than me has posted a great model of
people / contacts. Let's see if I can find it...

one moment please...

.... ok I am impatient can't find it at the moment. One of the gurus that
frequent the Access groups has done something like this. Hopefully
someone can identify the resource.

 

[scion111]

My apologies - I should not have included the field Contact Type ID, as
it is not relevant to my question (Contact Type ID relates to another
table that just stores the values Contact ID and Contact Type (which
can be either Client or Prospect).

I've read several posts about storing addresses. Seems to me that
keeping 2 tables (one for Clients and Prospects, one for Vendors) works
better in my situation. Having them share an address table would be
great, but introduces the problem I've specified - how to only allow
one address per address "type" (ie., you can only have one home
address, one mailing address, etc.)

Thanks everyone for your input thus far. Hopefully, someone wiser than
me can come up with a solution!