Data Models, Data Modeling, Data Warehouse and Data Warehousing by Universal Data Models

The Data Model Resource Book
Chapter 2: People and Organizations

Introduction
The most frequent business information need is to ask questions about people and organizations and to be able to rely on accurate information. 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 of people and organizations, and how can they be contacted?
What types of communication or contacts have occurred between various parties?

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 upon which to add more detail. This chapter includes data models on:

Organization definition
Person definition
Party definition
Party relationship
Address definition
Contact mechanism definition
Contact information

Organization Definition
Most data models maintain organizational information in various 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 upon 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 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 get out a correct mailing list!

The solution to this redundancy problem is to model an entity called ORGANIZATION which 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 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. The ORGANIZATION entity is sub-typed into INTERNAL ORGANIZATION and EXTERNAL ORGANIZATION. An INTERNAL ORGANIZATION is one that is part of the enterprise for whom the data model is being developed and an EXTERNAL ORGANIZATION is not part of that enterprise.

This model reduces redundancy since the organization name is stored only once, as opposed to storing this information redundantly in a customer entity, supplier entity, department entity, or any other entity storing organization information.

Table 2.1 gives examples of data in the ORGANIZATION entity. Notice that organizations include not only businesses but also other groups of individuals such as departments. For example, the accounting and information systems departments are included as organizations.

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 which will determine how the enterprise will customize these models for its own use.

Person Definition
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 which change over time. Most systems will record redundant information about a person since 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. However, much of John Smith's information has remained the same, such as his name, sex, 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 for 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 yet one division, a customer contact of yet another division, and a supplier contact of another division. Rather than have three separate records for Shirley with redundant information, there should only be one record.

To address this issue, Figure 2.2 shows a PERSON entity which stores a particular person's information, independent of his or her jobs or roles. Attributes of the PERSON entity could include sex, birth date, height, weight, and any other characteristics which describe the person.

Just as the ORGANIZATION entity is sub-typed, the PERSON entity is sub-typed into EMPLOYEE and NON-EMPLOYEE. An EMPLOYEE is a person who is employed by the enterprise for whom the data model is being developed and a NON-EMPLOYEE is not employed by that enterprise.

Table 2.2 shows some example data for the PERSON entity. This model has again helped reduce redundancy since the person's base information is only maintained once, even though the person may play many different roles. The Party Relationships section later in this chapter will describe how to model the various roles each person and organization can play.

Party Definition
Organizations and people are similar in many respects. They both have common characteristics which 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 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 form an exclusive arc (see Chapter 1 for a discussion on exclusive arcs). For instance, a sales order could either 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 super-entity named PARTY which has as its two sub-types, PERSON and ORGANIZATION. This PARTY entity will enable storage of some of the common characteristics and relationships which people and organizations share.

Parties are classified into various categories using the entity PARTY DEFINITION which stores each category into which parties may belong. The possible values for categories are maintained in the PARTY TYPE entity. For example, a type of party may be "minority-owned business", "8A business", "woman-owned business", "government institute", or "manufacturer". The categorizations of parties can be used to determine if there are any special business considerations for parties, special pricing arrangements, or special terms based upon the type of party. It is also a mechanism for classifying businesses into types of industries for market segmentation. A from date and thru date are included so history can also be tracked, since 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 organization as a party to a transaction.

Party Relationship
As noted previously, a person or organization may play any number of roles such as a customer, supplier, employer, or subsidiary. Each role that a party plays only makes sense 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.

Instead of modeling just 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 ACME Company is a customer, but that ACME Company is a customer of ABC Subsidiary. By default, this fact also implies that the ABC Subsidiary is a supplier of the ACME Company.

A relationship is comprised of two parties and their respective roles. For example, customer/supplier, parent/subsidiary, and division/department are possible organization relationships.

