Business Cases

The vertical ellipsis (dots) in the last field indicates that this table has one or more fields that we omitted for the sake of brevity.

If you're new to the world of database programming but have used other computer applications, you might be surprised that a database application makes you go through a few additional steps before you can start entering data. A word processing application, for example, enables you just to open the application and type; the details of how the file is saved are hidden in the application itself. The main reason for designing databases ahead of time is efficiency. If a computer application knows exactly how much and what kinds of data to store, it can store and retrieve those data optimally. As you'll learn after you create your first 100,000-record multiuser database, speed is of paramount importance in the database environment. Anything you can do to speed the process of adding information to and retrieving it from the database is worthwhile.

A guiding principle in database table design is to put fields related to the same category of data in the same table. Thus all customer records go in a Customer table, the orders that those customers place go in an Orders table, and so on.

Just because different sets of data go into different tables doesn't keep you from using them together—quite to the contrary. When the data you need is spread across two or more tables in a relational database, you can access that data by using a relationship. Later in this chapter we discuss relationships; for now, we focus on table design.

Business Case 1.2: Designing Tables and Relationships

Brad Jones has determined that Jones Novelties, Incorporated, requires a way to store information on customers. He's reasonably sure that most orders will be repeat business, so he wants to be able to send customers catalogs twice a year.

Jones scribbles a basic database schema on a cocktail napkin. "Here's what the business needs to keep track of," he says:

• The customer's name, address, city, state, zip code, and phone number

• The customer's region of the country (Northwest, Southwest, Midwest, Northeast, South, or Southeast)

• The date of the customer's last purchase

Jones figures that all this information should go into a single table, to keep the database simple. His database developer tells him that might be possible but that he would end up with an inefficient, disorganized, and extremely inflexible database.

The information that Jones wants to include doesn't all map directly to database fields. For example, because a region is a function of a person's state of residence, it doesn't make sense to have a State field and a Region field in the same table. Doing so would mean that a data-entry person would have to enter similar information on a customer twice. Instead, it would make much more sense for the database to store a State field in the Customer table and store information pertaining to regions in a separate Region table. If the Region table always knows which states map to which regions , the data-entry person doesn't have to enter a region for each customer. Instead, he can just enter the name of the state, and the Customer table can work with the Region table to determine the customer's region.

Similarly, splitting the Name field into FirstName and LastName fields will make it easier to sort on those fields once data has been entered into them. This aspect of the design might seem trivial, but surprisingly, many database developers don't take it into consideration. Recovering from this kind of design flaw in a production database is awfully hard.

So Jones and his associate determine that data on the company's customers should be stored in a table called tblCustomer that contains the following fields.

Data pertaining to the various regions of the country is to be stored in a separate table called tblRegion. This table contains the following fields.

The two tables are related by the State field, which exists in both tables. The relationship between the Region table and the Customer table is a one-to-many relationship; for each record in tblRegion there can be none, one, or many matching records in tblCustomer. (In the sections on relationships later in this chapter we discuss in detail how to take advantage of such a relationship for retrieving records.)

Note how the database developer named the tables and fields in her preliminary table designs. First, she named each table with the prefix tbl. Doing so enables her to distinguish, at a glance, a table from another type of database object that can also store records. Next, note that each field name consists of full words (instead of abbreviations) and doesn't contain spaces or other special characters such as underscores.

Although SQL Server enables you to name database objects with spaces, underscores, and other nonalphanumeric characters, it's a good idea to avoid their use. Using them makes it difficult to remember the exact spelling of a field name later. (You won't have to remember whether the field is named FirstName or FIRST_NAME, for example.) Although this guideline seems like a trivial distinction now, when you start writing code against a database consisting of 50 tables and 300 fields, you'll appreciate having named things simply and consistently from the beginning.

One last thing missing from Jones's wish list is the answer to the question, When did this customer last purchase something from us? The database developer decides that this information can be determined from date values in the table that stores data pertaining to customers' orders. This table has the following structure.

In this table, the ID field uniquely identifies each order. The CustomerID field connects an order with a customer. To attach an order to a customer, the customer's ID is copied into the Order table's CustomerID field. That way, looking up all the orders for a particular customer is easy (as we demonstrate later).