Many computer books contain long laundry lists of software features, with hastily scribbled
In contrast, in this book we present the software in terms of business solutions. Accordingly, many of the chapters contain at least one business case, in which a fictional company pursues the
Business Case 1.1: Introducing Jones Novelties, Incorporated
The company's CEO, Brad Jones, recognizes that, for Jones Novelties, Incorporated, to succeed, it must automate many of its transactions. These include customer contacts, inventory, and billing systems, and implementation must be tailored to the business and flexible enough to change over time.
Jones recognizes that the company will rise or fall on the basis of its access to information, so he decides to use a relational database system to manage the company's information. The design and functionality of such a database is the focus of the rest of this chapter.
Tables and Fields
Databases consist of
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
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).
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
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
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
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
A guiding principle in database table design is to put fields
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:
Jones figures that all this information should go into a single table, to keep the database simple. His database developer
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
Similarly, splitting the Name field into FirstName and LastName fields will make it easier to
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
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
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