CHAPTER
2
People and Organizations
The most frequent business information need is to ask questions about people and organizations and to be able to rely on accurate answers to these questions. For instance:
- What are the attributes or characteristics of the people and organizations that are involved in the course of conducting business?
- What relationships exist between various people, between various organizations, and between people and organizations?
- What are the addresses, phone numbers, and other contact mechanisms of people and organizations, and how can they be contacted?
- What types of communication or contacts have occurred between various parties, and what is necessary to effectively follow up on these communications?
Almost all business applications track information about people and organizations, recording information about customers, suppliers, subsidiaries, departments, employees, and contractors redundantly in many different systems. For this reason, it is very difficult to keep key information such as client contact data consistent and accurate. Examples of applications that store information about people and organizations include sales, marketing, purchasing, order entry, invoicing, project management, and accounting.
The data model within this chapter can be used for most organizations and applications. Subsequent chapters use this data model as a basis on which to add more detail. This chapter includes data models on the following:
- Organization
- Person (alternate model also provided)
- Party (organizations or people)
- Party roles (i.e., customers, suppliers, internal organizations)
- Specific party relationships (i.e., customer relationship, supplier relationship, employment)
- Common party relationships
- Party relationship information
- Postal address information (postal addresses and geographic boundaries)
- Party contact mechanism—telecommunications numbers and electronic addresses
- Party contact mechanism (expanded)
- Facility versus contact mechanism
- Party communication event (i.e., phone calls, support calls, meetings)
- Communication event follow-up
Most data models maintain organizational information in various entities that are portrayed as completely separate entities. For instance, there may be a customer entity, a vendor entity, and a department entity. Each application within an enterprise has its own needs; therefore, the data modeler will often base the model on the needs of a particular application. For example, when building an order entry application, the customer information is crucial; therefore, the data modeler shows a separate entity for customer. Likewise, the supplier information is critical when building a purchasing application; hence, there is normally a supplier entity. For a human resources system, the data modeler might show an entity called a department within which the employees work.
The problem is that an organization may play many roles, depending on the particular circumstance. For instance, in larger companies, internal organizations sell to each other. The property management division may be a supplier to the product sales division. The property management division may also be a customer of the product sales division. In this case, there would normally be both a customer and supplier record, with redundant data, for each of these divisions. Not only could there be a customer and a supplier record, but there could also be many additional records for the organization depending on how many roles the organization plays within the enterprise.
When an organization's information changes—such as a change in address—the information might be updated in only one of the many systems where organization information is stored. This, of course, results in inconsistent information within the enterprise. It may also result in major frustration on the part of managers, customers, suppliers, and anyone who might want to generate a correct mailing list!
The solution to this redundancy problem is to model an entity called ORGANIZATION that stores information about a group of people with a common purpose such as a corporation, department, division, government agency, or nonprofit organization. Basic organizational information, such as its name and federal tax ID num (for legal entities), is stored once within this entity, reducing redundancy of information and eliminating possible update discrepancies.
Figure 2.1 shows the data model for organization information. An organization is defined as a group of individuals that, together, have an informal or formal association. An ORGANIZATION may be a LEGAL ORGANIZATION, such as a CORPORATION or GOVERNMENT AGENCY, or an INFORMAL ORGANIZATION, such as a FAMILY, TEAM, or OTHER INFORMAL ORGANIZATION. Both legal and informal organizations may share many relationships because they may both be assigned to various roles and responsibilities and may be managed by people. While they share many things in common, they also have differences. For instance, a legal organization is the only type of organization that may be a party to a contract.
Figure 2.1 Organization.
This model reduces redundancy because the organization information is stored only once, as opposed to storing this information redundantly in a customer entity, a supplier entity, a department entity, or any other entity storing organization information.
Table 2.1 gives examples of data in the ORGANIZATION entity. ABC Corporation and ABC Subsidiary are examples of legal organizations that happen to be internal organizations of the enterprise being modeled. “Accounting Division,” “Information Systems Department,” and “Customer Service Division” are informal organizations and internal to the enterprise. “Fantastic Supplies,” “Hughs Cargo,” and “Sellers Assistance Corporation” are legal corporations that represent companies with whom the enterprise engages in business. The “Smith family” is an organization because it represents a group of individuals that are associated by family, and it may be useful for recording demographic information. The “Government Quality Commission” is a government agency that is involved in monitoring the operations of the enterprise.
Table 2.1 Organizations
| ORGANIZATION ID | ORGANIZATION SUBTYPE | NAME |
| 100 | Legal organization | ABC Corporation |
| 200 | Legal organization | ABC Subsidiary |
| 300 | Informal organization | Accounting Division |
| 400 | Informal organization | Information Systems Department |
| 500 | Informal organization | Customer Service Division |
| 600 | Informal organization | Customer Support Team |
| 700 | Legal organization | ACME Corporation |
| 800 | Legal organization | Fantastic Supplies |
| 900 | Legal organization | Hughs Cargo |
| 1000 | Legal organization | Sellers Assistance Corporation |
| 1100 | Informal organization | Smith Family |
| 1200 | Legal organization | Government Quality Commission |
For the remainder of this book, the term “enterprise” will be used to refer to all the internal organizations for whom the data model is being developed. For instance, each enterprise will have its own specific needs and business rules that will determine how the enterprise will customize these models for its own use.
Just as most data models show separate entities for various types of organizations, they also show separate entities for various types of people such as employees, contractors, supplier contacts, and customer contacts. The problem with keeping this information in separate entities is that people may also have different jobs and roles that change over time. Most systems will record redundant information about a person because they store a record each time the person's role changes.
For example, John Smith was a good customer of ABC Corporation. John then decided to perform contract labor for ABC Corporation. The people at ABC Corporation liked his work so much that they then hired him as an employee. For most systems, there would be a separate record for John Smith as a customer contact, then as a contractor, then as an employee. Much of John Smith's information has remained the same, such as his name, gender, birth date, skills, and other demographics. Because John Smith's information is stored in several locations, many systems would have trouble keeping his information accurate and consistent.
Another problem is that the same person may have many different roles at the same time. For instance, ABC Corporation is a large company with many divisions. Shirley Jones is an employee and manager of the transportation division. She is also considered a customer of the supplies division. At the same time, she is the supplier for the publishing division, which needs her services to transport catalogues. Therefore, Shirley is an employee of one division, a customer contact of another division, and a supplier contact of yet another division. Rather than have three separate records for Shirley with redundant information, there should be only one record.
To address this issue, Figure 2.2a shows a PERSON entity that stores a particular person's information, independent of his or her jobs or roles. Attributes of the PERSON entity may include current last name, current first name, current middle initial, gender, birth date, height, weight, and many other attributes that are listed in Figure 2.2a and describe the person.
Figure 2.2a Person.
Table 2.2 shows some example data for the PERSON entity. The table shows key information about John Smith, Judy Smith, Nancy Barry, Marc Martinez, William Jones, Shirley Jones, Barry Cunningham, and Harry Johnson. This model helps reduce redundancy because the person's base information is maintained only once, even though the person may play many different roles. The “Party Roles” section later in this chapter will describe how to model the various roles each person and organization can play and the “Party Relationship” section will show how to model the interrelationships between party roles.
Table 2.2 Person Data
Some of these attributes in the PERSON entity may be repeating attributes and may need to be separated into their own entity, depending on whether the enterprise has the will and means to store many instances of that attribute.
Figure 2.2b shows an alternate model for PERSON with the repeated attributes separated into their own entities. For instance, the MARITAL STATUS entity allows the maintenance of the history of marital changes, and the MARITAL STATUS TYPE entity could store instances such as “single,” “married,” “divorced,” and “widowed.”
To reiterate the diagramming convention from Chapter 1, a tilde (“~”) across the relationship line indicates that the inherited foreign key is part of the primary key of the child entity. For instance, the tildes (“~”) across the relationship lines in the top left corner of Figure 2.2b indicate that the party id and marital status type id are part of the MARITAL STATUS entity primary key. This convention allows a shorthand notation, providing for the primary key to be identified as a combination of the primary key attributes (identified with a “#”) as well as the primary keys of the entity to which the relationship with a tilde is referencing.
The PHYSICAL CHARACTERISTICs entity provides a means to store the history of a person's physical characteristics such as height and weight. This history is also useful in health-related fields. The details of each characteristic is stored in the PHYSICAL CHARACTERISTIC TYPE entity, which could have values of ”height,” ”weight,” ”blood pressure,” and so on. The value attribute in the PHYSICAL CHARACTERISTIC maintains the characteristic's measurement such as a height of 6 “#” and is an alphanumeric attribute to accommodate different characteristics.
A person may have many PERSON NAMEs either at the same time (name aliases) or over time as his or her name changes. This may be important information in many applications. An example of this is in a prison or correctional facility where the enterprise would want to maintain a history of names and aliases. In these cases, the PERSON NAME entity can be used to store all the names and aliases. The current last name, current first name, current middle initial, current prefix, current suffix, and current nickname attributes are now maintained in the PERSON NAME and PERSON NAME TYPE entities. The PERSON NAME entity stores the name and the time period during which it is valid, and the PERSON NAME TYPE entity maintains the type of name it is in the description attribute. For instance, the PERSON NAME TYPE description may be “first” or “last.” This data model provides much more flexibility because many names can now be stored. Some cultures have more than one middle or last name, and this structure handles diverse needs. Because the current name has great applicability in many circumstances, the current last name, current first name, current middle name, current personal title (i.e., Mr., Mrs., Dr., Ms.), current suffix (i.e., Jr., Senior, III) and any nicknames or aliases could be stored in the PERSON entity as in the previous diagram, and the PERSON NAME could be used to store a history of names.
Figure 2.2b Person—alternate model.
Also shown in Figure 2.2b are entities to show the CITIZENSHIP and PASSPORTs that a person has had instead of the simple current passport number and passport expiration date attributes in the first model. This could be useful in travel applications.
The GENDER TYPE entity stores common descriptions for gender classifications and may contain instances such as ”male,” ”female,” “male to female,” “female to male,” and “not provided.” If a history is needed, for instance, in specialized medical enterprises, there could be an associative entity of PERSON GENDER that may be between PERSON and GENDER TYPE.
As the model illustrates, a great deal of demographic information is maintained about people and organizations. By maintaining information about people and organizations once in a single place, the enterprise can capture much more consistent data and be able to apply this information in many contexts.
Organizations and people are similar in many respects. They both have common characteristics that describe them, such as their credit rating, address, phone number, fax number, or e-mail address. Organizations and people can also serve in similar roles as parties to contracts, buyers, sellers, responsible parties, or members of other organizations. For example, membership organizations (like a computer users group) may keep similar information on their corporate members and their individual members. Contracts can usually specify an organization or a person as a contracted party. The customer for a sales order may be either an organization or a person.
If person and organization were modeled as completely separate entities, the data model would be more complex. Each contract, sales order, membership, or transaction that involved either a person or an organization would need two relationships: one to the person entity and one to the organization entity. Furthermore, these relationships are mutually exclusive and thus would require an exclusive arc (see Chapter 1 for a discussion on exclusive arcs). For instance, a sales order could be placed by a person or an organization, but a single sales order cannot be placed by both a person and an organization at the same time.
Therefore, Figure 2.3 shows a superentity named PARTY that has as its two subtypes PERSON and ORGANIZATION. This PARTY entity will enable storage of some of the common characteristics and relationships that people and organizations share.
Figure 2.3 Party.
Parties are classified into various categories using the entity PARTY CLASSIFICATION, which stores each category into which parties may belong. There are subtypes for ORGANIZATION CLASSIFICATION such as INDUSTRY CLASSIFICATION, SIZE CLASSIFICATION, and MINORITY CLASSIFICATION as well as subtypes to categorize people such as EEOC (Equal Employment Opportunity Commission) CLASSIFICATION and INCOME CLASSIFICATION. The ORGANIZATION CLASSIFICATION and PERSON CLASSIFICATION could be related to ORGANIZATION and PERSON, respectively, if one wanted to model them more specifically. For simplicity purposes, however, this model shows them as subtypes of PARTY CLASSIFICATION, which is related to PARTY. These represent only a few possible types for illustration purposes, and other possible values for categories are maintained in the PARTY TYPE entity.
Examples for MINORITY CLASSIFICATION include “minority-owned business,” “8A business,” or “woman-owned business.” INDUSTRY CLASSIFICATION may include “telecommunications,” “government institute,” or “manufacturer.” SIZE CLASSIFICATION may be “small,” “medium,” ”large,” and “national account” and may also be defined by a range of number of employees. For people, the EEOC CLASSIFICATION instances may include values such as “african american,” “native american,” “asian or pacific islander,” “hispanic,” and “white non-hispanic.” The instance “women” is another EEOC classification; however, it is covered using the gender attribute within the PERSON entity. INCOME CLASSIFICATIONs may include values to indicate yearly income such as “less than $20,000,” “$20,001 to 50,000,” “$50,001 to 250,000,” and “over $250,000.”
These categorizations of parties can be used to determine if there are any special business considerations for parties, special pricing arrangements, or special terms based on the type of party. It is also a mechanism for classifying businesses into types of industries for market segmentation and to target marketing efforts. A from date and thru date are included so history can be tracked because it is possible for the definition to change over time [e.g., businesses may “graduate” from the 8A (minority startup) program].
Table 2.3 shows several party occurrences that are merely consolidations from the person and organization examples. This single entity allows the data models to refer to either a person or an organization as a party to a transaction. The table shows our previous examples of organizations and people along with the PARTY TYPEs that serve to classify them according to various demographic categories. Organizations and people may be classified several ways, thus the need for the many-to-many relationship between PARTY and PARTY TYPE. “ABC Corporation” is classified as a “minority owned business” and a “manufacturer.” “ACME Corporation” is classified as a “woman-owned business,” “mail order firm,” and a “large organization.” People may be classified into various categories such as EEOC CLASSIFICATION types as well as other personal classifications such as INCOME CLASSIFICATION. Table 2.3 shows that Marc Martinez is classified as a “Hispanic” with an income classification of “$50,000−250,000” and that William Jones is classified as an “African American.” Many of the people do not have classifications, which illustrates that this information is totally optionally and may not be available from many of the parties.
Table 2.3 Party Data
| PARTY ID | PARTY NAME (ORGANIZATION NAME OR FIRST NAME/LAST NAME) | PARTY TYPE |
| 100 | ABC Corporation | Minority-owned business Manufacturer |
| 200 | ABC Subsidiary | Minority-owned business Manufacturer |
| 300 | Accounting Division | |
| 400 | Information Systems Department | |
| 500 | Customer Service Division | |
| 600 | Customer support team | |
| 700 | ACME Corporation | Woman-owned business Mail order firm Large organization |
| 800 | Fantastic Supplies | Janitorial service organization Small organization |
| 900 | Hughs Cargo | 8A organization Service organization Small organization |
| 1000 | Sellers Assistance Corporation | Marketing service provider Medium-sized organization |
| 1100 | Smith Family | |
| 5000 | John Smith | |
| 5100 | Judy Smith | |
| 5200 | Nancy Barry | |
| 5300 | Marc Martinez | Hispanic Income classification $50,000−250,000 |
| 5400 | William Jones | African American |
| 5500 | Shirley Jones | |
| 5600 | Barry Cunningham | |
| 5700 | Harry Johnson | |
| 5800 | Barry Goldstein | |
| 5900 | Joe Schmoe | |
| 6000 | Jerry Red |
As noted previously, a person or organization may play any number of roles such as a customer, supplier, employee, or internal organization. Because the same PARTY may play many roles over time or at the same time, the need to define the information about each role arises. The PARTY entity defines the nature of the party, which will not change over time. The PARTY TYPE classifies the party into certain categories. The PARTY ROLE entity defines how a party acts or, in other words, what roles the party plays in the context of the enterprise's environment. There may be certain information related to a party that applies only to a specific role. For instance, credit information may be applicable only to customers and thus be a relationship or attribute of this role only. Specific relationships may be applicable only to certain roles. For instance, the relationship from the entity EMPLOYMENT with a “from date” and “start date” is specifically related to a role of employee.
Are these roles just subtypes of the party entity, or is there a PARTY ROLE entity to indicate that each PARTY may act in one or more PARTY ROLEs? For example, are the entities CUSTOMER and SUPPLIER subtypes of the PARTY entity, or should they be subtypes of a PARTY ROLE entity to show that the same PARTY can be both a CUSTOMER and a SUPPLIER? One can argue the data model either way (which has happened innumerable times during the course of this writing).
With either of these approaches, it is important to establish that these roles such as CUSTOMER, SUPPLIER, EMPLOYEE, and INTERNAL ORGANIZATION are entities that need to be tracked in addition to the PARTY entity. The PARTY entity allows the enterprise to track consistent information about the person or organization once. The PARTY ROLE allows the enterprise to maintain information (attributes or relationships) about each party within the context of their specific roles. For example, a certain party may have various contact information (home address, office address, home phone, cell phone, and so on) regardless of how many roles he or she may play within the enterprise (the party may be a customer, a supplier, and an agent). For the party's role as a customer, and only for that role, it may be important to store credit rating information.
Figure 2.4 provides a data model to illustrate how to model specific roles within an enterprise. It contains many common roles that are widely applicable to many enterprises. The Party Role id primary key attribute allows the same party to play the same role at different points in time. Roles are subtyped into PERSON ROLEs, ORGANIZATION ROLEs, and roles that may be either. PERSON ROLEs include EMPLOYEE for legal employees of the enterprise, CONTRACTOR for a person who is or has performed a contract with the enterprise, FAMILY MEMBER to indicate that this person is part of a biological family, and CONTACT to indicate someone who is acting as a representative with an organization (this may be a sales contact, support contact, customer contact, supplier contact, or any other type of representative). .
Figure 2.4 Party roles.
Common roles that may be for either people or organizations are CUSTOMER, which is a party that has purchased (BILL TO CUSTOMER), been shipped (SHIP TO CUSTOMER), or used (END USER CUSTOMER) products (either goods or services) from the enterprise. A SHAREHOLDER may also be either a person or an organization and is therefore not a person role or organization role. The same applies to a PROSPECT, which is a person or organization that the enterprise thinks will purchase, be shipped, or use their products.
The ORGANIZATION roles include DISTRIBUTION CHANNEL (which typically is an AGENT or a DISTRIBUTOR), COMPETITOR, PARTNER, REGULATORY AGENCY, HOUSEHOLD, ASSOCIATION, SUPPLIER, various ORGANIZATION UNITs such as a PARENT ORGANIZATION, SUBSIDIARY, DEPARTMENT, DIVISION, and OTHER ORGANIZATION UNIT as well as INTERNAL ORGANIZATION, which indicates if the organization is part of the enterprise or is an external organization.
A DISTRIBUTION CHANNEL is an organization that markets the enterprise's products. An AGENT markets these products without buying or carrying goods while a DISTRIBUTOR generally markets the goods by first buying them and then selling them. A COMPETITOR carries similar products and is tracking performance for comparative analysis. A PARTNER is an organization that is identified as an ally and with whom mutually beneficially relationships are established. A REGULATORY AGENCY is an organization that regulates or governs the activities of the enterprise. A HOUSEHOLD is an informal organization of people that live within the same residence and is typically a family. This information is helpful to establish customer demographics for personal products. An ASSOCIATION is an organization that provides services such as networking and sharing of information within particular fields of interest or industries. A SUPPLIER is an enterprise that may or does provide products (goods and/or services) to the enterprise. An ORGANIZATION UNIT identifies the form of this organization and is useful to identify parts of organizations as well as maintenance of organizational structures. This role may be further subtyped as a PARENT ORGANIZATION, SUBSIDIARY, DEPARTMENT, DIVISION, or OTHER ORGANIZATION UNIT to cover more unique types of organizations that are specific to the enterprise. A PARENT ORGANIZATION is a role whereby this enterprise encompasses other enterprises. A SUBSIDIARY organization is a role whereby this organization is encompassed by another enterprise and is partially or wholly owned by the parent organization. A DIVISION is a portion of an organization dedicated to a specific purpose within the enterprise. A DEPARTMENT is also a portion of an organization dedicated to a more specific purpose within the enterprise and is sometimes within a division of the enterprise. An INTERNAL ORGANIZATION is an organization that is part of the enterprise for whom the data model is developed.
These subtypes are intended in order to provide a list of common subtypes used in most enterprises, and it should be understood that each enterprise may modify this list of subtypes to suit its own specific needs. Therefore, each PARTY ROLE may be described by one and only one PARTY ROLE TYPE. The PARTY ROLE TYPE is a subtype of ROLE TYPE that has a description attribute that stores available values of role types. Roles may be defined declaratively such as this person is a “prospect” or roles may be associated with specific transactions such as orders, agreements, requirements, and so on. Therefore, this book will use the ROLE TYPE entity to show additional role types showing how parties are involved in the enterprise. Other ROLE TYPE subtypes that will be discussed later in this book will be an ORDER ROLE TYPE, AGREEMENT ROLE TYPE, REQUIREMENT ROLE TYPE, and specific role types for many other types of transactions.
Examples of PARTY ROLE TYPEs include all the subtypes previously mentioned such as “employee,” “contact,” “customer,” “supplier,” “internal organization,” and so on, plus more specific role types such as “placing customer,” “bill to customer,” “installation customer,” “customer contact,” “supplier contact,” and any other roles not specified in the PARTY ROLE subtypes. As a reminder, for illustrative purposes, the notation of this book shows subtypes within an entity such as PARTY ROLE and then also shows a relationship to a “TYPE” entity to cover all the other possible types.
Some PARTY ROLEs are dependent on the context of another party in order to fully define them, and some roles can stand on their own. For instance, a PARENT ORGANIZATION role is useful to identify companies that own other companies. This role is usually dependent on the other organization that is the SUBSIDIARY ORGANIZATION. These roles are therefore dependent on having a PARTY RELATIONSHIP instance, which will be defined in the next section. PARTY ROLES such as “notary” or “doctor” can stand on their own, and parties may have these roles, even without an associated PARTY RELATIONSHIP.
Each PARTY ROLE may be valid for certain time frames, and therefore the attributes from date and thru date are part of the PARTY ROLE. These attributes are optional because many of the time frames for the roles will be dependent on (and can be derived from) the PARTY RELATIONSHIP entity, which will be discussed in the next section. These attributes are particularly useful for relationship-independent roles such as “notary” or “doctor.”
Should Roles Be Defined at the Time of the Transaction?
One may make the point that the enterprise doesn't really know the role of the party until certain transactions take place and therefore this role information is derived and unneeded. For instance, if CUSTOMER is defined as a party that has purchased, been shipped, or used products, the ORDER, INVOICE, DEPLOYMENT USAGE (from Volume 2) or SHIPMENT entities will dictate who is a customer; this information will be available from relationships between these entities and the PARTY entity. As a practical matter, it is important to be able to declaratively state the role of certain parties. The enterprise may declaratively state that “XYZ company” is a prospect, although there aren't any transactions that the enterprise maintains about the event of becoming a prospect. Similarly, the enterprise may want to declaratively state a certain party is a customer even though there aren't any associated transactions. Additionally, even though this is a technical consideration, the enterprise, as a practical matter, would want to know who was a customer, supplier, employee, and so on without having to search for the related transactions. The relationship-independent roles such as “notary” and “doctor” need to be declaratively stated without necessarily being related to transactions that the enterprise is interested in storing.
Another point about the argument of these roles being defined when the transactions occur is that, in some circumstances, the enterprise could instantiate these roles at the time of the transactions. The roles do not necessarily have to be instantiated before any transactions occur. For instance, when a party places an order, the party could then be set up as an instance of a PARTY ROLE of a CUSTOMER (and the enterprise may specifically have them in several customer roles such as BILL TO CUSTOMER, SHIP TO CUSTOMER, END USER CUSTOMER) and then the ORDER could be related to these instances.
Table 2.4 illustrates examples of possible roles. Notice that most parties will have at least one role because they are maintained for some reason, and they often have more than one role. It is possible for a party to not have even a single role, for instance, in maintaining census data for people (although one could argue that the role is “census participant”).
Table 2.4 Party Role Data
| PARTY ID | PARTY | PARTY ROLE |
| 100 | ABC Corporation | Internal organization |
| Parent organization | ||
| 200 | ABC Subsidiary | Internal organization |
| Subsidiary organization | ||
| 300 | Accounting Division | Internal organization Division |
| 400 | Information Systems Department | Internal organization Department |
| 500 | Customer Service Division | Internal organization Department |
| 600 | Customer support team | Internal organization Team |
| 700 | ACME Corporation | Customer Supplier |
| 800 | Fantastic Supplies | Supplier Prospect |
| 900 | Hughs Cargo | Supplier |
| 1000 | Sellers Assistance Corporation | Agent |
| 1100 | Smith Family | Prospect |
| 5000 | John Smith | Employee |
| Supplier coordinator | ||
| Parent | ||
| Team leader | ||
| Mentor | ||
| 5100 | Judy Smith | Child |
| 5200 | Nancy Barry | Supplier service contact |
| 5300 | Marc Martinez | Customer contact |
| 5400 | William Jones | Employee |
| Account manager | ||
| (internal sales representative) | ||
| 5500 | Shirley Jones | Project manager |
| Employee | ||
| QA representative | ||
| 5600 | Barry Cunningham | Contractor |
| 5700 | Harry Johnson | Notary |
| 5800 | Barry Goldstein | Employee |
| Apprentice | ||
| 5900 | Joe Schmoe | Customer contact |
| 6000 | Jerry Red | Employee |
| Customer service representative |
Role Types Throughout This Book
The previous section illustrated that parties may play many roles. Roles may be defined “declaratively” for a party without a transaction involved, such as the person is a “doctor.” Roles may also be defined for any type of transactions involved in the data model. There may be roles for orders, shipments, invoices, and any other types of transactions occurring in the data model.
All of these roles will have a standard structure and will be associated with a PARTY, be of a ROLE TYPE, and be associated with the transaction. For example, each ORDER may have many ORDER ROLEs of certain ORDER ROLES TYPEs (party taking order, party giving order, party paying for the order) associated with PARTYs. Each of these ROLE TYPEs (ORDER ROLE TYPE, PARTY ROLE TYPE, SHIPMENT ROLE TYPE, INVOICE ROLE TYPE, and so on) will be considered subtypes of ROLE TYPE and will therefore inherit the attributes of ROLE TYPE such as role type id and description. This subtype notation for role is shown in Figure 2.6 (a PARTY ROLE TYPE is a subtype of ROLE TYPE); however, the rest of the book will show only the specific role type, for example, ORDER ROLE TYPE without its supertype for simplification reasons.
As noted previously, a person or organization may play any number of roles such as a customer, supplier, employer, or subsidiary. Many roles that a party plays make sense only in relation to another party. If ACME Company is a customer, is it a customer of ABC Subsidiary or a customer of the parent company, ABC Corporation? Maybe it is a customer of the widgets division or the gadgets division.
Customer relationship management is a hot field in our industry. An amazing fact is that many customer relationship management systems fail to include an entity to track each relationship between parties. These systems will frequently have a “contact” entity (and table) to track information about parties (if they even implement the concept of parties). Then they will associate statuses, priorities and notes, and various dates about these contacts.
The problem is that a great deal of information such as statuses, priorities, notes, and certain dates are not related to a “contact”; they are related to a relationship between two parties. For example, picture three salespeople selling different product lines who all have a relationship to the same “contact” of Marc Martinez at ACME Company. Is it possible that each salesperson may want to assign his or her own status, priority, notes, and relationship start date? Each salesperson has a unique relationship to Marc and one salesperson who has sold a great deal to Marc may record a relationship status of “very active” while another salesperson records a status of “inactive” because they don't do much business for the time being. If a status attribute is related to just the contact (Marc), these sales representatives will probably override each other's information depending on their relationship and perspective. Each salesperson will also have conversations and want to record notes about their relationships, some of which may be private to their relationship. Of course, the enterprise would also want to be able to access the entire information about the “contact” but they should also want to maintain the unique information about each relationship. In other words, if relationships are so important, why not maintain information about each relationship?
Therefore, in addition to modeling the roles of the party, there is a need to model the relationship between parties. For example, there is a need to know not only that Marc Martinez is a customer contact, but to maintain the details of Marc's relationship with each of the sales representatives. Similarly, there is a need to know not only that ACME Company is a customer, but that ACME Company is a customer of ABC Subsidiary (as opposed to another internal organization of the enterprise).
A relationship is defined by the two parties and their respective roles. For example, Figure 2.5 shows CUSTOMER RELATIONSHIP, EMPLOYMENT, and ORGANIZATION ROLLUP as subtypes and examples of PARTY RELATIONSHIPs. The PARTY RELATIONSHIP entity allows parties to be related to other parties and maintains the respective roles in the relationship. The PARTY RELATIONSHIP entity has attributes of from date and thru date in order to show when the relationship started and optionally when (and if) it ended.
Figure 2.5 Specific party relationships.
The PARTY RELATIONSHIP entity shown in Figure 2.5 allows parties to be related to other parties and maintains the respective roles in the relationship.
While there are many subtypes of party relationships, a few subtypes are shown, namely, EMPLOYMENT, CUSTOMER RELATIONSHIP, and ORGANIZATION ROLLUP. For example, the PARTY RELATIONSHIP TYPE subtype of CUSTOMER RELATIONSHIP may be related to the PARTY ROLE subtype of CUSTOMER and a PARTY ROLE subtype of INTERNAL ORGANIZATION. The PARTY RELATIONSHIP entity has attributes of from date and thru date in order to show the valid time frames of the relationship. For the CUSTOMER RELATIONSHIP, this would indicate when the party formed a customer relationship and when (and if) it ended.
The CUSTOMER RELATIONSHIP subtype shows that a CUSTOMER may be involved as a customer in several INTERNAL ORGANZATIONS and vice versa—hence, this associative, many-to-many entity. If there was a need not only to store customers of the enterprise but also to track who is a customer of what organization (i.e., who are our partners' and competitors' customers) then CUSTOMER RELATIONSHIP subtype could be related to the CUSTOMER and SUPPLIER role subtypes instead of the CUSTOMER and INTERNAL ORGANIZATION roles. This would enable relating customers to any organization and showing who are the customers and suppliers of any organization.
The EMPLOYMENT subtype of PARTY RELATIONSHIP provides a means for relating people who are employees of each of the enterprise's internal organizations—hence, the relationships to EMPLOYEE and INTERNAL ORGANIZATION. This is also a many-to-many relationship because, over time, a person may be an employee of several internal organizations and an INTERNAL ORGANIZATION may have several EMPLOYEEs.
The ORGANIZATION ROLLUP associative entity shows that each ORGANIZATION UNIT may be within one or more ORGANIZATION UNITs, over time. One may argue that a DEPARTMENT is always within one and only one DIVISION; however, what if the organization structure changes over time and at first it was within one division and then it moved to another division? If there are known one-to-many relationships between two PARTY ROLES, the modeler can show the one-to-many relationship between these roles instead of using the PARTY RELATIONSHIP entity. Most relationships between two PARTY ROLES, though, tend to be of a many-to-many nature.
These examples illustrate that there is some commonality between various party relationships. Figure 2.6a shows many more examples of party relationships and generalizes the model instead of showing the specific relationships between each pair of roles. When customizing or applying this model, it is recommended to draw relationship lines for each specific PARTY RELATIONSHIP subtype to the two PARTY ROLEs to which it is related. This will clarify the nature of each relationship, and this is crucial because each of these relationships represents very important information. Figure 2.6a shows the general nature of these PARTY RELATIONSHIPs being related to and from PARTY ROLEs.
Figure 2.6a Common party relationships.
Figure 2.6a also shows the corresponding PARTY RELATIONSHIP TYPE and its relationship to PARTY ROLE TYPE entity. The PARTY RELATIONSHIP TYPE description attribute describes in more detail the meaning behind this type of relationship. An example is that a “customer relationship” (which would be the name value) has a description of “where the customer has purchased or used purchasing products from an internal organization.” (Substitute “supplier” for internal organization if a larger scope for customer is needed.).
Each relationship type is valid only for a specific role pair. For example the “customer relationship” party relationship type is only valid for the “customer”/ “internal organization” role pair. The application of the PARTY RELATIONSHIP TYPE of “customer relationship” would yield a PARTY RELATIONSHIP instance with PARTY ROLEs for the actual parties that play the roles of “customer” and “internal organization.” The name attribute in the PARTY RELATIONSHIP TYPE describes the nature of a specific relationship. For example, a “customer relationship” name may define the relationship between the “customer” PARTY ROLE and the “internal organization” PARTY ROLE identifying where this party is a customer to each internal organization.
The PARTY ROLE TYPE entity is a list of possible roles that can be played by the parties within a PARTY RELATIONSHIP TYPE. The two relationships from PARTY ROLE TYPE to PARTY RELATIONSHIP TYPE define the nature of the relationship. To form a “customer relationship” PARTY RELATIONSHIP TYPE, there would be two lines to the PARTY ROLE entity: one to the “customer” instance in the PARTY ROLE TYPE entity and another to the “internal organization” PARTY ROLE instance.
Is the party relationship type id part of the key to the PARTY RELATIONSHIP entity? If roles are defined in such a way that each pair of roles forms a unique PARTY RELATIONSHIP instance, then it is not necessary to define the party relationship id as part of the key. In this scenario, the PARTY RELATIONSHIP TYPE entity has as part of its key, two party role ids and hence the foreign key inheritance lines (the small horizontal tildes “~” across the relationship line) shown in the relationship between PARTY RELATIONSHIP TYPE and PARTY ROLE TYPE.
One could also argue that different pairs of roles types could be related to the same relationship. For instance, there could be a “person client” role and an “organization client” role, each of which could be used in the party relationship “client relationship,” which links either of these roles to an “internal organization” in order to fully define the relationship. Instead of defining these roles, one could just define a “client role” and the PARTY entity will define if it is a person or an organization. Alternatively, one could define two party relationships: a person client relationship and an organization client relationship. Still, if the enterprise wants to have the flexibility to model different combinations of roles for the same party relationship, then include the party relationship type id as part of the unique identifier of the PARTY RELATIONSHIP entity and form many-to-many relationships between PARTY ROLE TYPE and PARTY RELATIONSHIP TYPE.
Whereas Table 2.3 identified the roles that parties played, the following tables show their relationships to other parties to complete the information needed. Table 2.5 shows the examples of data maintained in the PARTY RELATIONSHIP to represent organization to organization relationships and Figure 2.6b graphically illustrates these relationships.
Table 2.5 Organization-to-Organization Party Relationships
Figure 2.6b Party relationship hierarchy example.
The internal organizations illustrated within Table 2.5 are ABC Corporation, ABC Subsidiary, XYZ Subsidiary, and ABC's Customer Service Division. The first 2 rows show that ABC Subsidiary and XYZ subsidiary are subsidiaries of the parent corporation, ABC Corporation. The third row shows that the Customer Service Division is a division of ABC Subsidiary. The fourth row shows that ACME Company is a customer of ABC Subsidiary. The fifth row shows that Sellers Assistance Corporation has a relationship with ABC Subsidiary as its agent and can sell products for ABC Corporation. Notice that the sixth row shows that Fantastic Supplies is a supplier for ABC Subsidiary. If Fantastic Supplies was a supplier for all of ABC Corporation, there would be a relationship to the parent company, ABC Corporation, instead of to the subsidiary.
Table 2.6 shows examples of people's relationships within their respective organizations. Table 2.4 identified that John Smith and William Jones were employees, and this table further identifies that they are employees of ABC Subsidiary (as opposed to the parent company, ABC Corporation). Nancy Barry is a supplier representative for Fantastic Supplies; therefore, people can contact her to purchase items from Fantastic Supplies. Marc Martinez is the customer representative for ACME Company and is the person to contact for getting in touch with this customer. Barry Cunningham is a contractor for the parent company, ABC Corporation. Table 2.6 shows person-to-person relationship examples. These relationships are stored in the same entity (PARTY RELATIONSHIP) as organization-to-organization relationships; however, Table 2.6 shows only person-to-person relationships for ease of understanding.
Table 2.6 Person-to-Organization Party Relationships
To complete the examples of party relationships, Table 2.7 provides examples of people who have relationships with other people. Examples of person-to-person relationships include customer contact relationships, supplier contact relationships, people's mentors, and people's family structures. John Smith is the supplier coordinator for ABC and has a relationship with Nancy Barry, who is the supplier service contact (and is the supplier representative for Fantastic Supplies, as shown in the previous table). William Jones is an account manager and has a relationship with Marc Martinez, who is the customer representative for ACME Company. John Smith is the mentor for Barry Goldstein, and the last row shows that Judy Smith is John Smith's daughter.
Table 2.7 Person-to-Person Party Relationships
Party Relationship Information
PARTY RELATIONSHIPs have other information associated with them, as shown in Figure 2.7. Each PARTY RELATIONSHIP may have a PRIORITY TYPE, a PARTY RELATIONSHIP STATUS TYPE, and several COMMUNICATION EVENTs associated with it. The PRIORITY TYPE entity establishes the relative importance of the relationship to the enterprise. Examples may include “very high,” “high,” “medium,” and “low.” Alternatively, an enterprise may choose to use “first,” “second,” “third,” and so on to prioritize the importance of various relationships. The PARTY RELATIONSHIP STATUS TYPE entity defines the current state of the relationship. Examples include “active,” “inactive,” or “pursuing more involvement.” Each COMMUNICATION EVENT records any type of contact between parties within a relationship—for example, phone calls, meetings, e-mails, and so on. This entity will be further described later in this chapter. Table 2.8 provides examples of information that may be maintained for party relationships. The table shows that the customer relationship with ACME is regarded as high and the relationship is active. The agent relationship with Sellers Assistance Corporation is currently inactive. The supplier relationship with Fantastic Supplies is active, and the priority is regarded as medium. John Smith and Nancy Barry's supplier coordinator relationship is active, and so is the customer contact relationship between William Jones and Marc Martinez.
Figure 2.7 Party relationship information.
Table 2.8 Party Relationship Information
Similar to ROLE TYPEs, there will be many statuses for many entities throughout the data models-for example, ORDER STATUS, SHIPMENT STATUS, WORK EFFORT STATUS, and so on. The PARTY RELATIONSHIP STATUS TYPE is shown as a subtype of STATUS TYPE. Other STATUS TYPEs throughout this book will also be subtypes of STATUS TYPE; however, again for simplicity purposes (and to make best use of room on the paper), the subtype relationship will not always be explicitly shown as it is in Figure 2.7).
People and organizations may be contacted many different ways; by mail, phone, fax, e-mail, cell phone, pager. This section describes three very flexible data models for storing information about addresses, phone numbers, fax numbers, and any other type of mechanism used for contacting parties.
Most data models will portray contact information in separate attributes such address line 1, address line 2, home phone, office phone, office fax, and so on. There are two main issues with modeling information this way. First of all, how does one know how many contact numbers to allow for and what types? What if someone has two or three home addresses or home phones? What if new mechanisms arise for contacting people? In practice, when the database doesn't allow for all possibilities, the user just adds this information into the “comment” field which makes searching much more difficult!
The other issue with modeling contact information as individual attributes is that each contact address, number, or string may have its own information. For instance, addresses may have directions, and contact numbers may have information associated with them such as indications not to solicit or information about the best times to call. If these contact mechanisms are not modeled on their own, a great deal of redundancy can occur. For instance, if the address of the headquarters of a large company is stored as an attribute, the directions may be stored inconsistently a number of times in the database.
One way to contact a person or organization is by either visiting them at their address or mailing them at their postal address. Figure 2.8 provides a data model to capture postal address and geographic boundary information. The POSTAL ADDRESS entity maintains all addresses used by the enterprise in a central place. The PARTY POSTAL ADDRESS entity shows which POSTAL ADDRESSes are related to which PARTYs. The GEOGRAPHIC BOUNDARY entity maintains any type of encompassing area such as a COUNTY, CITY, STATE, COUNTRY, POSTAL CODE, PROVINCE, or TERRITORY, and it is related back to the POSTAL ADDRESSes as well as recursively to other GEOGRAPHIC BOUNDARY.
Figure 2.8 Postal address information.
An organization may have many addresses or locations. For instance, a retailer might have several outlets at different addresses. In this instance, there is only one organization but many locations or addresses. Additionally, the same postal address might be used by many organizations. For instance, many subsidiaries of an organization might share the same address. Also, different organizations might share the same address if they are in a shared office facility.
There is also a many-to-many relationship between PERSON and POSTAL ADDRESS. A particular address may have many people residing there, such as many employees who work at the same facility. And, of course, people generally have many addresses: their home address, work address, vacation address, and so forth. Therefore, there is a many-to-many relationship between PARTY and POSTAL ADDRESS.
Instead of two separate relationships for people and organizations, the model shows a many-to-many relationship between PARTY and POSTAL ADDRESS that is resolved via an intersection entity (sometimes referred to as an associative or cross-reference entity) named PARTY POSTAL ADDRESS, as shown in Figure 2.8. Notice that PARTY ADDRESS has a from date and thru date that allow tracking of the address history of parties.
Tables 2.9 and 2.10 give examples of party addresses. Table 2.9 lists the individual address records, while Table 2.10 cross-references parties to addresses. With this model, addresses are stored only once—thus eliminating redundant data problems—and can be reused many times in relationship to many parties. For instance, in Tables 2.9 and 2.10, the same address, address ID 2300, is used by ABC Corporation and ABC Subsidiary. Additionally, ABC Subsidiary has more than one address, as illustrated by its two entries in Table 2.10.
Table 2.9 Postal Address Data
Table 2.10 Party Postal Address Data
| PARTY | ADDRESS ID |
| ABC Corporation | 2300 |
| ABC Subsidiary | 2300 |
| ABC Subsidiary | 2400 |
| ACME Company | 2500 |
The POSTAL ADDRESS entity stores attributes to identify the specific location for either visiting or sending mail to a party. The address1 and address2 attributes provide a mechanism for two text lines of an address. There may be a need for more address line attributes depending on the needs of the enterprise. The directions attribute provides instructions on what roads to travel and what turns to take in order to arrive at that address. Often this direction information is repeated in databases where the address is not treated as a separate entity on its own.
Each address may have many other GEOGRAPHIC BOUNDARYs. For example, each POSTAL ADDRESS may have a CITY, PROVINCE, TERRITORY, or other GEOGRAPHIC BOUNDARY, depending on its location within the world. POSTAL ADDRESSES may also be identified within other boundaries such as a SALES TERRITORY, SERVICE TERRITORY, or REGION. Each POSTAL ADDRESS may also have a POSTAL CODE. The POSTAL CODE identifies the mailing code that is used for sorting addresses and delivery within postal services. In the United States, the postal code is referred to as the zip code.
An alternate data model for this structure could be to tie the POSTAL ADDRESS to the specific GEOGRAPHIC BOUNDARYs that apply, instead of using the associative entity, POSTAL ADDRESS BOUNDARY. For instance, there could be a one-to-many relationship from POSTAL ADDRESS to CITY and another one-to-many relationship from POSTAL ADDRESS to POSTAL CODE. If there is a need for worldwide applicability—perhaps some addresses use territories (Australia), provinces (Canada), prefectures (Japan)—there would need to be a supertype for all of these or individual relationships. If a more specific application of this model is needed, the reader is encouraged to modify this model with more specific relationships.
GEOGRAPHIC BOUNDARYs are recursively related to other GEOGRAPHIC BOUNDARYs. For instance, each SALES TERRITORY, SERVICE TERRITORY, or REGION may be defined by relating it to a number of CITYs, STATEs, or COUNTRYs.
In many data models, phone numbers are shown as attributes of the organization or person. Usually, there are also fields for fax numbers, modem numbers, pager numbers, cellular numbers, and electronic mail addresses. This often leads to limitations in the systems built. For instance, if someone has two or three business phone numbers and there is only one business phone number field for a person, where are the other business phone number entered? In this new world, where there are many methods for contacting parties, more flexible data structures are needed.
Party Contact Mechanism—Telecommunications Numbers and Electronic Addresses
The CONTACT MECHANISM entity in Figure 2.9 stores access mechanisms for parties. Each CONTACT MECHANISM may be the way to contact a particular PARTY. The intersection entity PARTY CONTACT MECHANISM shows which contact mechanisms are related to which parties. The CONTACT MECHANISM TYPE entity maintains allowable values for different types of contact mechanisms, for example, “phone,” “mobile phone,” “fax number,” “e-mail address,” and so on.
Figure 2.9 Party contact mechanism—telecommunications numbers and electronic addresses.
CONTACT MECHANISMs are subtyped to include TELECOMMUNICATIONS NUMBER and ELECTRONIC ADDRESS. TELECOMMUNICATIONS NUMBER includes any access via telecommunications lines such as phones, faxes, modems, pagers, and cellular numbers. ELECTRONIC ADDRESS includes any access via services like the Internet or other electronic mail services.
The CONTACT MECHANISM TYPE entity shows all the possible values for types of contact mechanism. Examples include “phone,” “fax,” “modem,” “mobile phone,” “Internet address,” and “Web URL.” With technology growing so quickly, it is very likely that there will be other ways to get in touch with someone. The data structure in Figure 2.9 provides an easy method for adding any new contact mechanisms by simply inserting and using new CONTACT MECHANISM TYPEs.
The attribute non-solicitation ind on PARTY CONTACT MECHANISM provides a mechanism to indicate that that the mechanism is not to be called for solicitation purposes. If someone indicated that he or she did not want to be solicited on a particular number, it would be important to record this from a consideration point of view as well as a legal point of view.
Party Contact Mechanism (Expanded)
If CONTACT MECHANISMs are a means of reaching a person or organization, why not include POSTAL ADDRESSes as a subtype of CONTACT MECHANISM? Postal addresses are merely another mechanism for reaching someone. Imagine contact management systems that could provide flexible, scroll-down lists showing all the different ways of getting ahold of a party showing all the phone numbers, fax numbers, e-mail and postal addresses and categorized by their purpose (i.e., “primary home address,” “summer home address,” “main office number,” “secondary fax number,” “billing inquiries,” “headquarters number,” “emergency only,” and so on.). This could greatly facilitate how we contact parties and allow for very flexible and easily accessible contact information.
Another advantage of including POSTAL ADDRESSES as a subtype of CONTACT MECHANISM is that many business processes need a contact mechanism to complete a transaction and that contact mechanism may be a postal address, telecommunications number, or electronic address. For example, an order may be secured with either a POSTAL ADDRESS or an ELECTRONIC ADDRESS (i.e., an e-mail address).
Figure 2.10 provides a standard data model with this flexible structure. The CONTACT MECHANISM entity contains the subtypes POSTAL ADDRESS, TELECOMMUNICATIONS NUMBER, and ELECTRONIC ADDRESS. Because it has already been established that there is a many-to-many relationship between each of these subtypes and PARTY, this diagram shows a many-to-many relationship between CONTACT MECHANISM and PARTYs.
Figure 2.10 Party contact mechanism (expanded).
Also PARTY CONTACT MECHANISMs are optionally related to PARTY ROLE TYPE to indicate that each party's contact mechanism may be specified for a particular roles only. For instance, an organization may provide address information only in their role as a customer, and this may not be applicable to any other role.
Contact mechanisms may be related to each other and hence the recursive entity, CONTACT MECHANISM LINK. For example, certain phone numbers, when busy, may be automatically routed to pagers or cellular numbers. Fax numbers may be automatically connected to e-mail addresses. This may be important information to know when contacting parties.
Each contact mechanism for each party may have many purposes. For instance, an address might be used as a mailing address, a headquarters address, a service address, and so on. Most systems have a separate record for the mailing address, headquarters address, and service address, even though the address information may be exactly the same. Furthermore, just as addresses are intended for specific purposes, so are other contact mechanisms. A single contact mechanism may have more than one purpose. For example, business people sometimes have a single number for both their phone and fax needs.
Therefore, the data model in Figure 2.10 shows that each PARTY CONTACT MECHANISM must have one or more PARTY CONTACT MECHANISM PURPOSEs, each of which is described by a CONTACT MECHANISM PURPOSE TYPE.
Because the purposes of various contact mechanisms change over time, the from date and thru date identify when the purposes are valid. The CONTACT MECHANISM PURPOSE TYPE maintains the possible list of purposes that can be applied.
An alternate way this could be modeled is to relate the CONTACT MECHANISM PURPOSE TYPE directly to the PARTY CONTACT MECHANISM and include the contact mechanism purpose type id as part of the key to the PARTY CONTACT MECHANISM entity. This would be a simpler design, but it would lead to redundancy in many cases. For example, if the same party's address served as a mailing, headquarters, and service address, it would be stored as three instances in the PARTY CONTACT MECHANISM entity. Each instance would have the same party and contact mechanism id but would have a different purpose. Therefore information related to just the party and contact mechanism, such as the non-solication ind, would be repeated. In other words, the PARTY CONTACT MECHANISM entity has significance on its own and may have its own attributes or relationships, independent of the purpose(s). For this reason, our model shows separate PARTY CONTACT MECHANISM and PARTY CONTACT MECHANISM PURPOSE entities.
Table 2.11 gives examples of party contact mechanisms. The first seven entries show that there are many contact mechanisms for ABC Corporation. The first contact mechanism is a phone number and serves as the general phone number for the organization. The main fax number shows on the second row. ABC Corporation has a secondary fax number, shown as (212) 356-4898. The next rows show that 100 Main Street has more than one purpose; it is both the headquarters and the address for billing inquiries. ABC Corporation has another address where it has a sales office at 500 Jerry Street. ABC Corporation has its Web address at abccorporation.com. ABC Subsidiary, which is part of ABC Corporation, has two addresses listed: one at 100 Main Street for a service address and one at a sales address at 255 Fetch Street. As another example, John Smith has several contact mechanisms listed such as his main office number, main home number, his cellular or mobile number, and his home and work addresses. Barry Goldstein's contact numbers show two work numbers, a primary number and a secondary number, along with a work e-mail, personal e-mail, and home address.
Table 2.11 Party Contact Mechanisms
Table 2.11 shows the flexibility and maintainability of correct contact information for all types of current contact mechanisms as well as new contact mechanisms that could potentially emerge as telecommunications evolves.
Facility Versus Contact Mechanism
A contact mechanism could be tied to a particular PARTY, such as a person's cellular telephone number, or it may be tied to a physical location, for example, the telephone number for a manufacturing plant or the telephone number for a tower facility. These physical facilities are not postal addresses or parties (although they are associated with postal addresses and parties), and another entity is needed to describe them. The CONTACT MECHANISM entity maintains information about a label or identifier for contacting a party; the FACILITY stores the attributes or relationships associated with physical structures.
Figure 2.11 provides a structure to record information about physical facilities. FACILITY subtypes include WAREHOUSE, PLANT, BUILDING, ROOM, OFFICE, and the FACILITY TYPE allows for other types. Each FACILITY may be made up of other FACILTIES. For example, a BUILDING may be made up of ROOMs; if there is a more specific need to track floors, a BUILDING may be made up of FLOORs that are made up of ROOMS. An attribute that may be of interest to the physical facility is square footage.
Figure 2.11 Facility versus contact mechanism.
Each FACLITY may involve one or more parties, thus the FACILITY ROLE entity maintains what PARTYs are playing what FACILITY ROLE TYPEs for what FACILITYs. For instance, certain parties may use the facility, lease the facility, rent the facility, or own the facility-hence, the FACILITY ROLE TYPE entity stores these possible values. Also, each FACILITY may be contacted via one or more CONTACT MECHANISM. A PLANT facility may be contacted using several of the previously mentioned contact mechanisms such as phone, fax, e-mail, postal address, and so on. The FACILITY could have more than one of each of these CONTACT MECHANISMs; for example, it may have a street postal address of 100 Smith Street and another address of PO Box 1234.
Depending on the environment, a CONTACT MECHANISM may be the mechanism to contact more than one FACILITY. For example, perhaps the same postal address may be the contact mechanism for more than one plant that is grouped together within the same postal address. Since this may be possible, the FACILTIY CONTACT MECHANISM associative entity provides for a many-to-many relationship between CONTACT MECHANISM and FACILITY.
It is important in many applications to track information regarding with whom and when contact or communication was made within relationships between parties. For instance, sales or account representatives often need to know who was called for what purpose and when in order to properly follow up with their customers. The contact or communication might have been via a telephone call, an in-person sales meeting, a conference call, a letter, or any other method of encounter.
In Figure 2.12, the entity COMMUNICATION EVENT provides a history of the various communications that have been made or will be made between parties. A COMMUNICATIONS EVENT is defined as the interchange of information between parties via some type of contact such as a phone call, meeting, videoconference, and so on. The COMMUNICATION EVENT may be within the context of a particular PARTY RELATIONSHIP, or it may be between many parties and therefore use the COMMUNICATION EVENT ROLE to describe the roles of parties. For contact events that involved more than two parties (for instance, a meeting or seminar), the COMMUNICATION EVENT ROLE may define the parties and the roles they play with the event (facilitator, participant, note taker, and so on).
Figure 2.12 Communication event.
The COMMUNICATION EVENT will usually be within the context of a PARTY RELATIONSHIP and not the COMMUNICATION EVENT ROLE because it is within a relationship that communications usually make sense. It is possible to have several relationships between two parties. For instance, Marc Martinez might be the customer contact for John Smith in one relationship. At a later date, Marc Martinez might decide to work for John Smith's company (ABC Subsidiary) and report to him. It would be appropriate to track the communication events for these relationships separately.
COMMUNICATION EVENTs may be categorized two main ways: what contact mechanism was used to conduct the event (phone, fax, letter, e-mail, face to face, and so on) and what was the purpose of the contact event (support call, sales follow-up, customer service call, conference, seminar). A COMMUNICATION EVENT occurs via one and only one CONTACT MECHANISM TYPE; however, it may have many COMMUNICATIONs EVENT PURPOSEs. For instance, a particular meeting may be a customer service support call as well as a request to deliver an additional item. Figure 2.12 provides the subtypes of INQUIRY, SUPPORT CALL, CUSTOMER SERVICE CALL, MEETING, SALES FOLLOW-UP, CONFERENCE, SEMINAR, ACTIVITY REQUEST as well as the COMMUNICATION EVENT PURPOSE TYPE to indicate that there may be additional COMMUNICATION EVENT PURPOSEs. Other possible purposes include “initial sales call,” “service repair call,” “demonstration,” “sales lunch appointment,” or “telephone solicitation.” The description attribute provides for storing additional information about the purpose such as “This is a critical sales call that must turn client around”.
The VALID CONTACT MECHANISM ROLE provides a mechanism to identify what types of COMMUNICATION EVENT ROLES TYPEs are valid for what types of CONTACT MECHANISM TYPEs. For instance, a “caller” and “receiver” may be valid for a “phone” contact mechanism type, while “facilitator,” “participant,” and “note taker” are valid roles for a “face-to-face communication.” The COMMUNICATION EVENT STATUS TYPE maintains the state of the event. Example statuses include “scheduled,” “in progress,” and “completed.”
The COMMUNICATION EVENT maintains the datetime started, datetime ended, and a note describing the contact. An example note may be “initial sales call went well and customer seemed interested in moving forward quickly with a demonstration of the Thingamajig product”. Table 2.12 gives other examples of types of communication events.
Table 2.12 Communication Events
Table 2.12 gives examples of possible communication events. William Jones, an account manager for ABC Corporation, has made several sales contacts with Marc Martinez, the customer contact for ACME Corporation. The first four entries in Table 2.12 show the date, time, purpose, and type of contact mechanism used as well as the status of the event. The status is used to indicate if the activity has been completed or if it is just scheduled. William Jones started with a face-to-face initial sales meeting on January 12 and then proceeded with a more detailed product demonstration using the company's Web site, which has the capabilities to do a product demonstration and includes chatting capabilities. The fifth entry shows an email contact initiated by John Smith to Nancy Barry who is a supplier representative. The sixth entry shows that Joe Schmoe has made a call to Jerry Red regarding a problem and illustrates that communication event tracking is important not only in sales but in other areas of the business such as purchasing relationships. This data structure provides a mechanism for tracking communication events for all types of relationships and circumstances and is a very powerful business tool.
Very often, communication events require follow-ups and management in order to ensure proper service, sales coverage, or support. Many times, cases are opened to manage related communication events involving the same issue. For instance, if a customer calls up about a technical support problem, an action may need to be taken—for instance, sending the latest patch to correct the problem.
Figure 2.13 shows the data model that both supports grouping communication events into cases and relates each event to some work that may be required as a result of the communication event. A CASE may be set up for a series of related COMMUNICATION EVENTs regarding a particular issue. Each CASE may have several CASE ROLEs that identify who is responsible for the CASE, who checks the quality of service within the CASE, who is the customer for the CASE, and so on.
Figure 2.13 Communication event follow up.
Each COMMUNICATION EVENT may be related to one or more WORK EFFORTS because in a single event one of the parties may have more than one follow-up action. WORK EFFORTS will be further defined and explained later in this book in Chapter 6, Work Efforts. Also each WORK EFFORT may be related to one or more COMMUNICATION EVENTs because a caller may request a WORK EFFORT, follow up on the progress of the WORK EFFORT, and have several other communications about the WORK EFFORT.
Table 2.13 provides sample data for the WORK EFFORTs that stem from communication events. All the communication events listed are related to a technical issue of a customer's software crashing and calling in on a support line to establish the first communication event. Jerry Reed is the person handling the call; he opens case “105.” Joe Schmoe is the customer calling in, and Larry Assure is his manager, who is monitoring this case. The first call is taken on Sept 19 at 3 p.m. Jerry realized that he needs to send out a software patch so he sets up a work effort “1029” to make sure this gets done. On Sept 20 he follows up on this outstanding work effort and sends out the software patch with another communication effort, namely an e-mail. The example illustrates that a work effort to send a software patch in order to correct the problem may be related to two communication events; it is related to the request to send the software as well as to the communication event in which the software patch was sent. It also could have been related to follow-up calls from the customer about the sending of the patch. The third communication event shows that he followed up to make sure the patch worked and to close the case.
Table 2.13 Communication Event Follow-Up
Most data models and database designs unnecessarily duplicate information regarding people and organizations. As a result, many organizations have a difficult time maintaining accurate information. This chapter has illustrated how to build a very flexible and normalized data model for people, organizations, party relationships, addresses, contact mechanisms, and contacts made between parties. Figure 2.14 shows the key entities discussed in this chapter and their relationships.
Figure 2.14 Overall party model.
Please refer to Appendix A for a listing of entities and attributes. SQL scripts to build tables and columns derived from the logical models in this book can be found on the full-blown CD-ROM, which is licensed separately.