Identifying the Data for Tables

Before designing the tables, indexes, and views of the database, you must determine what the data is. Where will you get the data? How will the data be used? Consider the following test case.

Integrated Peripherals Incorporated uses purchase order documents that track the purchasing of parts from external vendors. The goal of the Data Management Group is to create an automatic process for tracking purchases, paying accounts, mapping external parts to internal parts, and keeping track of internal part stock levels.

Figure 2-1 “Identifying Data Categories” shows the purchase order used by Integrated Peripherals.

Figure 2-1 Identifying Data Categories

You can use similar documents as a starting point for designing the tables in your own databases. Data from the purchase order can be arranged in categories.

Once the data has been identified, it must be broken up into tables. This is done by identifying entities, attributes, and keys within the data. An entity is a category of information to be tracked by the database; an attribute is a characteristic of an entity; and a key is one or more attributes that uniquely identify a member of an entity.

For example, on the purchase order for Integrated Peripherals, the database designer might identify the following general groups of information as entities:

The Vendor Information entity has several attributes, including vendor number, vendor name, vendor address, sales contact, and phone number. The vendor number attribute might be identified as a key for the Vendor Information entity, since it is a unique code that distinguishes vendors from one another.

Make a list of the entities used by your organization in creating applications. For each of the entities, list the attributes, including keys. Refer to Figure 2-2 “Entities, Attributes, and Keys” for the entities, attributes, and keys taken from the purchase order used by Integrated Peripherals.

Figure 2-2 Entities, Attributes, and Keys

The entities become tables, and the attributes and keys become the columns in the tables. Each entity must have a unique value or combination of values for each row in the database table in which it will appear. This value or combination is known as the primary key.

Note that the key for order item information is composed of two items. This is because neither the Item Number nor the Order Number is significant by itself. Remember that a primary key must be unique. Item number alone cannot be the primary key because every order can have an item #1. The order number alone cannot be the primary key either because one order can have several items, forcing the order number to be repeated (not unique). Therefore, the two pieces of information are combined to yield a unique key.

Once the data has been defined and put into groups, it can be normalized to produce the tables that will become the database.