The PARTY RELATIONSHIP entity shown in Figure 2.4 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 the valid time frames of the relationship. The PARTY RELATIONSHIP TYPE entity in Figure 2.4 consists of a pair of roles which are used to define the nature of a PARTY RELATIONSHIP. Customer/Supplier is a valid pair of roles, while the combination of Customer/Sales Agent roles would not be valid because these roles do not complement each other (Authorizor/Sales Agent would make more sense). The description attribute describes the nature of a specific relationship. For example, a customer/supplier relationship description may be "where the customer has purchased or is planning on purchasing items or services from the supplier."

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/supplier" PARTY RELATIONSHIP TYPE, there would be two relationships: one to the "customer" instance in the PARTY ROLE TYPE entity, and another to the "supplier" instance.

The PARTY RELATIONSHIP STATUS entity defines the current state of the relationship. Examples include "active", "inactive", or "pursuing more involvement". The PARTY PRIORITY 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.

Figure 2.5 illustrates the relationships for the organization, ABC Subsidiary. Table 2.4 shows the data which is stored in the PARTY RELATIONSHIP entity to represent these relationships. The internal organizations within the table are ABC Corporation, ABC Subsidiary, and ABC's Customer Service Division. The first row shows that ABC Subsidiary is a subsidiary of the parent corporation, ABC Corporation. The second row shows that the Customer Service Division is a division of ABC Subsidiary. The third row shows that ACME Company is a customer of ABC Subsidiary. Notice that the fifth row shows that ABC Subsidiary is a customer of Fantastic Supplies, or in other words, 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.

Just as organizations have relationships with other organizations, people have relationships with other people. Examples of person-to-person relationships include people's mentors, people's family structures, and people's business contacts. Table 2.5 shows person-to-person relationship examples. These relationships are stored in the same entity (PARTY RELATIONSHIP) as organization-to-organization relationships; however, Table 2.5 breaks out the person-to-person relationships for ease of understanding.

In Table 2.5, John Smith has Barry Goldstein as a mentor. Judy Smith is John Smith's daughter. Joe Schmidt is the customer representative whom Nancy Barry calls upon to sell her company's products. John Smith is Barry Cunningham's customer (contact).

Finally, a person may play any number of roles within an organization: an employee, a supplier contact, a customer contact, and so on. Table 2.6 shows examples of people's roles within organizations. For example Nancy Barry, John Smith, and William Jones are all employees of ABC Subsidiary. William Jones is not only an employee of ABC Subsidiary but also contracts to Hughes Cargo. Barry Cunningham is a supplier representative for Fantastic Supplier; therefore, people can contact him to purchase items from Fantastic Supplies. Joe Schmidt is the customer representative for ACME Company and represents its interests as a customer.

Address Definition
Figure 2.6 shows the data model for address-related information. The GEOGRAPHIC BOUNDARY entity stores the COUNTY, CITY, STATE, and COUNTRY of an ADDRESS. The ADDRESS entity maintains all addresses used by the enterprise in a central place. The PARTY ADDRESS entity shows which ADDRESS is related to which PARTY. The PARTY ADDRESS ROLE entity defines the roles that a PARTY ADDRESS may have and is primarily used to validate that the address is used for its intended purpose. PARTY ADDRESS ROLE TYPE maintains the possible values of the roles which addresses may play.

Address
Each ADDRESS has geographic boundaries in which it resides. These could include counties, cities, states, territories, provinces (Canada), prefectures (Japan), regions, and countries; they will vary by country. As an example, the model in Figure 2.6 includes the sub-types COUNTY, CITY, STATE, COUNTRY, and the super-type GEOGRAPHIC BOUNDARY with appropriate relationships between them.

The ADDRESS entity stores attributes to identify the specific location within the geographic boundary. 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 postal code identifies the mailing code that is used for delivery. In the United States, the postal code is referred to as the zip code. The directions attribute provides instructions on what roads to travel and what turns to take in order to arrive at that address.

Party Address
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 or PARTY but many locations or addresses. Additionally, the same 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.

