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?