Is this acceptable practice for a many to many?

As a newbie in database design, I have a question about many-to-many relationship practice.

This question is actually a follow up question of this question, but I found it too big to explain in a comment.

The design should be like this(picture below):

• A person can have multiple phone numbers

• A club can have multiple phone numbers

• A phone number can only belong to either 1 club or either 1 person

As I understand it, this isn't exactly a many to many relationship but a many to one.

However, 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?

Personally, I thought it was better to work with an extra table contact and have person and club link to this table and then have phone numbers link to this table also.

But,...

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 - email?

I hope my question is a bit clear considering its actually a follow up.

-edit-

My schema seems to be incorrect for what my intentions are.

• a person/club can only have one contact

• a contact can only belong to one person/club

• a phone number can only belong to one contact

• an email address can only belong to one contact

• a contact can have multiple phone numbers

• a contact can have multiple email addresses

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 (or Club) and TelephoneNumber, and between Person (or Club) and EmailAddress.

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 and Email are both types of ContactMeans.

• A particular type of ContactMeans must be fixed for a Person (or a Club).

• Once a specific type of ContactMeans has been fixed for a Person (or a Club), this Person (or 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 Telephone or Email.

Proposed methods and answers to your individual questions

First method


Personally i thought it was better to work with an extra table contact and have person and club link to this table and then have phone numbers link to this table also.


But,...


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 Club or Person (the Party subtypes) with a given ContactMeans, Telephone or Email.

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

And then comes the second supertype-subtype cluster. In this case I would call your Contact table “ContactMeans” (the supertype of Telephone and Email) holding a PRIMARY KEY composed of two columns, PartyId (FOREIGN KEY referencing Party.PartyId) and ContactMeansNumber. Every ContactMeans must hold a type, so I would add a FOREIGN KEY named ContactMeansTypeCode pointing to ContactMeansType.ContactMeansTypeCode.

Then, you should establish a validation method in order to guarantee that any given occurrence of a Party (Club or Person) can only be reached through the ContactMeansType (Telephone or Email) that has being fixed for such occurrence. Involve the columns Party.ContactMeansTypeCode and 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 Party to ContactMeans, and then from ContactMeans to Telephone or Email. Said PRIMARY KEY has also migrated from Party to Club or Person (receiving the rolenames ClubId and PersonId, respectively), and then to ClubMember (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.

Second method


As i understand it, this isn't exactly a many to many relation but a many to one.


However, phone number can belong to 2 other tables. So, should i add 2 columns to phone numbers then? 1 with a FK to `per

StackExchange Database Administrators Q#104639, answer score: 4