Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-18-2017, 09:58 AM   #1
Danick
Newly Registered User
 
Join Date: Sep 2008
Posts: 189
Thanks: 28
Thanked 2 Times in 2 Posts
Danick is on a distinguished road
Contacts and Companies in CRM database

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

Danick is offline   Reply With Quote
Old 05-18-2017, 10:22 AM   #2
plog
Newly Registered User
 
Join Date: May 2011
Posts: 7,856
Thanks: 10
Thanked 1,900 Times in 1,861 Posts
plog has a spectacular aura about plog has a spectacular aura about plog has a spectacular aura about
Re: Contacts and Companies in CRM database

Quote:
The best way would be to use tags or links, but I don't think Access is able to do that.
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.

Quote:
So what would be the best way to deal with type of situation without making a huge change to the table structure / design.
When you add that stipulation on to it; my advice would be 2 records in the Contacts table for them.
plog is offline   Reply With Quote
Old 05-18-2017, 01:43 PM   #3
Danick
Newly Registered User
 
Join Date: Sep 2008
Posts: 189
Thanks: 28
Thanked 2 Times in 2 Posts
Danick is on a distinguished road
Re: Contacts and Companies in CRM database

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...

Danick is offline   Reply With Quote
Old 05-18-2017, 05:41 PM   #4
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 1,515
Thanks: 2
Thanked 327 Times in 323 Posts
Cronk will become famous soon enough
Re: Contacts and Companies in CRM database

...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.
Cronk is offline   Reply With Quote
Old 05-20-2017, 05:50 AM   #5
Lightwave
Ad astra
 
Lightwave's Avatar
 
Join Date: Sep 2004
Location: Edinburgh
Posts: 1,270
Thanks: 88
Thanked 99 Times in 94 Posts
Lightwave will become famous soon enough
Re: Contacts and Companies in CRM database

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.

Lightwave is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Membership Database - Companies v. Individuals LenaOlson Tables 11 04-10-2015 12:35 PM
Data model for all contacts - companies and people (Party!) ML! Tables 3 01-11-2013 01:13 AM
Numerous companies and many contacts mtobey Tables 9 08-14-2012 03:02 PM
Contacts Database karthikcoep General 3 08-17-2009 08:35 AM
contacts database cuttsy Tables 4 06-09-2004 03:57 AM




All times are GMT -8. The time now is 06:35 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World