A single relational database can contain any number of tables. This possibility raises questions of how many tables a database should have and what fields belong in what tables. Such decisions are the basis of database design. A good design makes the database easy to work with and provides flexibility to support future requirements. A bad design gets in the way of select, project, and join operations, making the database inflexible and hard to work with.

Like so many things in life, database design is part art and part science. You can approach database design informally, armed with experience and rules of thumb, or formally, applying rigorous scientific methods. First, the informal approach.

The approach detailed in the following steps—each described in a separate section—will generally produce a fairly workable database design. This is the approach most database designers use for real work. The formal approach described later serves more often to check and validate an informal design than to generate a design from scratch.

A true relational database is one that follows the so-called relational model first articulated by E. F. Codd, a research fellow at IBM. The relational model describes databases, tables, records, fields, and operators such as select, project, and join in a formal, mathematical way. One of the relational model’s great strengths is that it’s mathematically complete—a term that in common English means it contains no inconsistencies or missing links. The result is a database model with unprecedented applicability to an extremely wide range of problems.

In relational technology, the process of organizing fields into tables is called normalization because with each step, the design approaches the relational standard (that is, the relational norm) more closely. Experts have proposed many degrees of normalization, but everyone seems to agree on the first five:

As you might have noticed, the normal forms are more like auditing rules than procedures. In theory, you could start with any random assortment of fields, repeatedly apply the normalization rules, and end up with a logically correct design. In practice, this is extremely tedious and boring.

Achieving normal form is generally a matter of breaking large tables into multiple smaller ones until all data in each table is clearly and uniquely associated with other data in the same table. This approach makes the data easier to maintain and the application easier to code. You can always use the join operation to reassemble tables you’ve split apart in the pursuit of normalization.

After they’ve designed and normalized a number of databases, most developers acquire a certain mindset and no longer subject each table to a sequential normalization process. When the need to accommodate another field develops, they instinctively put it in the right table or, if required, create a new table. Such is the nature of experience.

Access databases consist of a single file with an .mdb filename extension. No matter how many tables, queries, forms, reports, macros, and modules you create, they all reside within that one file. Therefore, the first step in any Access project is to initialize a blank database. Here’s the procedure:

Access 2002 provides three ways to create a new table. Each way offers a different degree of automation, but they all produce essentially the same results.

Among these three options, Design View provides the least automation but the most control over results. Design View is also the best way to add, modify, or delete fields in existing tables. The following example, therefore, uses Design View:

Soon after creating a table, you’ll probably want to put some data in it. There are several ways to do this. To add the data to the members table, follow these steps:

If you’re working with data that already exists in another source, you can paste it into any table you’ve opened in Datasheet view. To paste data from a spreadsheet, follow these steps:

Datasheet view is also quite handy for changing and deleting records. To change a record, highlight the data in any field and just start typing. To delete a record, click anywhere within it and then choose Delete Record from the Edit menu.

Most applications require the use of several—perhaps even dozens—of tables. Although not required, it’s usually a good idea to document the relationships you intended these tables to have when you designed them. If you like, Access can then enforce these relationships and maintain consistency among your tables.

The next graphic shows the structure of the ads table. These tables are related through the memberid field in two ways:

Here’s the procedure to tell Access about this relationship:

Access uses relationship information in two ways: First, whenever you select two or more tables for making a query, the default relationships will be the ones you defined in the Relationships window.

The second use of relationships occurs only if you select the Referential Integrity option. Doing so tells Access that no records should exist in the table named in the Related Table/Query (right) side of the Edit Relationships dialog box, unless a matching record exists in the corresponding table named in the Table/Query (left) side. If this seems confusing, refer again to the previous graphic and consider the following points:

Relationships and their implementation involve complex ideas that might take you some time to appreciate, let alone debug. Suppose, for example, that you established the cascading deletes between the member and ads tables. If an administrator then deleted a member record, Access would automatically delete all that member’s ads records. If the administrator then re-created the member record, the ads records would still be missing. You could stare at the member maintenance code for hours and never suspect it would affect the ads table. Regardless of the type of database, referential integrity, cascading updates, and cascading deletes are famous for their unexpected consequences.

Given all that, you might choose not to deal with relationships at all. As your experience grows, however, you’ll find relationships a more and more valuable tool, and it’s good to know that Access is ready when you are.

Given the frequent mention thus far of the relational operators select, project, and join, you might be wondering how Access makes use of them. The following example joins the members table and the ads table based on memberid, projects some of the fields into a grid, and selects all records from the results. Here’s the procedure:

Many queries involve record selection criteria, or as you might call them, search values. In SQL parlance, this is a select. To make the query you just created perform a select operation, enter the value you want in the Criteria row of the Select Query window shown in step 6 of the "Create a query" procedure earlier in this chapter.

Selecting based on constants is a valuable technique, but it’s sometimes awkward for recurring use. Specifically, every time you want to query a different value, you have to open Design view, locate the proper column, and update the Criteria value. It would be much more convenient to tell Access that it needed to prompt you for one or more Criteria values every time it ran the query. Access could then run the query with the values you specified. Parameter queries provide exactly this facility.

To see a parameter query in action, refer to the next graphic. This query retrieves all records from the members table having a given value in the lastname field. A parameter named qlastname specifies the desired value. The entry that makes this happen is the value [qlastname], where the Criteria row and the lastname column intersect.

The square brackets around qlastname identify this entry as a database field. If you entered just the characters qlastname in the Criteria row, Access would, by default, treat your entry as a constant, surround it with quotation marks, and retrieve all member records with an lastname value of qlastname. Surrounding an expression with square brackets tells Access (and SQL) that the expression names a database field.

