Multiple one-to-many relationships

[Solice]

Hi

I don't have much experience in database design and would appreciate some advice.

I am creating a database currently and this time i thought i would put all a company's contacts in a single table named "tblContacts" and add a contact type field to distinguish between the different types.

Now i have another table called "tblInstructions" (snap shot of tables below)

Contact ID Instruction ID
Contact type Vendor
Title Applicant
First Name Vendor Solicitor
etc.... Applicant Solicitor
etc.......

I have set up a one to many relationship between the Contact ID and Vendors Solicitor but am having problems linking the Contact ID to the Applicant Solicitors aswell.

Is it possible to setup multiple one to many relationships or do i need to spilt up the contacts table into separate tables?

 

[Solice]

I attach an image of what i am trying to do. After thinking about it i don't think it can be done the way i was intending.

I think i need to scrap the contacts table and do separate tables for each contact type or maybe keep the contacts table and have feeder tables, for example:

tblVendor
VendorID (pk)
contact ID

Will my ideas work ? Does anybody have any other suggestions?

 

[NotSoRandomOne]

First, it seems like 'Instructions' is a terrible name, but maybe I'm wrong. Is it really 'Projects?', or something of the like? Instructions to do what, exactly?

As to your question, it is simple to do what you want. In your 'Instructions' table, use lookup fields to insert each of those contacts. Then remove the lookup fields later, as outlined towards the bottom of this thread.

 

[music_al]

Ironically I created a database similar to what youre trying to do. Id say you might have made a couple of small errors in your structure, one being, a vendor might have more than one property. Well, thats what I found when I was creating mine. If you upload it I can check it over if you like.

Al

 

[dcb]

Can I suggest something like this:

--tblInstructions
InstructionsID
Other Detail
 
--tblAssociates
AssociatesID  (PK)
InstructionsID  (FK)
AssociateType   (Vendor;VendorsSolicitor;Negotiator;Applicant;ApplicantSolicitor)
ContactID  (FK)
CompanyID  (FK) Autofill 
Notes
 
--tblContacts
CompanyID
Company
Detail etc
 
--tblCompany
ContactID  (PK)
CompanyID  (FK)

Thus creating a n:n between contacts and instructions
This method also allows for the possibility of more than one solicitor etc.

You will notice that I seperated company from the contact in the associate relationship as a contact can change companies however you cant allow your data to change history because of this.

 

[Solice]

Thanks for the replies.

I haven't been able to setup multiple one to many relationships between two tables and i don't think its possible.

However i have been able to setup combo boxes on a form ,named "frminstructions" that allow the user to select the relevant contact and the selection is stored in the table named "tblInstructions".

I think that will do.