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
TelephoneNumber, and between
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:
Email are both types of
- A particular type of
ContactMeans must be fixed for a
Person (or a
- Once a specific type of
ContactMeans has been fixed for a
Person (or a
Club) can be reached through one-to-many occurrences of such type of
ContactMeans, and you want to make sure that each one of these occurrences belong to the same type, either
Proposed methods and answers to your individual questions
Personally i thought it was better to work with an extra table
contact and have
club link to this table and then have
phone numbers link to this table also.
I find myself in the same situation when it comes to e-mail addresses. So, my idea was to also link them to that same
contact table. Was this a good step, or should i have added a separate table for the relationship between
club - email and
person - e mail?
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 particular
Party subtypes) with a given
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 called
ContactMeansType, with a PRIMARY KEY named
ContactMeansTypeCode. Then, in the
Party table, you should add a FOREIGN KEY pointing to
And then comes the second supertype-subtype cluster. In this case I would call your
Contact table “
ContactMeans” (the supertype of
Email) holding a PRIMARY KEY composed of two columns,
PartyId (FOREIGN KEY referencing
ContactMeans must hold a type, so I would add a FOREIGN KEY named
ContactMeansTypeCode pointing to
Then, you should establish a validation method in order to guarantee that any given occurrence of a
Person) can only be reached through the
Email) that has being fixed for such occurrence. Involve the columns
ContactMeans.ContactMeansTypeCode in this effort. For instance:
- Suppose that a particular
Party has been fixed to be reached only by means of
Telephone, then when there is an attempt to INSERT a new
ContactMeans, you must make sure that the value contained in this
ContactMeans.ContactMeansTypeCode is the same as the value contained in
Party.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 from
ContactMeans, and then from
Email. Said PRIMARY KEY has also migrated from
Person (receiving the rolenames
PersonId, respectively), and then to
PersonId being rolenamed as
MemberId). 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.
As i understand it, this isn't exactly a
many to many relation but a
many to one.
phone number can belong to 2 other tables. So, should i add 2 columns to phone numbers then? 1 with a FK to
person and 1 with a FK to
club? Leaving one of them
null when a phone number is entered?
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 a
Person or a
Club. But no, I do not recommend the addition to the
Telephone table of the two FOREIGN KEYS that make reference to
Club, nor I advise you leaving them
NULL 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”
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 column
Party.ContactMeansCode that makes to the PRIMARY KEY (
ContactMeans.ContactMeansCode) of a table named
ContactMeans. Or you could deal with this value by way of a BOOLEAN column called
As you can see in Figure 2, in this option
Email are structured with a many-to-one relationship with
Party, and then, through the latter, to
I included the column denominated
Email, which should be of use to store the fact that an individual
Email) has been established as the main instance (of its corresponding type) for contacting a particular
Party. 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
Person) can only be reached through (or contacted via) the
Email) that has being fixed for such instance. The column
Party.ContactMeansCode is useful in achieving this goal. For example:
- Say an individual
Party has been fixed to be contacted only via
by virtue of a specific value in the
Party.ContactMeansCode column), so you have to accept only INSERTS of new
Emails for such
Party and, of course, reject every INSERT attempt in the
Telephone table for this
Party. 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.