Design Considerations for Large or Complex Databases | Microsoft Access Version 2002 Inside Out (Inside Out (Microsoft))

3 4

Designing a database for a client—especially if the database is large or complex-is a whole different ball game from creating a simple database for yourself. Your development experience will go more smoothly—and your customers are more likely to get the databases they want—if you think about three important considerations up front:

A picture is worth a thousand words. Create a set of prototype, or dummy, forms that you can use to walk the customer through the system before you begin creating it.
Choose your tools carefully. Think about whether the database project will be better served by the SQL Server 2000 Desktop Engine or SQL Server.
Divide and conquer your data. Splitting the database into a front end and a back end creates a system you can easily modify later.

The sections that follow provide more detail on each of these design techniques for large and complicated databases.

Creating an Interface Prototype for User Feedback

If you have a large database project that's not well defined, you can help clarify the client's expectations and test your preliminary database design by creating forms that let the users get a feel for how the database works, without actually writing the code and creating the tables that will ultimately be needed. To do this, create a set of unbound forms as an interface prototype and then demonstrate the prototype to the database users so that they can comment on the form design and functionality before you create the tables, queries, and bound forms. Creating a prototype is a good way to clarify exactly what data needs to be stored in what table and can save you a good deal of time in the long run.

See Chapter 5, "Creating Forms for Entering, Editing, and Viewing Data," for more details on creating forms.

It's easier to change table and field structure in Access than in some older databases. In Access, you can add or delete a field or change a field's data type if necessary. However, it's still preferable to get the table and field structure finalized as much as possible before you begin working on queries, forms, and reports, because changes to table structure generally require changes to queries, forms, and reports that are bound to the tables.

Here are some suggestions for user feedback you might want to gather using dummy forms:

Can the primary key for a table be an AutoNumber value, or should it be entered manually or selected from a list of existing IDs (such as Employee IDs or Product Numbers)? Generally, it's best to use an existing ID, if it's unique and available for each record.
If an existing ID code (such as Employee ID) is to be used as the key field, is it a text or numeric value?
If the customer wants to use an existing code as the key field (say, an employee number or a Social Security number), would it ever happen that a record has to be entered without the code? In that case, you need to use another field—usually an AutoNumber field—as the key field.
Is data to be entered manually or imported from some other data source, such as a text file generated by a mainframe computer database?
Should contact names be typed in or selected from a table of contacts displayed in a combo box?
Should e-mail addresses be typed in or selected from the Outlook Contacts list or a global address book?
Do you need to store only a fixed set of telephone numbers (say, home and work numbers) or an open-ended list, which would require creating a linked table?
Do you need to store only a fixed set of addresses for a customer (say, home and work addresses) or an open-ended list, which would require creating a linked table?
Do you need to duplicate every feature of an old paper form, or can some components be discarded or replaced by computer-specific elements such as combo boxes, now that data entry is done on the computer?
Could you use some new Access features not present in the old system, such as tabbed forms, PivotTables, or PivotCharts?

Answering these questions will help you set up your tables and fields properly in the first place, minimizing time-consuming modifications later in the development process.

Choosing Access or SQL Server as a Back End

For a small database, especially a single-user database, an all-Access database is fine, but if you're developing a database intended for use by a small workgroup, you can use SQL Server 2000 Desktop Engine (the successor to Microsoft Data Engine [MSDE]) as a data storage back end, linked to a front-end Access project. For more information about Access projects, see Chapter 19, "Creating Access Projects." SQL Server 2000 Desktop Engine uses the same data engine as SQL Server, so a single Access project can be linked to either back end, but there are some differences between the two. Apart from some highly technical differences, these are the two main differences:

SQL Server 2000 Desktop Engine comes with Access 2002; SQL Server must be purchased separately.
SQL Server 2000 Desktop Engine has a 2-GB database limit.

In general terms, SQL Server 2000 Desktop Engine has the basic data engine features of SQL Server 2000, but it lacks a user interface, management tools, analysis capabilities, merge replication support, client access licenses, developer libraries, and SQL Server Books Online. It also limits database size and user workload.

SQL Server 2000 Desktop Engine is an excellent choice if you think that at some point in the future your database will need to be moved to full SQL Server—you can develop the Access project with a SQL Server 2000 Desktop Engine back end and then upsize it to SQL Server when necessary, using the Upsizing Wizard. (See Appendix E, "Upsizing to SQL Server," for more information about using the Upsizing Wizard.)

Splitting a Database into Front and Back Ends

Even if you don't need to convert your Access database to a project and give it a SQL Server 2000 Desktop Engine (or SQL Server) back end, you can still split the database into a front end (containing the interface objects) and a back end (containing only the data tables). If you're creating a database for a client, this is generally a good idea, because splitting a database into a front end and a back end makes it easier to swap in a modified interface, without disturbing data entered or modified by the client-all you have to do is send the client a new front-end database.

If tables, forms, reports, and other database objects are all in the same database and you send the client an updated database, their newly entered data will be lost. See Chapter 15, "Using Add-Ins to Expand Access Functionality," for more details on using the Database Splitter utility, which automates the process of splitting a database into a front end and a back end.

Making and Delivering Database Changes

If you have Microsoft Office XP Developer, you can create a distributed version of your Access application, which can be run by users who don't have Access installed. (See Appendix B, "Distributing Access Applications," for more details on preparing a distributed application.) This isn't always the best option, however (and if you don't have the Developer edition of Office XP, it isn't an option at all). If your clients want to modify the database themselves, and all potential users have Access installed, it will save time to just prepare an Access database. Additionally, if you need to be able to make modifications to the database on line, this can be done only if the regular version of Access is installed at the client's location.

tip - Package your application using the Packaging Wizard

If you have Office XP Developer installed and you want to deliver an Access application that uses regular Access rather than the runtime version of Access, you can use the Packaging Wizard component to package your application, including any ActiveX controls and other components that you need. This option is especially useful if you need to ensure that all the required supporting files are installed with the database.