Creating Your Own Database and Objects

Designing Databases

Data Analysis and Design

After you have analyzed and documented all the tasks involved in a system, you're ready to work on the data analysis and design phase of an application. In this phase, you must identify each piece of information needed to complete each task. You must assign these data elements to subjects, and each subject will become a separate table in the database. For example, a subject might be a client; every data element relating to that client—the name, address, phone number, credit limit, and any other pertinent information—would become fields within the client table.

You should determine the following for each data element:

• The appropriate data type

• The required size

• Validation rules

You should also determine whether you will allow the user to update each data element and whether the user will enter each update or the system will calculate it. Then you can figure out whether you have properly normalized the table structures.

Database Terms Introduced

In the preceding hours we have briefly mentioned some terms that are integrally important now that you will be designing your own objects. The following are the most important of these terms and what they mean:

• Column or field— A single piece of information about an object (for example, a company name).

• Row or record— A collection of information about a single entity (for example, all the information about a customer).

• Table— A collection of all the data for a specific type of entity (for example, all the information for all the customers stored in a database). It is important that each table contain information about only a single entity. In other words, you would not store information about an order in the Customers table.

• Primary key field— A field or a combination of fields in a table that uniquely identifies each row in the table (for example, the CustomerID).

• Natural key field— A primary key field that is naturally part of the data contained within the table (for example, a Social Security number). Generally it is better to use a contrived key field, such as an AutoNumber field, than a natural key field as the primary key field.

• Composite key field— A primary key field comprising more than one field in a table (for example, LastName and FirstName fields). It is preferable to create a primary key based on an AutoNumber field than to use a composite key field.

• Relationship— Two tables in a database sharing a common key value. An example is a relationship between the Customers table and the Orders table: The CustomerID field in the Customers table is related to the CustomerID field in the Orders table.

• Foreign key field— A field on the many side of the relationship in a one-to-many relationship. Whereas the table on the one side of the relationship is related by the primary key field, the table on the many side of the relationship is related by the foreign key field. For example, one customer has multiple orders, so whereas the CustomerID field is the primary key field in the Customers table, it is the foreign key field in the Orders table.

Testing As far as testing goes, you just can't do enough. I recommend that if an application is going to be run in Windows 98, Windows NT, Windows 2000, and Windows XP, you test in all these environments. I also suggest that you test an application extensively on the lowest-common-denominator piece of hardware so you can ensure that it will work on all the machines in the environment; an application might run great on your machine but show unacceptable performance on a user's slower machine. It usually helps to test your application both in pieces and as an integrated application. Recruit several people to test your application and make sure they range from the most savvy of users to the least computer-adept person you can find. These different types of users will probably find completely different sets of problems. Most importantly, make sure you're not the only tester of your application because you're the least likely person to find errors in your own programs.

Creating Tables

Building a New Table

There are several ways to add a new table to an Access 2003 database: You can use a wizard to help you with the design process, design the table from scratch, build the table from a datasheet (a spreadsheet-like format), import the table from another source, or link to an external table. This hour discusses the processes of building a table from a datasheet and designing a table

Building a Table from a Datasheet

Building a table from a datasheet might seem simple, but it isn't always the best way to build a table because it's easy to introduce severe design flaws into a table. Microsoft added this method of building tables as an "enhancement" to Access—and it is primarily for spreadsheet users getting their feet wet in the database world. I suggest you use one of the other methods to design tables. If you decide to use the datasheet method, though, follow these steps:

1. Select the Tables icon from the list of objects in the Database window.

2. Click the New button. The New Table dialog box appears.

3. Select Datasheet View from the New Table dialog box.
   

4. Rename each column by double-clicking the column heading (for example, Field1) you want to change or by right-clicking the column and selecting Rename Column from the context menu. Type a name for the field and then press Enter.

5. Enter data into the datasheet. Be sure to enter the data in a consistent format. For example, if a table includes a column for employee hire dates, make sure all entries in that column are valid dates and that you enter all dates in the same format. Access uses the contents of each column to determine the data type for each field, so inconsistent data entry confuses Access and causes unpredictable results.

6. After you have added all the columns and data you want, click the Save button on the toolbar. Access prompts you for a table name and asks whether you want to add a primary key.

7. Access assigns data types to each field based on the data you have entered. When Access is done with this, click the View button on the toolbar to look at the design of the resulting table.

8. Add a description to each field to help make the table self-documenting.

Selecting the Appropriate Field Type for Data The data type you select for each field can greatly affect the performance and functionality of an application. Several factors can influence your choice of data type for each field in a table: The type of data that's stored in the field Whether the field's contents need to be included in calculations Whether you need to sort the data in the field The way you want to sort the data in the field How important storage space is to you The type of data you need to store in a field has the biggest influence on which data type you select. For example, if you need to store numbers that begin with leading zeros, you can't select a Number field because leading zeros entered into a Number field are ignored. This rule affects data such as zip codes (some of which begin with leading zeros) and department codes.

Appropriate Uses and Storage Space for Access Field Types

Field Type	Appropriate Uses	Storage Space
Text	Data containing text, a combination of text and numbers, or numbers that don't need to be included in calculations. Examples are names, addresses, department codes, and phone numbers.	Based on what's actually stored in the field; ranges from 0 to 255 bytes
Memo	Long text and numeric strings. Examples are notes and descriptions.	Ranges from 0 to 65,536 bytes.

