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 bits and pieces 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.

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 13. These tools will handle the physical manipulation of datastoring 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 is technically a front-end development environment that uses either Jet or SQL Server natively and can, in fact, use any ODBC-compliant database engine as its data store. It uses the Jet database engine to manipulate data stored in .mdb files and SQL Server (or another ODBC data store) for data stored in .adp files. Access has always used the Jet database engine, although Microsoft didn't expose it as a separate entity until the release of Microsoft Visual Basic 3.

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- to medium-sized systems. (Please note that this does not imply that the Jet database engine is appropriate only for trivial 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. MSDE (an acronym for Microsoft Desktop Engine) is a scaled-down version of SQL Server intended for desktop use. From a designer's viewpoint, there is little difference between MSDE and the full version of SQL Server, and we won't be considering it further here. 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 13.

Data Access Object Models

Microsoft Access, and to a lesser extent Visual Studio .NET, provides 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, you'll need to use a data access 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. Since this book deals primarily with design rather than implementation, we won't be discussing the trade-offs between these models in any great depth, but I believe it useful to review them here.

Microsoft (currently) provides three data access object models: Data Access Objects (DAO), which comes in two flavors (DAO/Jet and DAO/ODBCDirect), Microsoft ActiveX Data Objects (ADO) and ADO.NET.

DAO, the oldest of the three, is the native interface to the Jet database engine. The statements of Microsoft's marketing department notwithstanding, it is the most efficient object model to use for manipulating Jet databases within Microsoft Access. ADO uses a smaller object hierarchy than DAO, consisting of only four primary objects, and provides some significant extensions to the modelfor example, its support for disconnected recordsets and data shaping. It is used within Microsoft Access and other products that support VBA for manipulating any ODBC-compliant database, including both Jet and SQL Server. ADO.NET is, of course, the version of ADO that is used when working within the .NET Framework.

Data Definition Environments

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 three in detail: Access and the SQL Server Enterprise Manager for relational models, and the Analysis Manager for dimensional models. There are other tools that provide roughly the same capabilities, but these are the ones 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 practiceif the database schema isn't stable, you probably haven't understood the problem domain), the interactive tools are quicker, easier, and a lot more fun to use.

Front-End Development

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 draw our example from two of these: Access and Visual Studio .NET (specifically, Visual Basic .NET). Again, there are hundreds of front-end tools around, but the design principles remain the same, so you should be able to apply what you learn here to your front-end tool of choice. We'll take a quick look at Internet browsers in Chapter 10, but HTML itself is outside the scope of this book.