Database Tools and Technologies

Workflow Designer for SQL Server uses Microsoft® SQL Server™ as the data store. SQL Server can support hundreds to thousands of simultaneous users. However, if you are building either a single-user application or a multi-user application for a small team, you can store the data in a Microsoft® SQL Server™ 2000 Desktop Engine database. The desktop engine is available on the Microsoft® Office XP CD-ROM.

A desktop engine database is compatible with SQL Server, but a desktop engine database cannot support as many users. Best performance with the desktop engine is achieved with five or fewer users. The advantage of using the desktop engine is that you can create a SQL Server database from within Microsoft® Access without having SQL Server on your computer. The desktop engine is a good tool for prototyping and designing an enterprise application that you can migrate to SQL Server, because you can run a desktop engine database under SQL Server without modification.

Note   To create a workflow application using Workflow Designer for SQL Server, you must use either SQL Server or Microsoft® SQL Server™ 2000 Desktop Engine. Access .mdb databases are not supported. To use Access to design and modify your SQL Server database, you create an Access data project.

Client/Server Applications

In previous versions of Access, the only way to create a client/server application was to create an .mdb file with linked tables that used an ODBC driver to link to a database server, such as SQL Server. This kind of client/server application also required Access to load the Microsoft® Jet database engine to open the database and the linked tables, which created additional memory overhead.

Although Access continues to support client/server applications that use linked tables, it also supports a new file format and data access architecture that makes it possible for you to create a client application that connects to a SQL Server through OLE DB without loading the Jet database engine. To do this, you create an Access project file that is saved by using an .adp extension.

An Access project can store forms, reports, data access pages, macros, and Microsoft® Visual Basic® for Applications (VBA) modules locally in your client application file and use the OLE DB connection to display and work with the tables, views, relationships, and stored procedures that are stored on SQL Server. You create the forms, reports, macros, and VBA modules in an Access project by using most of the same tools and wizards you use to create these objects in Access databases. This makes it possible for you to develop client/server applications quickly that work directly against a SQL Server database.

Access also makes it possible for you to create new SQL Server databases and provides a variety of visual tools to create and modify the design of tables, views, stored procedures, triggers, and database diagrams on your database server. The tables, views, and stored procedures you create, as well as SQL SELECT statements, are all valid data sources for Access forms, reports, and data access pages.

In addition to providing you with the ability to create and design client/server applications from scratch, Access also includes the Upsizing wizard, which makes it possible for you to convert an existing Access database to a client/server application by creating a new SQL Server database linked to an Access client application.

With the addition of Workflow Designer for SQL Server, you can add workflow and security features, as well as the ability to create a template of your database application.

Relational Database Design

Regardless of which data store you choose, designing the database structure is likely to be the most challenging part of building the relational database application. To understand how the tables in the database should be structured and how they should relate to one another, you must understand the data. Although it is not difficult to modify the data model while you are developing the application, it is much more challenging after your customers are using the application. Therefore, it is important to put as much effort as necessary into the process of designing the data model before you begin writing code.

One way to start is to think of all the questions this database must answer. The answers become the columns (or fields) in your tables. How many corporate customers do we have by country? This could require the following columns: customer, country.

In addition to determining the columns in your tables, you must set up the relationships between tables. Sometimes, you have what appears to be a straightforward one-to-many relationship that turns out to be much more complex.

The following example illustrates some of the issues you must consider when designing a relational database.

Note   PK = Primary Key, FK = Foreign Key

Relational Database Design Example: Phase I

The simple one-to-many design of a Customers table with a single relationship to the Orders table has several limitations. For example, all the product information would have to be re-entered on each order. That would slow down the order entry process and could cause problems as products are added or changed.

Relational Database Design Example: Phase II

Adding a Products table to relate product information to the Order table is better, but there is still a major limitation. You can place only one order per order number. Therefore, another table is required to handle the order details.

Relational Database Design Example: Phase III

Adding an OrderDetails table and relating the Products table to this new table handles the relationship better. You can maintain and update the products separately, and each order can have one or many order details. Notice that total is still stored in the Orders table. This column could be updated using code, after the order total was calculated for good record keeping.

The design of your database itself enforces certain rules on the way users can enter data. For example, a user cannot violate a table's primary key by adding a duplicate record. In addition, you can establish custom validation rules using triggers that prevent users from entering invalid data.

The Workflow Designer for SQL Server automatically tracks foreign-key constraints when you add a main table to the table hierarchy and creates a tree of detail and lookup tables. If you have other related tables for which there are no foreign-key constraints, you can add those as lookup tables manually using the Workflow Designer for SQL Server.

Data Retrieval, Analysis, and Presentation

After you have designed the data-storage and data-entry components of your application, you should begin thinking about how to present and summarize the data in a format that makes sense to users. Although generally not as difficult as database design, determining what data users want to see and building reports to display the data in a usable format can be a challenging task. Preplanning your reports also can lead you to rethink or enhance your database design. Sometimes, the lack of a certain column required for grouping or sorting does not become obvious until the reports are being designed.

Here are some questions to ask yourself as you design the reporting component of an application:

• Must the report be linked dynamically to the data source, or can it be a static report? If the report must display the most current data, it should be linked dynamically to the data source. On the other hand, if the data is not updated frequently or if the report must be re-created regularly because the structure of the underlying data source changes, you can create a static report.
• Must users interact with the data in the report, or can the report be read-only? If users must perform calculations on the data or manipulate the data to display it in novel ways, you might want to create the report in Microsoft® Excel or use Access data access pages to create it in a Web page.
• Must users be able to view the report from a Web page or from within one of the Office applications?

See Also

Planning Workflow Applications for SQL Server | Workflow Application Development Guidelines | Security Permissions Model | The Workflow Engine Model | Developing the User Interface for SQL Server | Using Access to Create the SQL Server Database | Setting Up a Table Hierarchy