Database table structure

One table "COMPANIES" needs to be linked to another table "CONTACTS".  Here is the twist: the contact for each company will change over time, but we need to keep the contacts.  My initial solution was to create a join table named something like "ACTIVE_CONTACT" that would hold pairs of the Company ID and the Contact ID, but i didn't know how to prevent a duplicate Company ID from being entered in the "ACTIVE_CONTACT" table. How would I do that?

Or, maybe using a join table is not the way to go?
rrhandle8Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Paul JacksonConnect With a Mentor Software EngineerCommented:
A join table is the way to go. If you need to keep previous company contacts I would have a join table as you have described but rather than calling it active_contact just call it company_contacts and as well as the Company Id and Contact Id have another column called Active and make it a bit datatype which would be set for active contact records. You would make the Primary Key of this table a joint key of the Company Id and the Contact Id
Doing it this way means you would have a history of company contacts and the active contacts.

Alternatively if you felt that there was no need to keep a history of the contacts for each company in the company_contacts table you would just use Columns of Company ID and Contact ID and make them a company id the primary key for the join table to prevent duplicate contacts for a company. Obviously you will need logic in your stored procedures or business logic to delete a current active contact for a company when another is added.
0
 
rrhandle8Author Commented:
I've requested that this question be deleted for the following reason:

I decide to make the Company ID column unique.  
0
 
rrhandle8Author Commented:
Jacko72,  I did not see your answer when I tried to delete the question.  I am awarding you now.  Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.