One address table with different address types?

S

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

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".
 
S

Smartin

scion111 said:
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!
Click to expand...

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

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Address Design Structure 16
"Free" ip address in asset database, not remove? 0
redundant data - help with Contact Database 6
Design theory for maintaining historical address communications 4
Company and multiple addresses DB design 1
Table Question 1
Creating Relationships 5
One-to-one relationship 2

Top