Database Diagrams and Tables

A database diagram is a collection of database objects, much like tables, views, and stored procedures are. Access 2000 stores them with the SQL Server or MSDE database file as opposed to the Access Project. While an individual database diagram can resemble the display that appears in the Relationships window of a classic .mdb Access file, client/server database diagrams are much more flexible and offer more granular control over the design of a database. For example, you can initiate the design of new tables from a database diagram window.

Using Database Diagrams to Map Relationships

Figure 12-5 shows the database diagram for the NorthwindCS database. As with the Relationships window in traditional Access files for Jet databases, it depicts the tables in the database design and the relationships among them. You can fine-tune the relationship between two tables by clicking the line that connects them and choosing Properties from the shortcut menu. The diagram in Figure 12-5 shows the relationship between the Products and Categories tables selected.

Figure 12-5. The database diagram from the NorthwindCS Project.

Figure 12-6 shows the Properties dialog box for the selected relationship in Figure 12-5. The tabs present substantially more information about the relationships between tables than you get from the Relationships window in a classic Access/Jet database file.

Figure 12-6. The Properties dialog box for the Products-Categories relationship in the NorthwindCS database.

You can use the Tables tab to control the validity specifications for data entering the table. You use constraints for this task. To select any constraint to edit or delete it, select it in the Selected Constraint drop-down list box. Click New to add a new constraint for a table. Click Delete to drop the currently selected constraint. The three check boxes at the bottom of the Tables tab determine when a constraint takes effect. The two selections at the top left of Figure 12-6 apply the constraint when a user adds or updates data in the table or when the table is copied to another database. Use the Selected Table drop-down list at the top of the Tables tab to select another table and add constraints for it.

The Relationships tab initially shows the relationship between the Products and Categories tables. However, you can use the Selected Relationship drop-down list box to examine the relationships that the Products table shares with the Suppliers table or the Order Details table. To accomplish the same result with a .mdb file, you must explicitly select that relationship from the Relationships window.

The Indexes/Keys tab shows the settings for the table's existing keys and indexes and lets you add new ones or delete existing ones. The ProductID field defines the primary key for the Products table. The index is clustered, which means that the physical order and the logical order of the rows are identical.

Managing Multiple Database Diagrams

Figure 12-7 highlights another potential use for database diagrams. It shows three different diagrams, all for the same database. Classic .mdb files do not permit multiple diagrams depicting the relationships in a database; each file has a single Relationships window. With SQL Server, however, you can have multiple database windows. Database diagram windows can have mutually exclusive or overlapping content. For example, both the FamilyMembers table and the AdditionsToFamilyMembers table appear in the MyDiagram and JustFamilyDiagram windows. You can copy a window to the Clipboard and paste it into another window. Since you can secure database diagram windows, you can control the views of a database that different groups of users have.

Figure 12-7. Three database diagram windows for a single database.

As you can see in Figure 12-7, you can add labels to diagrams to identify one or all of the tables. To modify the font style, size, color, or other features of a label, right-click in a label box to open the Font dialog box.

You can list the database diagram windows within a database by enumerating the members of the AllDatabaseDiagrams collection. Your programs can also read the IsLoaded property of the collection's members to report whether the window is open. However, you cannot set the IsLoaded property to open and close database diagram windows. The AllDatabaseDiagrams collection belongs to either the CurrentData or CodeData object within an application.

The following procedure writes out a list of all the database diagram windows and indicates whether they are open:

 Sub listDatabaseDiagrams() Dim obj As AccessObject, dbs As Object 'Set the hierarchical container for AllDatabaseDiagrams.     Set dbs = Application.CurrentData 'List database diagrams by whether they are loaded.     For Each obj In dbs.AllDatabaseDiagrams         If obj.IsLoaded = True Then             Debug.Print obj.Name; " is loaded."         Else             Debug.Print obj.Name & " is not loaded."         End If     Next obj End Sub 

Editing Tables in Database Diagram Windows

You can edit tables and enter new tables from either a database diagram window or the Tables Objects collection in the Database window. Each approach has merit for particular tasks.

Database diagram windows are great for performing data definition tasks graphically or in a table's Properties dialog box. To designate a primary key, you select the field's row selector in the database diagram. If your primary key relies on more than one field, hold down the Ctrl key as you select other fields that also define the primary key. Right-click the selected row or rows and choose Primary Key from shortcut menu to complete the task.

Database diagram windows are also helpful for defining relationships. Start by selecting the row selector for the primary key in the first table. You do not have to use a primary key, but the field must have unique values. Next, drag the selected field from the first table and drop it on the title bar for the second table. The Create Relationship dialog box (which resembles the top right panel in Figure 12-6) will appear. Modify the relationships specifications as needed, and then click OK.

You can enforce referential integrity using three check boxes in the lower part of the Create Relationship dialog box and on the Relationship tab of the Properties dialog box (shown in Figure 12-6). Select the Enable Relationship For Insert And Update check box to enforce referential integrity for all new data in the current database. If your existing data already obeys referential integrity or if it doesn't need to follow referential integrity, leave the Check Existing Data On Creation check box deselected. If following referential integrity in another database that will receive rows from your database will cause an unacceptable number of records to fail to transfer (because of key violations), leave the Enable Relationship For Replication check box deselected.

Editing and Creating Tables Using a Worksheet

You can add a new table to a database by right-clicking in a database diagram window and choosing New Table. After you accept the default system-generated name or assign a new name, you'll see a worksheet for naming fields, assigning data types, and making other data definition specifications. This same worksheet opens from the Tables collection in the Database window when you click New or Design. It exposes several important user-friendly table and field properties.

Figure 12-8 shows a pair of worksheets for the FamilyMembers and MyLInks tables from Figure 12-7. Actually, the relationship was updated from Figure 12-7 so that there is a one-to-many relationship between the tables. The FamID field in MyLInks relates to the primary FamID field in FamilyMembers. You can create a foreign key in one table that exactly matches a primary key in another table, just as you do in a classic Access .mdb file. Copy the primary key to the Clipboard from the first table and paste it into the second table.

Figure 12-8. Two worksheets that show settings for defining fields in tables for MSDE and SQL Server databases.

The FamID primary key in FamilyMembers has the SQL Server Integer data type. This corresponds to the Long data type in Access/Jet databases. The LinkID primary key in MyLInk uses the Integer data type with an Identity column setting. Notice that this interface, unlike Access with Jet, provides a graphical interface for setting the identity start and step values. The nvarchar data type is a special designation for Unicode variable-length string fields. The comparable non-Unicode data type is varchar. Again, Access with Jet does not expose this data type distinction.