Field Name

The Field Name is the set of absolute minimal words that uniquely identifies a particular field throughout the database. You created and refined field names earlier in the database design process (see Chapter 7, “Establishing Table Structures”), so you’ll just take each name and use it as the setting for this element.

Parent Table

The table that incorporates a given field within its structure is known as the field’s parent table. This is the only table in which the field will appear unless the field is participating in establishing a relationship. (You’ll learn more about this exception in Chapter 10, “Table Relationships.”) For example, STUDENTS is the parent table of the STUDFIRST NAME field.

Specification Type

The elements you set for a given field depend upon the type of specification you define for the field. You can define a specification in three ways.

1. Unique: This is the default specification for all fields except those that serve as a template for other fields or those that participate within a table relationship as foreign keys. You can incorporate all but the Source Specification element for this type of specification, and the element settings you establish will apply only to the field indicated in the Field Name element.

2. Generic: This specification serves as a template for other field specifications and helps you ensure consistent definitions for fields that have the same general meaning. For example, you could create this type of specification for a generic STATE field and then use it as the basis for every other STATE field in the database. Fields such as CUSTSTATE, EMPSTATE, and VENDSTATE all have the same meaning (they represent a state within the United States), but enough of an obvious distinction exists between them to require that they remain separate fields. (If you recall, you learned about generic fields in Chapter 6, “Analyzing the Current Database,” when you were developing the Preliminary Field List and in Chapter 7 when you were working with the Elements of the Ideal Field.)

   A generic specification requires you to use a nonspecific field name and element settings that are as broad and general as possible. You can, however, incorporate any element except Parent Table, Shared By, Alias(es), and Source Specification.

3. Replica: This is the default specification for a field based on a generic field or a field that serves as a foreign key within a table relationship, and it draws a majority of its element settings from an existing specification. You can incorporate elements that were not already incorporated by the source specification, and you can alter any element settings drawn from the source specification.

You’ll learn how to define each type of specification in the section “Using Unique, Generic, and Replica Field Specifications” later in this chapter.

Source Specification

The Source Specification element is set only on a Replica specification and indicates the name of the specific field specification upon which the current specification is based. (You’ll see a good example of this element in the next section.)

Shared By

The Shared By element indicates the names of other tables that share this field. The only table names that should appear here are those that have an explicit relationship to the field’s parent table. For example, assume you have a data table called EMPLOYEES that is related to two subset tables called PART-TIME EMPLOYEES and FULL-TIME EMPLOYEES via a field called EMPLOYEE ID NUMBER. As you create a field specification for EMPLOYEE ID NUMBER, you would use “PART-TIME EMPLOYEES, FULL-TIME EMPLOYEES” as the setting for this element.

Alias(es)

The Alias is a name (or set of names) that you use for the field in very rare circumstances. One instance in which you would use an alias is when there must be two occurrences of the field in the same table. Let’s assume that an organization is accustomed to identifying its employees by unique values within an EMPLOYEE ID NUMBER field. Now, consider the SUBSIDIARIES table structure in Figure 9.2 (this is a partial structure only).

In this instance, each subsidiary has a president and a vice president. Both of these individuals must be represented in the table because of their positions within the subsidiary organization, so there are two EMPLOYEE ID NUMBER fields in the table structure. Proper database design, however, dictates that there can only be one occurrence of this field within the table; there is an obvious problem here. The only solution is to use an alias for one or both occurrences of the EMPLOYEE ID NUMBER field. For instance, you could (for sake of clarity) use PRESIDENT ID as an alias for the first occurrence of EMPLOYEE ID NUMBER and VICE PRESIDENT ID as an alias for the second occurrence of EMPLOYEE ID NUMBER. With the aliases in place, both employees are properly represented within the table. Figure 9.3 shows the revised table structure.

Although using an alias is acceptable under these circumstances, you should use them very judiciously; otherwise, they can become difficult to manage and maintain, eventually conceal or disguise the true meaning of the original fields, and cause you to misunderstand what the data actually represents. This issue will become even clearer when you begin to establish table relationships.

Description

Description is a complete interpretation of the field. Composing a field description is extremely beneficial because it forces you (and everyone in the organization) to think carefully about the nature of the data that will be stored in the field. You can be relatively sure that the field requires further refinement if you have difficultly composing a suitable description.

Earlier in the database design process, you learned a set of guidelines for composing a table description. Similarly, a set of guidelines exists that governs how you compose a proper field description:

Guidelines for Composing a Field Description

• Use a statement that accurately identifies the field and clearly states its purpose. The description should supplement the field name in terms of defining what the field represents. It should also state the field’s role within the table or its relationship to the table’s subject. Here’s an example of such a description:

  CustCity—The metropolitan area in which a customer resides or conducts business. This is an integral component of a customer’s complete address.

