2 of 2 people found this helpful
Ideally you would have join tables between Contact and Phone, Organisation and Contact, Organisation and Phone. This system allows you to store data once only and link it as many times as needed in as many ways a needed. The specific question of phone extensions is a little different from the base phone number, but would probably be best added as an optional field in the join table, as the extension is particular to an individual within an organisation, which is what the join table record is also. For this reason a join table is a better solution than a multi-line key.
Thanks for the response and thoughts. Do you tend to use a lot of join tables in general? I've decided to re-write the solution I've built part-time over the past few years. It was built very much in a make-it-up-as-you-go kind of way. I've been trying to optimize it and have to come to the realization that the best way forward is to start fresh. So I've started with the module I wrote first (and hardly remember), the contact manager.
Every contact must belong to a parent organization. Since this is for business, it is assumed that almost every contact will work for some organization. For the handful of self-employed contacts, the user must first create an organization record. How does a join table help me here over storing the parent organization record ID in the contact record and matching that way?
Every organization and contact must have a primary address and phone number selected. The Addresses and Phone Numbers tables store both organization and contact data. The contact record has a calculated multi-line with both the contact ID and parent organization ID, the contact ID prefixed with "C" and the organization ID with "O". The Phone Numbers table has a calculated field that stores either the parent organization ID or contact ID, with the "C" or "O", for matching purposes. When viewing phone numbers for a contact, all of the organization numbers show up, allowing the user to select one of these as the primary number for the contact. Addresses work the same way.
In most cases, the user will not need to add any contact-specific information to an organization address or phone number. However, in a few cases, the contact might have a unique phone extension or room/building information. I was thinking that when that situation occurs, the user clicks something (ie: an "Add Extension" button), the record is copied and linked to the contact and I find a way to filter out the parent organization's record and show the newly created contact record in the portal instead.
How would you advise the join table approach work? When a new contact is created, do I add a join record between that contact and every one of the parent organization's phone numbers? And if a new organization phone number is added, I add a join record for every organization contact?
Also, how does a join table help me with organization phone numbers? The number will never belong to more than one organization. I can understand how the join table will allow me to store the phone extension uniquely for each contact, but why would a join table help between organization and phone?
Thanks for your input! Much appreciated!
1.) Do some organizations have multiple contacts in your database?
2.) Do organizations and contacts have the exact same fields? Are they in separate tables?
1. Yes. Almost always.
2. No. Fields are different. They are in two different tables.
If an organization and a contact have the same telephone number and different extensions, you are having issues. Is that correct? It seems that is an organization and contact have different extensions then they also have different phone numbers. Is this an issue because you do not want to have the same 10-digit number included in the organization and contact tables as it was not be fully normalized?
In other words, I'm curious because I cannot determine why this is a problem.
If the organization and contact have different extensions, to me, that is the same as having different phone numbers. They user should enter the phone number and extension for the contact and select the phone record for the contact as the primary phone number to show in the portal.
Help me understand what I am not understanding.
I have three tables, Organizations, Contacts, and Phone Numbers. Whether the phone number belongs to a contact or an organization, the number is stored in the same table. When viewing an organization, I want to see all organization phone numbers. When viewing a contact, I want to see BOTH the parent organization's phone numbers AND any contact-specific numbers. In other words, all contacts for an organization see the parent organization's phone numbers when viewing the contact. Contacts may also have contact-specific phone numbers that only show up for that specific contact. No problems with any of this, using a multiline key as explained above.
The trouble comes when I want to take a shared organization phone number and let a contact attach an extension that is unique to that contact. That's where I was running into trouble. Since the organization phone number is shared among all the organization's contacts, any extension would be seen by all contacts.
Now, all of that said, I've spent the past few hours reworking things per keywords advice and have moved to join tables between Organization and Phone as well as Contact and Phone. This is now allowing me to store the phone extension in the join table and everything works great.
When a new organization phone number is created, I create the Phone Number record as well as the join table record to connect Organization with Phone Number. I also cycle through all organization contacts (if any) and add a join table record for each of them so that they see the new phone number (and can add an extension if desired).
Likewise, if I add a new contact, I cycle through all organization Phone Number records and make a join table record joining the new contact to each of the existing organization phone numbers.
If the organization or contact does not have a primary phone number designated, the new record is also set as the primary. For this, I'm storing the primary phone ID in a field in the organization or contact. I'm still thinking about this part a bit, but it all seems to work.
It sounds like the join tables were the solution. That was what my questions were probing around. If I can help, let me know.