Therefore, there is a many-to-many relationship between ORGANIZATION and ADDRESS. There is also a many-to-many relationship between PERSON and ADDRESS. A particular address may have many people residing there, such as when many employees work at the same facility. And, of course, people generally have many addresses: their home address, work address, vacation address, and so forth.

Instead of two separate relationships for people and organizations, the model shows a many-to-many relationship between PARTY and ADDRESS that is resolved via an intersection entity (sometimes referred to as an associative or cross-reference entity) named PARTY ADDRESS, as shown in Figure 2.6. Notice that PARTY ADDRESS has a from date and thru date which allows the ability to track the address history of parties.

Tables 2.7 and 2.8 give examples of party addresses. Table 2.7 lists the individual address records, while Table 2.8 cross-references parties to addresses. With this model, addresses are only stored once—thus eliminating redundant data problems—and can be reused many times in relationship to many parties. For instance, in Tables 2.7 and 2.8, 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.8.

Address Role
For each party located at an address, or PARTY ADDRESS, there may be many purposes or roles for the address. The address might be 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. Therefore, the data model in Figure 2.6 shows that each PARTY ADDRESS must have one or more PARTY ADDRESS ROLEs. The PARTY ADDRESS ROLE stores the roles a party address may play. A list of possible values is available in the PARTY ADDRESS ROLE TYPE entity.

Another way this could be modeled is to include the role in the PARTY ADDRESS entity and have additional cross-reference records for each of the address' roles. 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 ADDRESS entity. Each instance would have the same party and address ID but would have a different role. The disadvantage of this model is that the PARTY ADDRESS entity has significance on its own. For instance, each PARTY ADDRESS may have telephone and fax numbers associated with it. For this reason, our model shows separate PARTY ADDRESS and PARTY ADDRESS ROLE entities.

Table 2.9 illustrates that ABC Corporation's address can be used as the corporate headquarters, central mailing address, and legal office. The PARTY ADDRESS ROLE entity provides for the storage of a party address only once with many roles for that party's address.

Contact Mechanism Definition
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 business phone numbers and there is only one business phone number field for a person, where is the other business phone number entered? In this new world where there are many methods for contacting parties, more flexible data structures are needed.

The CONTACT MECHANISM entity in Figure 2.7 stores access numbers for parties. Each CONTACT MECHANISM may be the way to contact either a particular PARTY or PARTY ADDRESS. The intersection entity PARTY CONTACT MECHANISM shows which contact mechanisms are related to which parties or addresses. The model also shows valid roles available via the relationship from PARTY CONTACT MECHANISM to PARTY CONTACT MECHANISM ROLE. The CONTACT MECHANISM TYPE and PARTY CONTACT MECHANISM ROLE TYPE are entities which maintain allowable values.

Contact Mechanism
CONTACT MECHANISMs are sub-typed to include TELECOM NUMBER and ELECTRONIC ADDRESS. TELECOM 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 office phone, home phone, office fax, modem, cellular, Internet address, and other electronic addresses. With technology growing so quickly, it is very likely that there will be many ways to get in touch with someone. The data structure in Figure 2.7 provides an easy method for adding any new contact mechanisms by simply inserting and using new CONTACT MECHANISM TYPEs.

Contact Mechanism Relationships to Party and Party Address
A contact mechanism could be tied to particular physical locations (namely, PARTY ADDRESS) such as the telephone number for a retailer's store location, or it might be tied to a particular PARTY, such as a person's cellular telephone number. There is a many-to-many relationship from CONTACT MECHANISM to both PARTY ADDRESS and PARTY. For example, a contact mechanism may be used to contact more than one party, such as a joint telephone number for a family. The contact mechanism may also be for more than one party address, such as a roaming telephone number that covers more than one location. Therefore, the PARTY CONTACT MECHANISM is used as the intersection entity and represents the combination of a CONTACT MECHANISM used by either a PARTY or PARTY ADDRESS.

