Tables and Fields

Databases consist of tables, which represent broad categories of data. If you were creating a database to handle the accounts for a business, for example, you might create one table for customers, another for invoices, and another for employees. Tables have predefined structures containing data that fits into them.

Tables contain records, which are individual pieces of data within a broad category. For example, a table of customers contains information pertinent to the people that make up the client base of a business. Records can contain almost any type of data and are retrieved, edited, and deleted through the use of stored procedures and/or queries written in Structured Query Language (SQL).

Records comprise fields. A field represents a subdivision of data in a record. A record that represents an entry in an address book might consist of fields for a customer's first and last name, address, city, state, zip code, and telephone number.

You can use VB.NET code to refer to and manipulate databases, tables, records, and fields. One of the new features of database programming with VB.NET that deserves attention is how strongly it enforces the correct datatype. For example, available now are methods such as get String() and getInt(), which help reduce coding by formatting the data being retrieved by specifying its datatype when the data is gathered from the database.

Designing Your Database

To create a database, you must first determine the information that it is to keep track of. You then create table definitions comprising fields that define the types of data you'll store. After you create this structure, the database can then store data in the form of records.

You can't add data to a database that has no table or field definitions because the database has nowhere to store the data. So the design of the database is crucial, particularly because changing the design of a database can be difficult once you've implemented it.

In this book we present tables in a standard format, with the table's name at the top and the list of field names beneath, as follows.

tblMyTable

ID

FirstName

LastName

…

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.

tblCustomer

ID

FirstName

LastName

Company

Address

City

State

PostalCode

Phone

Fax

E-mail

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.

tblRegion

ID

State

RegionName

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.

tblOrder

ID

CustomerID

OrderDate

Amount

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).