Normalization

[Ocean West]

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

[Ugo DI LUCA]

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

[Ocean West]

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?

[CobaltSky]

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.

[Ugo DI LUCA]

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

[BobWeaver]

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.

[Ocean West]

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

[Kurt Knippel]

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

[Ocean West]

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

[CyborgSam]

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

[Ocean West]

I suppose you could put more fields in the "group" table to identify Group specific data.

[CyborgSam]

I think I'm missing something... Won't the Group table have multiple entries for a single "group"?

[Kurt Knippel]

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.