Table 2.10 gives examples of party contact mechanisms. The first two entries show the phone and fax numbers which are tied to the PARTY ADDRESS for ABC Corporation at 100 Main Street. The third and fourth rows show that John Smith's number at 100 Main Street is (212) 234-9856, but he has another office phone at 345 Hamulet Place. These are both tied to a PARTY ADDRESS. John Smith also has a cellular number which is tied directly to his PARTY instance. Barry Goldstein has an office phone which is tied to one PARTY ADDRESS (his work address) and a home phone which is tied to a different PARTY ADDRESS (his home address). He also has an Internet address which is tied directly to his PARTY instance, since he can access his e-mail from any location that is properly equipped.

Contact Mechanism Role
Furthermore, just as addresses are intended for specific purposes, so are party 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 PARTY CONTACT MECHANISM ROLE defines the designated purposes for each PARTY CONTACT MECHANISM. The valid roles are described in PARTY CONTACT MECHANISM ROLE TYPE.

An example of a party contact mechanism role is that a telephone number may be playing roles as the "primary business contact number" and the "general information number". Other possible party contact mechanism roles include "customer service number" or "invoicing questions line".

In the complex world of today, in which there are usually many contact mechanisms, it is very useful to identify the purposes of each contact mechanism. Since the purposes of various contact mechanisms change over time, the from date and thru date identify when the purposes are valid.

Party Location
Think of a party address as a physical location and a party contact mechanism as a virtual location. Therefore, the term party location is used throughout this book to mean the location of the party, whether it's a physical location such as the address of a retail store or a virtual location such as an Internet address. Although this model defines physical locations and virtual locations as very different and separate entities, it is conceivable to model a super-type PARTY LOCATION which has as its sub-types PARTY CONTACT MECHANISM and PARTY ADDRESS. This could be useful, for example, in identifying where an order came from—it could have been from a physical location or a virtual location such as an Internet address.

Contact Information
It is important in many applications to track information regarding with whom and when contact was made during a business relationship. 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 might have been via a telephone call, an in-person sales meeting, a conference call, a letter, or through any other method of encounter.

In Figure 2.8, the entity CONTACT NOTE provides a history of the various encounters or contacts made within a particular PARTY RELATIONSHIP. The CONTACT NOTE maintains the date of contact, a note describing the contact, and the type of contact. The status of the contact is maintained through the NOTE STATUS TYPE entity. Example statuses include "scheduled", "in progress", and "completed". The CONTACT TYPE entity is used to define the possible types of contacts such as "initial sales call", "service repair call", "demonstration", "sales lunch appointment", or "telephone solicitation". Table 2.11 gives other examples of types of contacts.

The CONTACT NOTE is related to the PARTY RELATIONSHIP and not the parties since it is within a relationship that contacts make sense. It is possible to have several relationships between two parties. For instance, Joe Schmidt might be the customer contact for Nancy Barry in one relationship. At a later date, Joe Schmidt might decide to work for Nancy Barry's company and report to her. It would be appropriate to track the contacts for these relationships separately. Table 2.11 gives examples of possible contacts. Nancy Barry, a sales person for ABC Corporation, has made several sales contacts with Joe Schmidt, the customer representative for ACME Corporation. The first four entries in Table 2.11 show the date and the nature of these calls. Notice that contacts have a status to indicate if the activity has been completed or if it is just scheduled. The fifth entry shows a contact initiated by John Smith in accounting to Barry Cunningham who is a supplier representative. This data structure provides a mechanism for tracking contacts for any type of relationship and is a very powerful business tool. Summary Most data models and database designs unnecessarily duplicate information regarding people and organizations. As a result, many organizations have a very 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.9 shows the key entities discussed in this chapter and their relationship to each other.

Please refer to Appendix A for more detailed attribute characteristics. SQL scripts to build tables, columns, and constraints derived from this logical model can be found on the accompanying CD-ROM that is sold separately.

Contact Universal Data Models, info@univdata.com or call (303) 688-1412 to find out how your organization can save time and money by "jump starting" your data modeling and data warehousing efforts.