We have a table of business-contacts with therefore many times the same companies.
OK, do I get it right: You enter the company data into fields on the individual person - and therefore you end up with the same company many times?
You are doing it the same way as FileMaker is doing in this table from the starter solutions. It is a very very bad idea.
The blue fields should definitely not have been in this table.
Have a look at this structure, the illustration below.
The field and key naming is not following our conventions, but FileMakers, but that's OK.
This way you will have the company once and as many contacts connected to each company as you want.
I wish I could count the times on one hand where I (mistakenly) put address information into a "Customer" (or other) table only to realize the "Customer" had multiple addresses!
You make is a great point.
You are right, in many cases addresses and other contract specific attributes could be put into a 3rd table, but here I wanted to keep the normalisation explanation rather simple.
Right. Understood. But, when I read your post it hit home. Good reinforcement.
thanks carsten, fmpdude. i fully understand your point. however, our starting point is people (business contacts=BC) and not companies. these BC are exported from LinkedIN - one file per (our) employee.
this export module is rudimentary and has no way of steering what you export, so everything is imported into a separate part of the system. every new import replaces all previous info. the connection to the rest of the system (with our data) is made via the email address.
in short, what i am talking about is how to make a best design from the (bad) starting point of having an end-result of one big file with business contacts, with many multiples.
our system already does not import "double people", but still, of course many BC still work at the same company and/or enterprise.