Relationship question

I have a couple tables in a database. One for vendors and includes all the contact information you would expect. I also tables for customers and employees. A lot of the information seems to be the same for all three tables such as address and contact information. However if I try to use relationship to connect each of the three tables to a fourth table containing all the contact info I am unable to create a customer without creating a vendor linked to the same address. I have tried one-to-many relationships from the vendor or customer to the contact info table. I have also tried one-to-one.

Is there another type of relationship I can try or set it up in another way? I have thought about denormalizing the tables or just going without relationships altogether and just looking up the info using and id or something.


Any thoughts?

re: Relationship question

Create the tables as follows:

tblAddress
AddressID (Primary Key - Autonumber)
AddressLine1
AddressLine2
AddressLine3
PostCode
Country
ContactName
Telephone

tblVendor
VendorID (Primary Key)
VendorName
AddressID (Foreign key reference to Primary key of tblAddress)

tblEmployee
EmployeeID (Primary Key)
EmployeeName
AddressID (Foreign key reference to Primary key of tblAddress)

tblCustomer
CustomerID (Primary Key)
CustomerName
AddressID (Foreign key reference to Primary key of tblAddress)

tblAddress has a one to many relationship with each of the other tables.

Mary

re: Relationship question

Your tblAddress should probably look something like this :
Each of the FK fields would have to be defined to allow Nulls and one, and only one, of the three should always be filled.