Relational Design - Organisations and Contacts Addresses - Help Please

[Kev.T]

Hello,

Could someone please point me in the right direction regarding a
relational design question.

The situation is holding contact addresses for Individual contacts and
their organisations, where each organisation could have several
contacts with different addresses or the same address. Not wanting to
duplicate addresses if possible, I am thinking about a table holding
only the address info and separate tables for organisations and
contacts. But I then need to make a link from the Org and Individual
to a shared address (ie where they are the same thing).

I would greatly appreciate any guidance that can be thrown my way.
Many thanks, and I look forward to reading any responses.

Kev

[Krij]

Hi!
I'll give it a try :-)

I have a similar construction in a db.

I used four tables:

tblIndividualsContacts
tblZipCodes
tblOrg
tblRegistration

tblRegistration is used for, that's right!, registration of contacts
and what organization they belong to.

tblZipCodes holds: ZipCodeID,ZipCode, City

and the rest is obvious I think.

Don't know really if this is the optimal design, but it works fine for
our company.

I think you could do without tblRegistration, if your goal is only to
register when creating a new contact. Then all registration would take
place in tblIndividualsContacts.

Hope this can help you out :-)

Kev.T skrev:

Hello,

Could someone please point me in the right direction regarding a
relational design question.

The situation is holding contact addresses for Individual contacts and
their organisations, where each organisation could have several
contacts with different addresses or the same address. Not wanting to
duplicate addresses if possible, I am thinking about a table holding
only the address info and separate tables for organisations and
contacts. But I then need to make a link from the Org and Individual
to a shared address (ie where they are the same thing).

I would greatly appreciate any guidance that can be thrown my way.
Many thanks, and I look forward to reading any responses.

Kev

[Ron2006]

Possibilities:

tblContact
ContactID autonumber
first name
last name
other fields
CompanyID foreign Key to Company table
AddressID foreign key to Address Table
tblCompany
CompanID autonumber
Company name
other company info
AddressID foreign key to Address Table

tblAddress
AddressID autonumber
addressline1
addressline2
addressline3
ZipcodeID foreign key to Zipcode table

tblZipcode
ZipcodeID autonumber
Zipcode
City
State
Country

If you make the zipcode table as above then you can
have to city names for same zipcode which can occur. If you use the
ZipCode as the key then ONLY ONE name can be associated with zipcode.

Will need to allow for changing the address for the contact, but can
default to the company address when originally created. How to present
the possibility or recognizing the potential may take some thought.

[Kev.T]

Thanks Krij I will give your suggestion a try, and let you know how I
got on.
Krij wrote:

Hi!
I'll give it a try :-)

I have a similar construction in a db.

I used four tables:

tblIndividualsContacts
tblZipCodes
tblOrg
tblRegistration

tblRegistration is used for, that's right!, registration of contacts
and what organization they belong to.

tblZipCodes holds: ZipCodeID,ZipCode, City

and the rest is obvious I think.

Don't know really if this is the optimal design, but it works fine for
our company.

I think you could do without tblRegistration, if your goal is only to
register when creating a new contact. Then all registration would take
place in tblIndividualsContacts.

Hope this can help you out :-)

Kev.T skrev:

Hello,

Could someone please point me in the right direction regarding a
relational design question.

The situation is holding contact addresses for Individual contacts and
their organisations, where each organisation could have several
contacts with different addresses or the same address. Not wanting to
duplicate addresses if possible, I am thinking about a table holding
only the address info and separate tables for organisations and
contacts. But I then need to make a link from the Org and Individual
to a shared address (ie where they are the same thing).

I would greatly appreciate any guidance that can be thrown my way.
Many thanks, and I look forward to reading any responses.

Kev

[Kev.T]

Thanks Ron, I will have a play with this one and let you know how I
get on.

Ron2006 wrote:

Possibilities:

tblContact
ContactID autonumber
first name
last name
other fields
CompanyID foreign Key to Company table
AddressID foreign key to Address Table
tblCompany
CompanID autonumber
Company name
other company info
AddressID foreign key to Address Table

tblAddress
AddressID autonumber
addressline1
addressline2
addressline3
ZipcodeID foreign key to Zipcode table

tblZipcode
ZipcodeID autonumber
Zipcode
City
State
Country

If you make the zipcode table as above then you can
have to city names for same zipcode which can occur. If you use the
ZipCode as the key then ONLY ONE name can be associated with zipcode.

Will need to allow for changing the address for the contact, but can
default to the company address when originally created. How to present
the possibility or recognizing the potential may take some thought.