Number	Data that's included in calculations (excluding money). Examples are ages, codes (such as employee IDs), and payment methods.	1, 2, 4, or 8 bytes, depending on the field size selected (or 16 bytes for Replication ID).
Date/Time	Dates and times. Examples are date ordered and birth date.	8 bytes.
Currency	Currency values. Examples are amount due and price.	8 bytes.
AutoNumber	Unique sequential or random numbers. Examples are invoice numbers and project numbers.	4 bytes (16 bytes for replication ID).
Yes/No	Fields that contain one of two values (for example, yes/no, true/false). Sample uses are indicating bills paid and tenure status.	1 bit.
OLE Object	Objects such as Word documents or Excel spreadsheets. Examples are employee reviews and budgets.	0 bytes to 1GB, depending on what's stored within the field.
Hyperlink	Text or a combination of text and numbers, stored as text and used as a hyperlink for a Web address (uniform resource locator [URL]) or a universal naming convention (UNC) path. Examples are Webpages and network files.	0 to 2,048 bytes for each of the three parts that compose the address (up to 64,000 characters total).

The All-Important Primary Key

A primary key is a field or a combination of fields in a table that uniquely identifies each row in the table (for example, CustomerID). The most important index in a table is called the primary key index; it ensures uniqueness of the fields that make up the index and also gives the table a default order. You must set a primary key for the fields on the one side of a one-to-many relationship. To create a primary key index, you select the field(s) you want to establish as the primary key and then click the Primary Key button on the toolbar.

Creating Queries

Ordering Query Results When you run a new query, the query output appears in no particular order. Generally, however, you want to order query output. You can do this by using the Sort row of the query design grid. To order the results of a query, follow these steps: In Design view, click within the query design grid in the Sort cell of the column you want to sort. Use the drop-down combo box to select an ascending or descending sort. Ascending or Descending appears in the sort cell for the field, as appropriate. Sorting by More Than One Field You might often want to sort query output by more than one field. The columns you want to sort must be placed in order, from left to right, on the query design grid, with the column you want to act as the primary sort on the far left and the secondary, tertiary, and any additional sorts following to the right. If you want the columns to appear in a different order in the query output, you must move them manually in Datasheet view after you run the query. Task: Ordering Query Results To sort query results in ascending order by the ContactTitle field, follow these steps: In Design view, click the Sort row of the query design grid for the ContactTitle field. From the Sort drop-down combo box, select Ascending. Run the query and view the results. The records should now be ordered based on the ContactTitle field. If you want to return to Design view of the query, click View on the toolbar. Sort the query output by the Country field and, within individual country groupings, by the ContactTitle field. Because sorting always occurs from left to right, you must place the Country field before the ContactTitle field. Select the Country field from the query design grid by clicking the thin gray button above the Country column. After you have selected the Country field, move the mouse back to the thin gray button and click and drag to the left of the ContactTitle field. A thick gray line should appear to the left of the ContactTitle field. Release the mouse button. Change the sort order of the Country field to Ascending. Run the query. The records should be in order by country and, within the country grouping, by contact title.

Refining a Query by Using Criteria

So far in this hour, you have learned how to select the fields you want and how to indicate the sort order for query output. One of the important features of queries is the ability to limit output by using selection criteria. Access allows you to combine criteria by using several operators to limit the criteria for multiple fields. Table covers the operators and their meanings.

Table Access Operators

Operator	Meaning	Example	Result of Example
=	Equal to	="Sales"	Finds only records with "Sales" as the field value.
<	Less than	<100	Finds all records with values less than 100 in that field.
<=	Less than or equal to	<=100	Finds all records with or equal tovalues less than or equal to 100 in that field.
>	Greater than or equal to	>100	Finds all records with values greater than 100 in that field.
>=	Greater than	>=100	Finds all records with or equal to values greater than or equal to 100 in that field.
<>	Not equal to	<>"Sales"	Finds all records with values other than Sales in the field.
And	Both conditions must be true	Created by adding criteria on the same line of the query design grid to more thanone field	Finds all records where the conditions in both fields are true.
Or	Either condition can be true	"CA" or "NY" or "UT"	Finds all records with the value "CA", "NY", or "UT" in the field.
Like	Compares a string expression to a pattern	Like "Sales*"	Finds all records with the value "Sales" at the beginning of the field (the asterisk is a wildcard character).
Between	Finds a range of values	Between 5 and 10	Finds all records with the values 5–10 (inclusive) in the field.
In	Same as Or	In("CA","NY","UT")	Finds all records with the value "CA", "NY", or "UT" in the field.
Not	Same as <>	Not "Sales"	Finds all records with values other than Sales in the field.
Is Null	Finds nulls	Is Null	Finds all records where no data has been entered in the field.
Is Not Null	Finds all records that are not null	Is Not Null	Finds all records where data has been entered into thefield.

Updating Query Results If you haven't realized it yet, you can usually update the results of a query. This means that if you modify the data in the query output, Access permanently modifies the data in the tables underlying the query. To see how this works, follow these steps: Build a query based on the Customers table. Add the CustomerID, CompanyName, Address, City, and Region fields to the query design grid and then run the query. Change the address of a particular customer and make a note of the customer ID of the customer whose address you changed. Make sure you move off the record so that Access writes the change to disk. Close the query, open the actual table in Datasheet view, and find the record whose address you modified. The change you made was written to the original table; this is because a query result is a dynamic set of records that maintains a link to the original data. This happens whether you're on a standalone machine or on a network. Building Queries Based on Multiple Tables If you have properly normalized your table data, you probably want to bring the data from your tables back together by using queries. Fortunately, you can do this quite easily by using Access queries.