Article Preview

COLUMN

Column

Issue: 13.6 (November/December 2015)
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): 10,255
Starting Page Number: 71
Article Number: 13609
Related Link(s): None

Excerpt of article text...

For the next several columns we are going to cover some major design patterns in the data modeling world. Let me say up front that these are design patterns. They are not the only way to model a particular subject area nor are they suited for all situations. However it is worth taking the time to study them as they will help you to apply the patterns in other situations that you come across. After we review two or three of these patterns you will see that there is a kind of theme or set of structures and concepts that gets repeated over and over again. I am not going to point those out just yet, but I will as we get to the end of this series. The first pattern we are going to look at is called "Party Role."

I am not sure who first articulated this design pattern, but I can tell you that there are two excellent books that address this topic. The first is by David C. Hay and it is called Data Model Patterns: Conventions of Thought. The second is by Len Silverston and it is called The Data Model Resource Book Volume 1. Both of these books are excellent and will provide a good basis of understanding for database design patterns in general. Hay's book is very readable and the explanations are well done. Silverston's book covers a lot of different subject areas and he tries to cram as much as he can into the tightest space possible. As a result his book takes a little more effort to really understand, but the benefits of working through his book pay off handsomely. Both books can be found online at www.safaribooksonline.com and are free to read if you have a subscription. Otherwise I am sure you can find used copies very reasonably priced on Amazon.

Before we dig into the whole party role pattern we need to step back a minute and review one or two basic things when it comes to designing a database. First off, most tables in a database should represent either a single concept (PERSON, ADDRESS, CAR, ORDER, TRANSACTION, PRODUCT, etc...) or the intersection of two or more concepts (PERSON ADDRESS, AUTHOR BOOK, etc...). Secondly data modeling has a concept of sub-typing. Think of this as sub-classing an object. It is not a perfect analogy, but it should be close enough to communicate the basics of the idea. For example, in your logical model you may have an entity called DOCUMENT and your system may track or store several different kinds of documents, so you decide to sub-type them into LEGAL DOCUMENT and GENERAL DOCUMENT. The LEGAL DOCUMENTs may be further sub-typed into CONTRACT, BRIEF, and APPEAL. Your OTHER DOCUMENT may be sub-typed into MEMO, WHITEPAPER, and AGENDA. You would typically do this sort of thing if each type had enough distinguishing characteristics to keep them in separate database tables when you implement them. You would also separate them into their own tables when you implemented them if you knew that there were going to be a significant number of each kind of document, say a million or more of each. See Figure 1 for an example of what this would look like. Please note that the half circle with the "X" in it means that it is an exclusive sub-type meaning that the document can only be one of these things. As opposed to the inclusive sub-type (also a half circle but without the "X") which means that the object could participate in multiple sub-types.

The Party Role Model

The party role approach basically says that any given party can play "N" number of roles within a given system. As we look around our world and even our own lives we see that this is true. For me, I am a CUSTOMER at lots of places. For taxing purposes (U.S.), I am HEAD OF HOUSEHOLD. For an annual fishing tournament, I am a CONTESTANT. Some years back, I was a PUBLIC NOTARY. For my current employer, I am both EMPLOYEE and CONSULTANT. But I am still the same person residing at the same address with many of the same attributes. So the design would start with a PARTY and two sub-types: PERSON and ORGANIZATION. An ORGANIZATION is a collection of PEOPLE, but sometimes needs to be treated the same as a PERSON and sometimes as an ORGANIZATION (see Figure 2). The discriminator or the flag that tells you which a PARTY is would be the PERSON INDICATOR. True or yes means that the party is a Person and goes down the Person path. False or no means that the party is an Organization. For the remainder of this column, we are going to explore the Person path.

Person

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