Article Preview

COLUMN

Column

Issue: 14.3 (May/June 2016)
Author: Craig Boyd
Author Bio: Craig Boyd is currently a data architect and senior consultant for a growing business intelligence consultancy. But in his 19 years of IT experience, he has been everything from a PC Technician, iSeries System Administrator, iSeries Programmer, Sr. Technical Lead, Data Modeler, Data Architect and Oracle DBA. He lives in the great state of Texas with his wife and two kids.
Article Description: No description available.
Article Length (in bytes): 8,802
Starting Page Number: 75
Article Number: 14310
Related Link(s): None

Excerpt of article text...

As a quick reminder we are talking about database design patterns. In particular the Party Role model. In the first article, we talked about the concept of Party and how it could be sub-typed into either Person or Organization. We then spent a bit of time talking about the concept of Person. In the second column, we continued the discussion by digging into Organization and Role. In the third installment, we discussed Location. In this fourth and final part, we are going to discuss contact methods.

I also need to make sure that everyone understands that the models we are walking through are very generic and therefore offer a great deal of flexibility, but because of that they can be complicated and technically more difficult to implement. Remember: the business and, to a lesser degree, the technical requirements should be the ultimate drivers for a logical data model. Is this the best way to model the concept of contact method? Maybe, depending on the requirements. Is this the only way? Absolutely not. This is a way to model these concepts. What you should be getting out of this is the pattern of the design. Take a concept and break it down to its most logical and smallest units and associate them or relate them in a way that makes sense.

As we walk through this portion of the model, please be sure to refer to Figure 1. There are two entities you should recognize from our previous columns: Party and Postal Address. Remember that a Party can represent either an Organization or a Person. In the case of the Contact Method, it does not matter which we are talking about.

Let's skip the Party Contact Method entity for now and talk about the Contact Method entity. This entity represents a single unique means by which a Party can be contacted. It doesn't mean that there is only one way to contact a Party, but it means that this particular way is unique. I know we have discussed this before, but by way of reminder, the half circle symbol with the "X" in it represents an exclusive sub-type. What this means is that any given record in Contact Method may be of a single type. That is, it can only represent a physical address or an electronic address or a form of telecommunication. It cannot represent multiple means of contacting the party. The entity Contact Method Type is the look up table for the sub-types. These codes explicitly restrict which kind of record a Contact Method can be.

A Party can have multiple Contact Methods and a Contact Method can be used by multiple parties, thus the Party Contact Method is the resolution to the many-to-many relationship between these two concepts. The Contact Type tells us what kind of Contact Method this is. You might be tempted to put this in the Contact Method entity, but that is not where it really belongs. The Contact Type is contextual. Let's walk through an example so you can see what I mean.

We have a Party that represents an Organization. This Organization has three kinds of physical addresses: Headquarters, Shipping, and Mailing. That means that it has three records in the Party Contact Method. However, only two of those addresses are unique: Headquarters (123 Main Street) and Shipping (456 Docks Blvd.) The Shipping entry represents a warehouse that is separate from the Headquarters which shares the same physical address with the Mailing address.

...End of Excerpt. Please purchase the magazine to read the full article.