SQL Server Magazine

What's the best way to handle the explosion of communications methods we're seeing in today's lifestyles? We're experiencing a greater and greater demand to capture all types of phone numbers—home phone, business phone, cellular phone, mobile phone, pager, fax phone, home fax. Add to that demand the proliferation of email addresses, usually more than one per person, that we also need to capture. Many database administrators and designers struggle to adapt existing database architectures to accommodate all this information.

Many database people resort to a technique I call the simple addition solution. With each new type of telephone number or email address, you add the new means of contact to the appropriate table on an as-needed basis. Screen 1, is a data entry/edit form from a contact management database; it shows the different types of phone numbers and an email address. As you use this database, if you need another type of phone number or space for a second email address, you simply add a new field to the table, then add a corresponding text box to this form.

Unfortunately, adding columns to a table to accommodate home phone, second home phone, work phone, email1, email2, etc., is the same as creating an array. An array is a structure that can accommodate multiple occurrences of a data value. Table 1 shows weeks (numbered as 1 and 2) and days of the week in an array. In Visual Basic (VB), you might define an array that could store the days of the week in this way:

Dim MyWeek, MyDay
MyWeek = Array("Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun")
'Return values assume lower bound set to 1 (using Option Base statement)
MyDay = MyWeek(2)' MyDay contains "Tue"
MyDay = MyWeek(4)' MyDay contains "Thu"

A relational database management system (RDBMS) presents data in two dimensions, as a set of rows and columns, with each intersection of a row and a column occupied by a data value or a NULL condition (lack of data). Neither the RDBMS architecture nor the SQL programming language (the 1992 SQL-2 standard) supports arrays. So if you want to build an array in a relational database management system, you have to restructure it to match the architecture in Table 2.

Table 2 shows how this same array would appear in an RDBMS table. Compare Table 2 to the array in Table 1, where the week number occurs once, but for each week number, you have seven entries for days of the week. In the relational database representation of the data (Table 2), each day is in a separate row, with an associated week number. As a result, the week number is repeated for each day.

Many database tables contain arrays. Some are subtly identified, such as home phone, work phone, and fax phone. Others are much more obvious, such as Phone_1, Phone_2, and Phone_3.

Although the array is appealing for informational data retrieval, as in an OLAP or decision-support system, such arrays are difficult and awkward to use in an OLTP environment because of the high level of data redundancy (week number repeated many times) and the uncertainty regarding data maintenance. The function and purpose of an OLAP/decision-support system is to provide storage and a means of retrieving information, not just disparate bits of data. Compared with data stored in an OLTP database, data stored in an OLAP/decision-support environment is significantly less fragmented, and when retrieved, can to present a more complete picture of the business environment. Furthermore, OLAP (and often decision-support) databases don't let the end-users modify the data.

In an OLTP, or transactional, database, a flattened array can mean complications with data management and data retrieval. For instance, in the contact manager on Screen 1, the following situations could arise, leading to misinterpretations, data maintenance headaches, or violations of data integrity:

• If the company changes its main phone number or fax number, you must change all occurrences of the old number for all Cascade Coffee Roasters contacts, or the data in your Contact Manager will be out of sync.
• If Cascade Coffee Roasters adds a second phone line for public access, you'll have to decide how to handle this change. Should you change the work phone number of half your Cascade Coffee Roasters contacts to this new number? Or should you campaign to have a new field added to the form (and thus a new column to the underlying table) in which to store the new phone number? If you opt for the latter, must the second work phone field remain blank (null) for all contacts in your database who do not work for Cascade Coffee Roasters?
• If you are successful in your request for a second work phone number field and you receive a directive to produce a phone list of company contacts, how do you handle the situation? Because only a few contacts will have a second work phone number, do you leave the second phone number out of the report entirely, thus excluding some information? Or do you include it, and end up with a report that has blank space for most of the contacts' second work phone numbers?
• You learn that Nancy Davolio's phone number has changed to (206)-555-9645. You have two issues to deal with in this situation. First, which of Nancy's phone numbers changed? Second, do you want to retain the old number, just in case the source of your information proves less than reliable?

The Scenario
The contact manager is of limited value for a serious contact management function. How-ever, some minor changes and enhancements could make it a useful source of information on client companies and their employees; business contacts and how to reach them; and telephone numbers, email addresses, and Web sites of everyone on your contact list. Proper normalization of the various parts of this contact manager and its underlying database will render this tool useful. (For details on how to normalize data, see SQL By Design, "Why You Need Data Normalization," Premiere issue).

Figure 1 is an entity model of the company-contact-communications portion of this contact manager database. Briefly, an entity is some-thing, such as a company (tblCompany), about which you want to store data, such as company name and address. An attribute is a property that describes an entity (CompanyName of tblCompany). A relationship is how one entity associates itself to other entities in the model. (I will describe the entity model in detail later.)

In this model, you can see the relationships between different entities. Each company can have many associated contacts. Each Contact has any number of associated phone numbers, email addresses, and Web sites (ContactComm). A value contained within the CommType table categorizes each means of contact. A company need not have a contact person of record; a company may have multiple phone numbers directly associated with it. (A more detailed description of the architecture is at the end of this article.)

   Prev. page   [1] 2     next page