Contacts and Companies in CRM database (1 Viewer)

I have a database that is a typical contact database where my table design has a one to many relationship for companies and contacts. That is, one company has many contacts but one contact can only be in one company.

This has worked great for years and never had an issue. Now I'm looking at a contact that actually works at two companies. I could create the same contact in both companies, but this contact has the same phone number and email, etc... which could lead to errors when only one of the two entries is updated.
The best way would be to use tags or links, but I don't think Access is able to do that. At least not my version of Access 2003.

So what would be the best way to deal with type of situation without making a huge change to the table structure / design.

Thanks

The best way would be to use tags or links, but I don't think Access is able to do that.

Click to expand...

Incorrect. The best way would be with a junction table (https://en.wikipedia.org/wiki/Associative_entity). Its how you deal with a many-to-many relationship. I wouldn't consider it a major table structure change, but it will require a new table and population of said table.

So what would be the best way to deal with type of situation without making a huge change to the table structure / design.

Click to expand...

When you add that stipulation on to it; my advice would be 2 records in the Contacts table for them.

Thanks, I guess I'll do the two entries for now since its only happened once in countless years. But it does bother me, so I may do the junction table if I get bored one day and can no longer live with it...

...or one day after the contact data gets out of sync because only one of the two records was updated and there is a lost contract or some other cost to your organization.

Danick I would definitely consider understanding the concept of Junction tables being able to model many to many relationships is very important.

For instance they are often used in Booking databases. You have a table of individuals and a table of courses and you have a table of bookings which relate individuals to courses. The bookings table is a junction table and the course table and the individual table are linked by a many to many relationship. One individual can be on many courses and one course can be taken by many individuals.

It is extremely elegant solution to quite a large problem area.