• Write a clear and succinct statement. The description should be free of confusing sentences or ambiguous phrases. Although the description should be as complete as possible, use the minimum number of words necessary to convey the required information. As you’ve seen with table descriptions, verbose statements are difficult to read and understand.

• Refrain from restating or rephrasing the field name. Neither of these practices does anything to illuminate the identity or purpose of the field. Remember that the purpose of a description is to provide a complete interpretation of the field. Here’s an example of a poor description:

  CustLast Name—the last name of a customer.

  A description is far more useful when you write it in this manner:

  CustLast Name—the surname of a customer, whether original or by marriage, that we use in all formal communications and correspondence with that customer.

• Avoid using technical jargon, acronyms, or abbreviations. Although some people within the organization will understand these types of idioms, it’s better for you to use terminology that everyone understands. Remember that a description must be as clear as possible to anyone who reads it. For example, you should avoid this type of statement:

  Employee ID Number—A unique number used to identify an employee within the organization. It is a component of the SSP.

  The problem with this description is that there is no inherent way to determine the meaning of the acronym SSP. You could resolve this problem by spelling out the complete term, but it would be better for you to restate the purpose of the field.

• Do not include implementation-specific information. There’s no reason to include the fact that a given field appears on a particular data entry screen or is used within a specific piece of programming code. This type of information is more appropriate for the implementation phase of the overall database development process.

• Do not make this description dependent upon the description of another field. Each description should be as complete as possible and independent of every other description in the database. Interdependent descriptions introduce unnecessary confusion and can inadvertently obscure the field’s true identity and purpose. Avoid using a description such as this:

  Item Reorder Level—Minimum number of items that must exist for a particular product. (See description for Quantity On Hand.)

• Do not use examples. As you learned in Chapter 7, using examples in a description is a bad idea because they depend on supplemental information to convey their full meaning. You can ensure that a description is clear and succinct by keeping it absolutely free of examples.

Figure 9.4 shows the General Elements section of a Field Specifications sheet for an EMPLOYEE ID NUMBER field.

Data Type

The Data Type element indicates the nature of the data that the field stores. The three data types we’ll use as the setting for this element are:

1. Alphanumeric: This data type stores any combination of letters, numbers, keyboard characters, or special characters. Keyboard characters include the comma, dollar sign, exclamation mark, percent sign, and period. Special characters include the copyright symbol, the trademark symbol, and the symbol for pi.

2. Numeric: This data type stores only whole numbers and real numbers. It will not accept numbers with leading zeroes (for example, 0000234) because they are not genuine numbers.

3. DateTime: This data type stores dates, times, or a combination of both.

Keep in mind that the RDBMS program you use to implement the database will have a wider variety of data types available for you to use. I use these general data types, however, to keep things as simple and clean as possible during the design process.

Length

The Length element specifies the total number of characters that a user can enter for any given field value. The RDBMS program you use to implement the database will determine the maximum number of characters you can set for this element. Although you can theoretically set the length element for any data type, you should be aware that some RDBMS programs do not allow you to specify a length for a numeric field. Instead, the RDBMS program sets the length of a numeric field based on the type of number the field stores, such as an integer, a long integer, or a real number.

Decimal Places

The Decimal Places element denotes the number of digits to the right of the decimal point in a real number. The number of digits determines the real number’s precision. For example, many businesses require that all currency values have four digits of precision to the right of the decimal point.

Character Support

The Character Support element indicates the type of characters that a user can enter into a given field value. Setting and enforcing this element helps you ensure that the user cannot introduce meaningless data into the field, thus enhancing field-level integrity.

Let’s say you’re working with a CUSTSTATE field and its data type is Alphanumeric. This data type is appropriate for the field because it allows a user to incorporate letters as part of a given field value. But it also allows him to use numbers, keyboard characters, and extended characters, which means that he can enter a meaningless value into the field—there are no state names or state abbreviations that contain characters other than letters. You solve this problem by using the Character Support element to define the characters that the user can incorporate within a field value. (I address the issue of a valid combination of letters later in the “Logical Elements” section.)

You can choose to include or exclude any of the following types of characters.

• Letters: All letters of the alphabet including foreign language letters such as é and ñ.

• Numbers: 0 through 9.

• Keyboard characters: Any standard character other than letters and numbers, such as asterisk, ampersand, bracket, caret, comma, equals sign, exclamation point, parenthesis, percent sign, period, pound sign, question mark, quote, semicolon, slash, or vertical bar. Note that the Field Specifications sheet includes examples of the characters that belong to this category.

• Special characters: Any character that you can produce only through specific combinations of standard keys and the CTRL, ALT, and/or SHIFT keys, or with the aid of a special software program. Characters in this category include complex mathematical symbols, the copyright symbol, fractions, the symbol for pi, and the trademark symbol. The Field Specifications sheet includes examples of these characters as well.

Figure 9.5 shows the Physical Elements section of a Field Specifications sheet for an EMPLOYEE ID NUMBER field.

Key Type

