Database Tools

Although this book focuses on design rather than implementation, abstract theory isn't of much value unless you know how to apply it; so in this book, we'll be talking a lot about building relational databases using the tools provided by Microsoft. There are a lot of these tools, and Microsoft seems to introduce a new one every time you turn around, so let's take a minute now to look at what all these gadgets are and how they fit together. Figure 1-2 shows the tools we'll be discussing. It's easiest to think about these tools in terms of what we, as developers, need to translate a system from an abstract model to a live production system, and this is how they're grouped in the figure.

Figure 1-2. The database tools discussed in this book.

Database Engines

At the lowest level are the database engines. These are sometimes called "back ends," but that's a bit sloppy since the term "back end" really refers to a specific physical architecture, as we'll see in Chapter 10. These are the gadgets that will handle the physical manipulation of data—storing it to disk and feeding it back on demand. We'll be looking at two: the Jet database engine and SQL Server. You may be surprised not to see Microsoft Access here. Access uses the Jet database engine to manipulate data stored in .mdb files and can link to and manipulate data stored in any ODBC data source, including SQL Server. Access has always used the Jet database engine, although Microsoft didn't expose the engine as a separate entity until the release of Microsoft Visual Basic 3. With the introduction of support for ODBCDirect in Access 97 and support for SQL Server in Access 2000, Microsoft has begun to uncouple the Access front-end tools from the Jet database engine, and I expect this trend to continue in future releases. (But don't quote me.)

The Jet database engine and SQL Server, although very different, are both wonderful tools for storing and manipulating data. The difference between them lies in their architectures and the problems they are intended to address. Microsoft Jet is a "desktop" database engine, intended for small-sized to medium-sized systems. SQL Server, on the other hand, uses a client/server architecture and is intended for medium-sized to huge systems, scalable to potentially thousands of users running mission-critical applications. (Note please that this does not imply that the Jet database engine is appropriate only for trivial systems.) We'll be looking at the differences between the two database engines throughout this book and discussing the trade-offs between the two architectures in Chapter 10.

Data Access Object Models

Both Access and Visual Basic provide simple mechanisms for binding form controls directly to a data source, avoiding the necessity for dealing directly with the database engine. For various reasons that we'll be looking at, however, this is not always either possible or appropriate. In these instances, it's most effective to use a data object model to manipulate the data in code.

A data access object model is a kind of glue between the programming environment and the database engine; it provides a set of objects with properties and methods that can be manipulated in code. Microsoft (currently) provides three data access object models: Data Access Objects (DAO), which comes in two flavors, DAO/Jet and DAO/ODBCDirect; Remote Data Objects (RDO), used primarily for accessing open database connectivity (ODBC) data sources; and Microsoft ActiveX Data Objects (ADO), which is intended to replace both DAO and RDO in the near future.

DAO, the oldest of the three, is the native interface to the Jet database engine. RDO is similar to DAO but is optimized for accessing ODBC data sources such as SQL Server and Oracle. ADO uses a smaller object hierarchy than the other two, consisting of only four primary objects, and provides some significant extensions to the model—for example, its support for disconnected recordsets and data shaping.

Since this book deals primarily with design rather than implementation, we won't be discussing the trade-offs between these models in any depth. William Vaughn's Hitchhiker's Guide to Visual Basic and SQL Server and various white papers available at the Microsoft Web site are your best sources for a discussion of these issues. (By the way, there are alternatives to data access object models, such as the Visual Basic Library for SQL Server (VBSQL) and OLE DB, but we won't be looking at them here.)

Front-End Development Tools

Microsoft Jet and SQL Server handle the physical aspects of manipulating data for us, but we need some way to tell them how to structure the data. Microsoft provides a plethora of methods for doing this, but we'll be looking at only two in detail: Access and Microsoft Visual Database Tools. The other methods provide roughly the same capabilities, but these are the two I prefer. Of course, once you understand the principles, you can use whichever tools best get the job done for you.

It's also possible to define the structure of your database using code, and we'll look at how you go about doing this, although under normal circumstances I don't recommend it. Unless for some reason you need to alter the structure of the data during the run-time use of your application (and with the possible exception of temporary tables, I'm highly suspicious of this practice), the interactive tools are quicker, easier, and a lot more fun to use.

Once the physical definition of your database is in place, you'll need tools to create the forms and reports your users will interact with. We'll be looking primarily at two of these: Access and Visual Basic. We'll take a quick look at Internet browsers in Chapter 10, but HTML itself is outside the scope of this book.