Some insights from having actually built a couple of apps with contacts and companies.
Firstly, you're missing several use-cases in your outline. Among the colorful ones I've run into over the years which you don't necessarily cover:
- Some organizations have sub-organizations, be them divisions, subsidiaries, you name it.
- Some people belong to several organizations. As in VP whatever of XYZ, chairman of ABC, and self-employed CEO/consultant -- each on a part time basis, hopefully.
- Some people can be contacts for an organization without actually belonging to it. E.g. when a high profile consultant is temporarily hired as a project lead, he could possibly become the primary contact for company XYZ without actually belonging to it.
- People can have sub-people too. And the latter can be in the same companies, or not. For instance, our VP of XYZ might prefer that you go through his main primary secretary over there, unless you contact him regarding ABC in which case that would be his assistant; or personally for consulting work -- a very busy person indeed, but there are people like that.
- Some organizations have no contacts associated with them. This can happen, for instance, in an app that outputs lists prospect companies (for sales people) in which contacts have yet to be identified.
- Conversely, contacts can have no organizations. For instance consumers.
Secondly, since your outline mentions customers, be wary that customers can be a company or a (physical) person. You seem to have that part covered based on your diagram, so let's move on.
If your next step is to add anything related to accounting, such as an "orders" table, be wary that anything accounting related is tied to a company, a contact, products, prices, etc. at a given point in time. These details can evolve in all sorts of colorful ways, and a frequent design mistake is to create the perfect normalized design and assume that you'll just update the tables as needed. Big no-no. If the taxman asks you to print out your invoices, and your IT says company XYZ at price P when accounting booked company ABC at price Q, you're very, very screwed. And don't get me started on archived/closed yearly accounts and reports that change due to similar design mistakes.
Thirdly, be very, very, very wary of UI/UX issues that you might introduce by normalizing too much. If it does not work like the apps that typical users have in their hands (read: Outlook), you'll be a poorly trained secretary away from changing the company of every employee at XYZ to ABC when Joe gets a new job. I've actually seen this happen and it was not a pretty sight. (She kept her job, in case you're wondering.)
Lastly, don't get me started on merging the inevitable duplicate companies, contacts, and so many other colorful things that couldn't possible turn up. Keep those in mind and make sure that your schema is very, very forgiving because it will happen.
Now... in practice...
Personally, I've come to actually turn a blind eye on normalization in this case. Contacts/Companies is one of those cases where DB design and beautifully normalized data as taught in school is a recipe for trouble in the form of resource hogging, over-complicated queries and ludicrously complex UI. It is, imho anyway, not the right thing to do.
Start by creating a contacts table, complete with fields such as first and last name, display_name if needed, company_name, address, phone, cell_phone, email, email2, secretary_name, secretary_phone, etc. If it's available when you create a contact in Outlook, it probably belongs in there.
You'll note that I didn't mention a companies table. That's because you usually don't want any strong ties between your contacts table and your companies table. If you need one, add one, and add a copmany_id in addition to the company_name. But then, make it a foreign key on delete set null. And be sure to keep it very, very loosely tied to company_name at the database level. Maintain it at the front-end level -- not anywhere else. This will keep a secretary from inadvertently changing the company of multiple contacts.
Keep things sane. Anything that might reasonably show up in a list (i.e. select * from contacts where ... limit 10), be queried against, or be useful frequently ought to be in the table. No joins, nada. Query, iterate through the result, done.
If you've really want additional garbage in there, you've two options.
One is to create an extra_contact_details table. It can be an EAV table, or the full load of company_name, address, phone, etc. fields, or a hogwash of normalized bits and pieces. Whichever option you take, don't over do it. Either will create additional (potentially complex) queries, and heaps of thorny programming issues down the road when you create the UI. What's absolutely key here is, if you go this route, a secretary who has always worked with Outlook needs to make sense of it.
The other, which I'll actually recommend upfront, is to add a text field called "extra_contact_details" in the contacts table and begone with it. No normalization at all. One set of primary details. Frequently used secondary details. Anything extra as plain text. Secretary gets it. End users get it. Done.
Lastly, if you need to store a version of any data at a given point in time, be sure to duplicate the value of any key details -- read: whatever you need to print it exactly as it was when it got inserted.
Best Answer
I notice that your specifications have turned a bit intricate (intentionally, right?), and I consider that this is a beneficial fact since this kind of scenarios will help you broaden your perspective on relational design.
Regarding such situation, I am going to suggest you two similar methods to deal with these new conditions. In the first one I propose the use of a (super)type-subtype cluster, and in the second one I recommend two one-to-many relationships. This way you would be addressing the relationships that you specify between the entities
Person
(orClub
) andTelephoneNumber
, and betweenPerson
(orClub
) andEmailAddress
.My understanding of your business rules
I will describe my suggestions within the context of your individual questions but, before I do that, you first need to know the way I understand your scenario, and I understand it as follows:
Telephone
andEmail
are both types ofContactMeans
.ContactMeans
must be fixed for aPerson
(or aClub
).ContactMeans
has been fixed for aPerson
(or aClub
), thisPerson
(orClub
) can be reached through one-to-many occurrences of such type ofContactMeans
, and you want to make sure that each one of these occurrences belong to the same type, eitherTelephone
orEmail
.Proposed methods and answers to your individual questions
First method
Yes, adding a separate table is a good step and, to me, the best method, but it needs some refinements. Here is where my first suggestion comes into play, you should make use of two supertype-subtype clusters.
In the first case, I would include a table called
Party
(which has been mentioned in my other answer). This table will serve the purpose of relating a particularClub
orPerson
(theParty
subtypes) with a givenContactMeans
,Telephone
orEmail
.You need to store the fact that a specific
Party
is fixed with only one type of means of contact, so I recommend you including a table calledContactMeansType
, with a PRIMARY KEY namedContactMeansTypeCode
. Then, in theParty
table, you should add a FOREIGN KEY pointing toContactMeansType.ContactMeansTypeCode
.And then comes the second supertype-subtype cluster. In this case I would call your
Contact
table “ContactMeans
” (the supertype ofTelephone
andEmail
) holding a PRIMARY KEY composed of two columns,PartyId
(FOREIGN KEY referencingParty.PartyId
) andContactMeansNumber
. EveryContactMeans
must hold a type, so I would add a FOREIGN KEY namedContactMeansTypeCode
pointing toContactMeansType.ContactMeansTypeCode
.Then, you should establish a validation method in order to guarantee that any given occurrence of a
Party
(Club
orPerson
) can only be reached through theContactMeansType
(Telephone
orEmail
) that has being fixed for such occurrence. Involve the columnsParty.ContactMeansTypeCode
andContactMeans.ContactMeansTypeCode
in this effort. For instance:Party
has been fixed to be reached only by means ofTelephone
, then when there is an attempt to INSERT a newContactMeans
, you must make sure that the value contained in thisContactMeans.ContactMeansTypeCode
is the same as the value contained inParty.ContactMeansTypeCode
, if it is so, then let the INSERT go on, otherwise deny said INSERT attempt. You are using MySQL, so this content about TRIGGERS from the MySQL Reference Manual may be relevant in this purpose.See Figure 1, which shows an IDEF1X data model depicting a logical structure for this method.
* Do not overlooke the vast power that relational keys have to offer, since they are being used to shape most of your business rules.
In this sense, e.g., it is worth noting how the PRIMARY KEY called
PartyId
has migrated fromParty
toContactMeans
, and then fromContactMeans
toTelephone
orEmail
. Said PRIMARY KEY has also migrated fromParty
toClub
orPerson
(receiving the rolenamesClubId
andPersonId
, respectively), and then toClubMember
(PersonId
being rolenamed asMemberId
). Thereby, all this “chain” of tables is provided with referential integrity.In this external document in .PDF format I give a more detailed treatment to this method.
Second method
Yes, as per the business rules that you have presented, this situation could be handled through a many-to-one (or one-to-many) relationship.
And yes, one given
Telephone
can belong to aPerson
or aClub
. But no, I do not recommend the addition to theTelephone
table of the two FOREIGN KEYS that make reference toPerson
andClub
, nor I advise you leaving themNULL
because, according to my personal experience, this may be a sign that the modeling stage needs to be extended.Instead of the above, I propose a second method in which, again, you would be using the supertype table called
Party
. This table will “link”Club
orPerson
withTelephone
orEmail
.It is necessary to add a column that represents the fact that a given
Party
has been fixed with a specific type of means of contact, so I would add the columnParty.ContactMeansCode
that makes to the PRIMARY KEY (ContactMeans.ContactMeansCode
) of a table namedContactMeans
. Or you could deal with this value by way of a BOOLEAN column calledParty.IsFixedWithEmail
orParty.IsFixedWithTelephone
, also.As you can see in Figure 2, in this option
Telephone
andEmail
are structured with a many-to-one relationship withParty
, and then, through the latter, toClub
orPerson
.I included the column denominated
IsPrimary
toTelephone
andEmail
, which should be of use to store the fact that an individualTelephone
(orEmail
) has been established as the main instance (of its corresponding type) for contacting a particularParty
. Regarding this aspect you must create a method to ensure that there could be only one instance set as primary.Then, you need to define a procedure to validate that any particular instance of a
Party
(Club
orPerson
) can only be reached through (or contacted via) theContactMeans
(Telephone
orEmail
) that has being fixed for such instance. The columnParty.ContactMeansCode
is useful in achieving this goal. For example:Party
has been fixed to be contacted only viaEmail
( by virtue of a specific value in theParty.ContactMeansCode
column), so you have to accept only INSERTS of newEmails
for suchParty
and, of course, reject every INSERT attempt in theTelephone
table for thisParty
. Again, since you are using MySQL, you may have to do it via TRIGGERS.See an IDEF1X data model illustrating a logical structure for this procedure in Figure 2.
In the same external document in .PDF format I give a more detailed treatment to this second method, as well.