The Key Type element designates a field’s role within a table, which you identified as you were establishing a primary key for the table. As you already know, a field can serve as a non-key, a primary key, or an alternate key. In Chapter 10, you’ll learn all about foreign keys and when to designate a field as a foreign key on the Field Specifications sheet.

Key Structure

The Key Structure element denotes whether a field designated as a primary key is acting as a simple (single-field) primary key or as part of a composite (multifield) primary key.

Uniqueness

This element indicates whether a field’s values are unique. You set it as “Unique” when the Key Type element is set to “Primary”; otherwise, you’ll typically set this element as “Non-unique.”

When you work with a non-key field, think about how its values are going to be used so that you can determine whether they should be unique. Consider the DEPARTMENTS table structure in Figure 9.6.

In this example, the EMPLOYEE ID NUMBER field identifies the person who manages a particular department. Assuming that a person is allowed to manage only one department at any given time, the values in this field should be unique; therefore, you should set the Uniqueness element for this field as “Unique.”

Null Support

Null Support specifies whether a field accepts Null. “No Nulls” is the setting you’ll commonly use for this element, especially when a field serves as a primary key or an alternate key, or when the field’s Required Value element is set to “Yes.” You can set this element to “Nulls Allowed,” however, when there is a valid reason for a field to accept Nulls. A CUSTCOUNTY field, for example, must accept nulls because a customer may not know the name of the county in which she lives. (Of course, it will no longer be Null after she supplies the county name.)

Remember that Null does not represent a blank—it represents a missing or unknown value. Users commonly make the mistake of using a blank to represent a meaningful value, such as “None,” “Not Applicable,” “No Response,” and “Not Wanted.” If these values are valid for a particular field, then make sure you include them in the Range of Values element for the field. Above all, use Nulls judiciously and do not use blanks!

Values Entered By

The Values Entered By element indicates the source of a field’s values. Either a user will enter values into the field manually or a database application program will enter them automatically; the application program can provide values for the field only if the person who developed the program provided a means for it to generate the values. Note that the setting that represents the database application program is “System.”

Required Value

The Required Value element denotes whether a user is required to enter a value for a field. Although you’ll typically set this element to “No” for most of the fields in a table, you must set it to “Yes” when the field serves as the primary key. You may also need to set Required Value to “Yes” for a field such as CUSTZIPCODE—a letter or package you send to a given customer must include a ZIP code for the Postal Service to handle it properly and accurately.

Range of Values

The Range of Values element specifies every possible valid value for a field. You can set this element in various ways, such as with a lower and upper limit (1,000 to 9,999) or with a specific list of values (“WA,” “OR,” “ID,” “MT”). You can establish a range of values under three categories:

1. General: A complete collection of every possible value for this field. For example, the general range of values for a CUSTSTATE field might include all valid abbreviations for every state in the United States.

2. Integrity-specific: A collection of values based on the field’s role within a table relationship. (You’ll learn all about this category in Chapter 10.)

3. Business-specific: A collection of values generated by a particular business requirement. Organizations commonly have various requirements that limit the range of values for a field. In an organization that conducts its business strictly in the Pacific Northwest, for example, the valid range of values for a CUSTSTATE field are “WA,” “OR,” “ID,” and “MT.” (You’ll learn more about this category in Chapter 11, “Business Rules.”)

You’re concerned only with the general range of values during this stage of the database design process, and you’ll revisit the Range of Values element later when you establish table relationships and business rules.

It’s important to note that “Other” and “Miscellaneous” are two values that you do not want to set within any category of the Range of Values element. Both values are nonspecific and absolutely meaningless within this context and are a sign of mental laziness in that their very presence indicates a need to review the field for possible refinement. You can avoid unnecessary confusion and potential problems by refraining from using these values.

Edit Rule

This element designates at what point a user can enter a value into a field and whether he can modify that value. You set this element to one of these five options.

1. Enter Now, Edits Allowed: A user must enter a value for this field when she creates a new record in the field’s parent table. She can then edit the value at any time.

2. Enter Later, Edits Allowed: A user has the option of entering a value for this field when creating a new record in the field’s parent table. This does not imply in any way that the field’s value can be Null for all time; the user must enter a value for this field at some point in the near future. After entering the value, she can then edit it at any time.

3. Enter Now, Edits Not Allowed: A user must enter a value for this field when she creates a new record in the field’s parent table, but she cannot edit it at any time whatsoever.

4. Enter Later, Edits Not Allowed: A user has the option of entering a value for this field when she creates a new record in the field’s parent table. This does not imply in any way that the field’s value can be Null for all time; the user must enter a value for this field at some point in the near future. After entering the value, she cannot edit it at any time whatsoever.

5. Not Determined At This Time: The Edit Rule will be determined at the time the database is implemented in an RDBMS program.

Figure 9.7 shows the Logical Elements section of a Field Specifications sheet for an EMPLOYEE ID NUMBER field.