This article is the third in a series about metamodels—data-model templates from which you can derive and create models for your own database projects. In "Metamodel for Retail Sales," May 2001, I introduced the basic concepts, then in "Metamodel for Product Assembly," June 2001, I showed how you can expand your metamodel. This time, I describe the contact-manager metamodel, which adds further complexity to the original metamodel.
You can use the contact-manager metamodel not only to build a standard contact-management system similar to the system in Microsoft Outlook but also to provide the basis for a customer-log or troubleshooting system. This metamodel is a plugin similar to the product-assembly metamodel. The contact-manager metamodel extends the foundational retail-sale metamodel by using the CUSTOMER and SALESPERSON entities as jumping-off points.
Nearly everyone in the IT industry has used contact-management software at some point, but few users understand the complexity of the file structure that lies beneath it. Some contact-management software packages aren't normalized, whereas other packages are partially normalized to provide better performance. However, as you probably know, the more denormalized a database becomes, the more data redundancy you incur. Consequently, data-integrity problems increase with every INSERT, UPDATE, and DELETE you perform. (For more information about the pros and cons of normalization, see "Why You Need Database Normalization," March 1999.)
The focus of this article is an idealized logical model for a typical, generic contact-management database. To show the database's data requirements, I express the entities in third normal form (3NF), meaning that you resolve all transitive dependencies. If you want to optimize performance when you convert the logical model to a physical model that's ready for implementation, you can denormalize at least part of the logical model.
The Players
To start the modeling process, you need to establish the database's basic entity set. The contact-manager example I discuss is concerned with customers, the units they work for (companies and departments within companies), and their various physical addresses, phone numbers, email addresses, and Web sites. And to complete the integration back to the retail-sale metamodel and provide a record of who's working with or servicing which customer, I've added salesperson to this mix. One way you can extend the contact manager's usefulness is to add a log file that tracks customer conversations. Further, you can design each log to organize the entries into categories and display a status value.
Building the Relationships
The relationships among the entities I list are quite complex, so let's develop the metamodel in several phases. First, let's look at the CUSTOMER, COMPANY, DEPARTMENT, and ADDRESS entities and their relationships, which Figure 1 shows. Figure 1 is a high-level entity relationship diagram (ERD) that displays the database's concept. As you build out the database, you add more detail to the conceptual metamodel. A company employs zero or many customers and has zero or many departments, whereas a customer works for only one company and only one department, which is part of only one company. A company, a department, or a customer can each have zero or many addresses, although each address can belong to one or more companies, departments, or customers. You can resolve the many-to-many (M:N) relationships between company address, department address, and customer address later in the design process by adding an associative entity for each M:N relationship. The complexity of these interrelationships lets you express real-world conditions, such as the small-business owner who lives and works at the same address and the individual who works for a department of a megacorporation that spans many locations.
To help you keep in touch with the people to whom you provide service or with whom you work, let's add an entity called CONTACT_VALUE, as the shadowed objects in Figure 2 show. CONTACT_VALUE is the entity that represents an individual's phone number, email address, or Web page. You can include as many different ways of keeping in touch with an individual as you need. A customer, a department, or a company can have zero or many contact values, whereas each contact value belongs to a customer, a department, or a company.
You can enhance this model to perform tracking and customer-service functions by adding two entities—CUST_LOG and SALESPERSON—as the shadowed objects in Figure 3 show. SALESPERSON is a holdover from the retail-sale metamodel. The CUST_LOG entity represents interactions between a salesperson and a customer. These interactions can be log entries from sales calls, technical support call records, prospective customer inquiries, or whatever type of customer or prospect interaction you want to record for your business. A customer can originate zero or many CUST_LOG entries, but a single CUST_LOG entry is originated by one customer. A salesperson manages zero or many CUST_LOG entries, whereas each CUST_LOG entry is managed by one salesperson.
Completing the Design
To complete the metamodel, you need to incorporate details that make the conceptual model more understandable. For example, you can add attributes and reference entities (which eventually become lookup tables, also known as lists of values), and you can resolve each M:N relationship by using an associative entity. Figure 4 is a detailed representation of the contact-manager metamodel that includes these features. Each entity carries its own set of attributes. A bold PK indicator at the top of the attribute list signals primary keys, and an FK followed by an integer value designates foreign keys. CASE software keeps track of multiple foreign key associations for each entity with these same designations. Notice that OwnerID, the CONTACT_VALUE entity's attribute, has three foreign key associations: the first for the COMPANY entity, the second for the DEPARTMENT entity, and the third for the CUSTOMER entity. The presence of the three foreign keys means that each instance—or record—of CONTACT_VALUE refers to an instance of COMPANY, or an instance of DEPARTMENT, or an instance of CUSTOMER, but not all three at the same time.
You can resolve the three M:N relationships by introducing three associative entities, one for each relationship. COMPANY_ADDR associates each instance of a company with an instance of an address, DEPT_ADDR associates a department with an address, and CUST_ADDR relates a customer to an address. Then, you can relate a single address to a company, many departments, and a multitude of customers while recording as many different addresses as you need for one customer. (For example, one customer might receive messages at home, business, vacation, and on-the-road addresses.)
Figure 4 includes three new entries: CONTACT_TYPE, CUSTLOG_STATUS, and CATEGORY. These entities are the reference entities, which will eventually become lists of values, or lookup tables. CONTACTTYPE is a list of descriptions for different telephone numbers—values such as home, business, cell, fax—that you can use to further define each instance of contact value. Similarly, CUSTLOG_STATUS is a list of status values or states that a customer log might be in, such as open, pending, closed, or unresolved. The third entity, CATEGORY, is a descriptor list of categories that you can use for addresses, customers, and customer logs. The many different types of addresses—home, business, vacation, on-the-road—are all categories of address; therefore, you would find these values in the CATEGORY table. A customer might be active or a prospect, current or previous. The categories of customer log might be under warranty or out of warranty, local customer or out-of-network customer. Because each of these reference entities provides a list of valid values for its associated child entities, the reference entities help maintain the integrity of the various entity domains.
Using Your Metamodel
Although this contact-manager metamodel is quite complicated, with properly designed complexity comes incredible flexibility. However, I have one caveat for aspiring metamodelers: The more flexible the design, the more difficult creating UIs that interact with the design can be. If you implement this contact-manager design without simplifying it, programming against it won't be easy. Nevertheless, the design can give you the necessary associations that you need to keep on top of your customer base without risking data-integrity violations that result from repetitive data. When you adopt any of these metamodel templates, you have to consider this trade-off. To denormalize is to incur problems with data redundancy that you have to manage through programming—otherwise you risk storing dirty data in your database. Choose wisely.
