Ocean West Posted March 16, 2004 Share Posted March 16, 2004 I have see this done many ways and have been reviewing many resources What are the pros and cons on the implementation of this scenario Tables: Company Contacts Locations Phone Each Table contains data unique but each the company and contact could have their own addresses and phone numbers How would the Relationship work best? Contacts to Phone ContactID = ContactID Company to Phone CompanyID = CompanyID In the Phone table have the following fields ContactID CompanyID Phone Number What of relationship between Contacts of the same company? Would a join table be the correct approach? CompanyContacts CompanyID ContactID 1001 002 1002 008 1002 012 In this case the contact or the company would have no direct relationship to the other without this join table. This would allow the creation of either with out the necessity of the other. For example I created a contact record but don't know what company they work for. I could create a contact record and at a later time assign them to an existing company or a new one. via the join table. In the above approach this would allow for the contact to be joined to multiple companies. (if your business logic allows) Some contact managers assume every contact has a "company" and creates a company record. or you first have to populate a company record. Also in the case of locations and phone each the company and the contact could have their own (multiple) numbers & addresses. If I were to normalize this out (as I understand it) the one could create a join table between the contact and the phone number and also one between the contact and the address - this would be true as well with the company but what is the practical implementation? Your thoughts? Version: v7.x Platform: Mac OS X Panther Link to comment Share on other sites More sharing options...
Ugo DI LUCA Posted March 16, 2004 Share Posted March 16, 2004 Hi Stephen, I can't say what new stuff FM7 could bring in this situation, but I progressively built my own theory that Join Files should be preferred to other "keys manipulation" when the type of datas (and structure) you're working with can evolve. Human management is probably (and hopefully) the best example of evolvement. A Contact Management would in my opinion obviously also follow this rule. As you pointed, one contact, initially entered as a One To One entry, could work for multiple companies in the future. Studying other complex diagrams, companies could also be (or become) subsidiaries of others. So, definitely, whith FM6 or lower, I'd go with the Join File technique. As for the Company File, there are situations/businesses when a Single Contact could be a "Company". That is why I generally don't use Companies file, but rather Operators file, where either Individuals and Companies are stored at the same level. For matter of Normalization, even if this Operator is an Individual, he gets entered through the Join File, so that he has an entry into the Contact File as well. Waiting for others ideas too... Link to comment Share on other sites More sharing options...
Ocean West Posted March 16, 2004 Author Share Posted March 16, 2004 So if you want to show a portal for all "associates" or "contacts" that work at the same company what do you use as your key. If you use the company name that is dangerous because like me - many can't spell. I tend to agree with you on terms that the "company" file is not a true representation. Although there is general data concerning the company or "Account" as I call it. it is not necessarily data that is tied to any one contact. So your saying that the better model is if I have an Individual that isn't necessarily works for any company then they also get an "Account" record and a "Contact" record? Link to comment Share on other sites More sharing options...
CobaltSky Posted March 16, 2004 Share Posted March 16, 2004 Hi, A join file is not really necessary here. It may be worth considering if you are supporting many-to-many joins, but in the case of contacts and companies, usually it is many-to-one, and that can be accommodated readily enough by a direct join in which the companyID is stored as a foreign key on the contact record. In this scenario, if you need to create a contact record but you don't know the company, you create the contact record and leave the companyID field blank until it is known - and in cases where there may be two companies for one contact (if any) you'd enter both companyIDs into the one field (using it as a multi-key). With direct relations on foreign ID, you would be able to establish access to records for other contacts in the same company via a self-join on companyID within the contacts table. In the interests of simplicity and the 'kiss' principle etc, it's often preferable to keep the join file option in reserve for cases when it's unavoidable - especially in v6, where the total number of files in a solution can become a management issue. Link to comment Share on other sites More sharing options...
Ugo DI LUCA Posted March 17, 2004 Share Posted March 17, 2004 Hi Ray, I just can't stand when I'm 90% done with a Project and that suddenly some parameter brings the whole job down because of an unexpected Many To Many situation. You Link to comment Share on other sites More sharing options...
BobWeaver Posted March 22, 2004 Share Posted March 22, 2004 Stephen, I've been thinking about updating my own contact list for a while and though I haven't done it yet, I'm seriously considering a multilevel (recursive?) type of relationship in one file. A record could represent either a company, a department or a person. A One to many relationship from parent to child would allow a portal to show a company's departments and/or employees. Going the other direction, the relationship from an employee record would allow access to the parent department or company info. You could also have a person who works for company A and company B and have a multi-key going this way too. These situations could be handled in this kind of file. In a single file, there would obviously be address and phone number fields for all records which is what I have now in a two file solution (companies file with a default address and main phone number; and employees file with specific employee address and phone number). However these could be separated out into a related address file. That's my 2 cents worth. Link to comment Share on other sites More sharing options...
Ocean West Posted March 23, 2004 Author Share Posted March 23, 2004 I guess it is really up to the nature of the business rules that we are presented with I am primarily hoping to develop the best approach I can to meet the majority of the type of requests I get. Right now I have set it up for myself that I have a Contact Table this is for contact names I have a separate table for both phone number/email & addresses. I also have yet another table for "companies" or "accounts". I have created a join table called groups this is a table of company ID and Contact ID. By doing this I can have an individual associated with Multiple companies but depending on my presentation layer prevent this from occurring. - Also this allows for the reverse having companies with multiple contacts. I choose not to over complicate matters by creating a separate table for staff/employees and another for vendors but still used the existing phone / address tables. The biggest challenge is creating a clean transition between each the Business & Presentation layers Link to comment Share on other sites More sharing options...
Kurt Knippel Posted March 23, 2004 Share Posted March 23, 2004 Ocean West said: If I were to normalize this out (as I understand it) the one could create a join table between the contact and the phone number and also one between the contact and the address - this would be true as well with the company but what is the practical implementation? In this case no you would NOT have join files between Contact/Company & Address or Contact/Company & Phone. Each company may have its set of related phone numbers and addresses, and each contact may have its own related set of addresses and phone number. However multiple companies will not share addresses or phone numbers with each other and contacts only share them by virtue of being related to the same company. Version: v7.x Platform: Mac OS X Jaguar Link to comment Share on other sites More sharing options...
Ocean West Posted March 23, 2004 Author Share Posted March 23, 2004 Thanks guys for allowing me to think aloud. Here is a sample of my setup.. Version: v7.x Platform: Mac OS X Panther normal.fp7.zip Link to comment Share on other sites More sharing options...
CyborgSam Posted March 23, 2004 Share Posted March 23, 2004 Maybe it's me... The group table relates a Contact to a Company, but doesn't include any information about the group itself. Don't you need another table for Group info (name, et al.)? Link to comment Share on other sites More sharing options...
Ocean West Posted March 24, 2004 Author Share Posted March 24, 2004 I suppose you could put more fields in the "group" table to identify Group specific data. Link to comment Share on other sites More sharing options...
CyborgSam Posted March 24, 2004 Share Posted March 24, 2004 I think I'm missing something... Won't the Group table have multiple entries for a single "group"? Link to comment Share on other sites More sharing options...
Kurt Knippel Posted March 24, 2004 Share Posted March 24, 2004 Well this assumes that the "group" is more than just a join between Company and Contact. Had Steve called this table "employee" then it might be less confusing. Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 7345 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now