Contact company history

[greerso]

I started a contact database with separate tables for companies and contacts joined by a relationship so I can add contacts to a company. This way when I update company info, I only do it once and its updated for all my contacts.

I'd like to track when contacts move from company to company rather than delete them from one company and add them to another. My idea was to create a third table and place in between the contacts and companies table relationship adding fields date start and date end.

Since I'm a beginner I hoped to get some feedback on whether this would be a sensible approach or if there is another way others have already had success with.

Thanks,

Danny

[greerso]

I've created a 3rd table that records what date a contact is added to a company and added a field named inactive to that table that when checked enters todays date into the dateend field.

It feels like this is too complicated and tracking the history of contacts at a company and which companies a contact has been at cannot be a rare requirement.

The only alternative I can think of is to leave the contacts and companies tables connected by a simple one-to-many relationship and start a new separate table that logs changes.

Danny

[greerso]

I have now realized that i've overlooked contacts that don't have a company. This is a problem because I'm creating separate tables for phones and addresses. Phones are not a problem because contacts have their own phone numbers. I had planned to relate addresses to companies since the addresses are shared but this does not work for contacts that do not have a company.