Of course, qlastname isn’t the name of any field in any table involved in the query. Because of this, Access assumes that qlastname is the name of a parameter you plan to supply whenever you run the query. If you’ve ever hand-typed a field name, misspelled it, and then wondered why Access displayed an Enter Parameter Value prompt, now you know.

The next graphic shows what happens when you try to run this query (that is, when you switch the query window to Datasheet view): the query won’t run until you supply a value for the qlastname parameter.

If you supply the value Shen, the query displays only records having that value in the lastname field. The next graphic illustrates this result.

Chapter 7 explains how to run a parameter query from a Web page. This approach has two advantages:

The primary disadvantage of using stored parameter queries in Web pages is the additional complexity required to invoke them. Chapter 7 explains this as well.

The keyword SELECT identifies the command, initiates it, and identifies the fields the command should return. Here’s how to code the arguments for a SELECT statement:

FROM identifies the table or join to use in formulating the result set. To select fields from an existing table, specify the table’s name. To select from a join of two tables, specify an expression that looks like this:

FROM left-table   jointype   right-table
  ONleft-table.joinfield = right-table.joinfield

That is, for example:

FROM ads INNER JOIN members
  ON ads.memberid = members.memberid

There are three common JOIN types:

Oracle uses a completely different syntax for joins. When using that DBMS, you just mention the two tables in a FROM clause and then specify an equality expression in the WHERE clause. Here’s an example.

 FROM ads, members
WHERE ads.memberid = members.memberid

This performs an inner join. To perform a left join, you code the characters (+) on the right side of the equality expression.

 FROM ads, members
WHERE ads.memberid = members.memberid (+)

To perform a right join, put the (+) on the left side of the equality expression (just before the equal sign).

WHERE specifies criteria for selecting records. Most criteria have the format field name, operator, value, as in

members.lastname = 'Shen'

As shown in the preceding code, single quotation marks identify text constants. Access demands that pound signs identify date constants, as in #12/31/1999#, but SQL Server and Oracle use apostrophes. Numeric constants have no surrounding delimiters, as in year = 1950.

You can group criteria using parentheses, the AND operator, and the OR operator.

GROUP BY, when present, tells the database system to consolidate similar records based on equal values in a supplied list of fields. If you specify GROUP BY firstname, lastname, the database system will, before presenting the result set, consolidate all records having the same set of values in those fields.

HAVING works a lot like WHERE, except that it operates after applying the GROUP BY clause. Suppose, for example, you code the following SELECT statement for the ads table in the classified ads database:

SELECT firstname, lastname, sum(askingprice) as totprices
  FROM ads
 GROUP BY firstname, lastname

If you coded HAVING totprices > 1000, the database system would first aggregate all ads records by firstname and lastname, and then, from those results, select any record whose aggregate totprices value was greater than 1,000.

ORDER BY controls the order of records in the result set. You can specify any fields in the result set, separated by commas and in any order, and the database system will sort the result set accordingly: first field first, second field second, and so forth. To sort any field in descending sequence, specify the keyword DESC after its field name. Here’s a typical ORDER BY clause:

ORDER BY lastname, firstname, askingprice DESC

The DELETE statement is one of the easiest SQL statements to code. All you need to know is the name of the table that contains the data and the field values that describe it. Here’s an example:

DELETE FROM ads WHERE askingprice > 50000

The syntax of the WHERE clause is exactly the same as it is for SELECT statements. Be careful when coding this clause, however, and especially careful if, for whatever reason, you omit it. There’s no limit to the number of records a DELETE statement can delete!

The general form of an INSERT statement appears below. The order of the fields and values isn’t significant, except that you must code both of them in the same order.

INSERT tablename (fieldname_3,  fieldname_1,  fieldname_2)
          VALUES (   'value_3',    value_1,     #value_2#)

If you omit the list of field names, the database system will expect the value list to contain one value for each field in the database. It will also expect these values to appear in the same order that the corresponding fields appear in the table definition. This requirement often leads to unexpected consequences when, during the life of an application system, someone adds or rearranges fields. For this reason, coding the field names is usually the safe choice.

Note that the example shows single quotation marks around a string value, pound signs around a date value, and no delimiter around a numeric value. In fact, the required notation varies from one DBMS to another. SQL Server, for example, uses single quotes rather than pound signs around dates, and some DBMSs accept or require single quotes around numeric values as well. Resolving such issues might require a bit of trial and error or consultation with the documentation.

A problem arises when you try to insert string values that contain apostrophes. In the following statement, for example:

INSERT ads (headline, askingprice, memberid)
VALUES ('Can't miss opportunity', 2500, '[email protected]')

the DBMS will interpret ’Can’ as a value and t miss opportunity’ as illegal characters in the SQL statement. To avoid this problem, replace all apostrophes in your data with double apostrophes, as shown in the following example. Only one apostrophe will be present when you later retrieve the data.

INSERT ads (headline, askingprice, memberid)
VALUES ('Can''t miss opportunity', 2500, '[email protected]')

Finally, notice that this INSERT statement specifies neither a field name nor a value for the adid field because adid is an AutoNumber field and Access automatically supplies a value that you can’t override.

Here’s a typical UPDATE statement. The name that follows the UPDATE command specifies the table you want to update. The SET clause specifies the values you want to set, and the WHERE clause specifies the records you want to update.

UPDATE ads
    SET ads.askingprice = 150, memberid = '[email protected]'
    WHERE ads.adid = 1

There’s no need to include SET expressions for fields you don’t want to update. The example, above, for example, makes no change to the headline field. As with the INSERT statement, you must code correct delimiters around any values.

As with the DELETE statement, there are no limits to the number of records an UPDATE statement can affect. If you forget the WHERE clause, an UPDATE statement modifies every record in the given table. If you want to modify only one record, the WHERE clause should specify a unique key.