ACCESS DESIGNING

Chia sẻ: Minh Tuan | Ngày: | Loại File: PDF | Số trang:117

Thêm vào BST

Báo xấu

251
lượt xem 88
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

In the simplest sense, a database is a collection of records and files that are organized for a particular purpose. On your computer system, you might keep the names and addresses of all your friends or customers. Perhaps you collect all the letters you write and organize them by recipient. You might have another set of files in which you keep all your financial dataaccounts payable and accounts receivable or your checkbook entries and balances. The word processor documents that you organize by topic are, in the broadest sense, one type of database. The spreadsheet files that you organize according to their uses are another type of...

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: ACCESS DESIGNING

Access Designing Tables of content What is a Database? ...................................................................................................2 Designing Your Database Application........................................................................6 Database Design Concepts .......................................................................................10 Building Your Database in Microsoft Access ...........................................................22 Importing, Linking, and Exporting Data In Microsoft Access...................................35 Adding Power with Select Queries ...........................................................................53 Designing a Relational Database ..............................................................................66 Advanced Query Design—And SQL select commands language..............................70 Forms and Control tools ...........................................................................................88 Access Tools in Designing Custom Multitable Forms ..............................................94 Creating Bound, Multiline, and Calculated Text Boxes ............................................99 Designing Access Report .......................................................................................105 The Finishing Touches ...........................................................................................108 VDC Media Page 1 11/27/01
Access Designing What is a Database? In the simplest sense, a database is a collection of records and files that are organized for a particular purpose. On your computer system, you might keep the names and addresses of all your friends or customers. Perhaps you collect all the letters you write and organize them by recipient. You might have another set of files in which you keep all your financial dataaccounts payable and accounts receivable or your checkbook entries and balances. The word processor documents that you organize by topic are, in the broadest sense, one type of database. The spreadsheet files that you organize according to their uses are another type of database. If you're very organized, you can probably manage several hundred spreadsheets by using folders and subfolders. When you do this, you're the database manager. But what do you do when the problems you're trying to solve get too big? How can you easily collect information about all customers and their orders when the data might be stored in several document and spreadsheet files? How can you maintain links between the files when you enter new information? How do you ensure that data is being entered correctly? What if you need to share your information with many people but don't want two people to try updating the same data at the same time? Faced with these challenges, you need a database management system (DBMS). Relational Databases Nearly all modern database management systems store and handle information using the relational database management model. The term relational stems from the fact that each record in the database contains information related to a single subject and only that subject. Also, data about two classes of information (such as customers and orders) can be manipulated as a single entity based on related data values. For example, it would be redundant to store customer name and address information with every order that the customer places. In a relational database system, the information about orders contains a field that stores data, such as a customer number, which can be used to connect each order with the appropriate customer information. In a relational database management system, sometimes called an RDBMS, the system manages all data in tables. Tables store information about a subject (such as customers or students) and have columns that contain the different kinds of information about the subject (for example, customers' or students' addresses) and rows that describe all the attributes of a single instance of the subject (for example, data on a specific customer or student). Even when you query the database (fetch information from one or more tables), the result is always something that looks like another table. Some Relational Database Terminology • Relation—Information about a single subject such as customers, orders, students, or colleges. A relation is usually stored as a table in a relational database management system. • Attribute—A specific piece of information about a subject, such as the address for a customer or the dollar amount of a contract. An attribute VDC Media Page 2 11/27/01
Access Designing is normally stored as a data column, or field, in a table. • Relationship—The way information in one relation is related to information in another relation. For example, customers have a one- to-many relationship with orders because one customer can place many orders, but any order belongs to only one customer. Students might have a many-to-many relationship with colleges because each student is interested in applying to multiple colleges, and each college receives applications from many students. • Join—The process of linking tables or queries on tables via their related data values. For example, customers might be joined to orders by matching customer ID in a customers table and an orders table. You can also join information on related values from multiple tables or queries. For example, you can join student information with college application information to find out which students applied to which colleges. You can join employee information with contract information to find out which salesperson should receive a commission. Database Capabilities An RDBMS gives you complete control over how you define your data, work with it, and share it with others. The system also provides sophisticated features that make it easy to catalog and manage large amounts of data in many tables. An RDBMS has three main types of capabilities: data definition, data manipulation, and data control. All this functionality is contained in the powerful features of Microsoft Access. Let's take a look at how Access implements these capabilities and compare them to what you can do with spreadsheet or word processing programs. Main Functions of a Database • Data definition—You can define what data will be stored in your database, the type of data (for example, numbers or characters), and how the data is related. In some cases, you can also define how the data should be formatted and how it should be validated. • Data manipulation—You can work with the data in many ways. You can select which data fields you want, filter the data, and sort it. You can join data with related information and summarize (total) the data. You can select a set of information and ask the RDBMS to update it, delete it, copy it to another table, or create a new table containing the data. • Data control—You can define who is allowed to read, update, or insert data. In many cases, you can also define how data can be shared and updated by multiple users. The Architecture of Microsoft Access Microsoft Access calls anything that can have a name an object. Within an Access database, the main objects are tables, queries, forms, reports, macros, and modules. VDC Media Page 3 11/27/01
Access Designing If you have worked with other database systems on desktop computers, you might have seen the term database used to refer to only those files in which you store data. In Access, however, a database also includes all the major objects related to the stored data, including objects you define to automate the use of your data. Here is a summary of the major objects in an Access database: Table An object you define and use to store data. Each table contains information about a particular subject, such as customers or orders. Tables contain fields (or columns) that store different kinds of data, such as a name or an address, and records (or rows) that collect all the information about a particular instance of the subject, such as all the information about an entertainment group named The Belltones. You can define a primary key (one or more fields that have a unique value for each record) and one or more indexes on each table to help retrieve your data more quickly. Query An object that provides a custom view of data from one or more tables. In Access, you can use the graphical query by example (QBE) facility or you can write SQL statements to create your queries. You can define queries to select, update, insert, or delete data. You can also define queries that create new tables from data in one or more existing tables. Form An object designed primarily for data input or display or for control of application execution. You use forms to customize the presentation of data that your application extracts from queries or tables. You can also print forms. You can design a form to run a macro or a Visual Basic for Applications (VBA) procedure (see the sections on macros and modules below) in response to any of a number of eventsfor example, to run a procedure when the value of data changes. Report An object designed for formatting, calculating, printing, and summarizing selected data. You can view a report on your screen before you print it. Macro An object that is a structured definition of one or more actions that you want Access to perform in response to a defined event. For example, you might design a macro that opens a second form in response to the selection of an item on a main form. You might have another macro that validates the content of a field whenever the value in the field changes. You can include simple conditions in macros to specify when one or more actions in the macro should be performed or skipped. You can use macros to open and execute queries, to open tables, or to print or view reports. You can also run other macros or VBA procedures from within a macro. Module An object containing custom procedures that you code using VBA. Modules provide a more discrete flow of actions and allow you to trap errorssomething you can't do with macros. Modules can be stand-alone objects containing functions that can be called from anywhere in your application, or they can be directly associated with a form or a report to respond to events on the associated form or report. on the following page shows a conceptual overview of how objects in Access are related. Tables store the data that you can extract with queries and display in reports VDC Media Page 4 11/27/01
Access Designing or that you can display and update in forms. Notice that forms and reports can use data either directly from tables or from a filtered "view" of the data created by using queries. Queries can use VBA functions to provide customized calculations on data in your database. Access also has many built-in functions that allow you to summarize and format your data in queries. Events on forms and reports can "trigger" either macros or VBA procedures. What is an event? An event is any change in state of an Access object. For example, you can write macros or VBA procedures to respond to opening a form, closing a form, entering a new row on a form, or changing data either in the current record or in an individual control (an object on a form or report that contains data). You can even design a macro or a VBA procedure that responds to the user pressing individual keys on the keyboard when entering data! Activity for Week Opening Create simple database base on information’s the class member’s details. • Name contact.mdb • Full details Name, address, suburb, state, Postcode, Phone number, Email address, Gender, age (optional), Note…etc.. • This will be a solution to do some exercise late on. • Save in floppy disk with your full name. • Hand in next week VDC Media Page 5 11/27/01
Access Designing Designing Your Database Application You could begin building a database in Microsoft Access much as you might begin creating a simple single-sheet solution in a spreadsheet application such as Microsoft Excel by simply organizing your data into rows and columns and then throwing in formulas where you need calculations. If you've ever worked extensively with a database or a spreadsheet application, you already know that this unplanned approach works in only the most trivial situations. Solving real problems takes some planning; otherwise, you end up building your application over and over again. One of the beauties of a relational database system such as Access is that it's much easier to make midcourse corrections. However, it's well worth spending time up front designing the tasks you want to perform, the data structures you need to support those tasks, and the flow of tasks within your database application. You don't have to go deeply into application and database design theory to build a solid foundation for your database project. You'll read about application design fundamentals in the next section, and then you'll apply those fundamentals in the succeeding sections. "Data Analysis." The section "Database Design Concepts" teaches you a basic method for designing the tables you'll need for your application and for defining relationships between those tables. Application Design Fundamentals Methodologies for good computer application design were first devised in the 1960s by recognized industry consultants such as James Martin, Edward Yourdon, and Larry Constantine. At the dawn of modern computing, building an application or fixing a broken one was so expensive that the experts often advised spending 60 percent or more of the total project time getting the design right before penning a single line of code. Today's application development technologies make building an application much cheaper and faster. An experienced user can sit down with Microsoft Access on a PC and build in an afternoon what used to take months to create on an early mainframe system (if it was even possible). It's also easier than ever to go back and fix mistakes or to "redesign on the fly." Today's technologies also give you the power to build very complex applications. And the pace of computing is several orders of magnitude faster than it was just a decade ago. But even with powerful tools, creating a database application (particularly a moderately complex one) without first spending some time determining what the application should do and how it should operate invites a lot of expensive time reworking the application. If your application design is not well thought out, it will also be very expensive and time-consuming later to track down any problems or to add new functionality. The following is a brief overview of the typical steps involved in building a database application. VDC Media Page 6 11/27/01
Access Designing Step 1: Identifying Tasks Before you start building an application, you'll probably have some idea of what you want it to do. It is well worth your time to make a list of all the major tasks you want to accomplish with the applicationincluding those that you might not need right away but might want to implement in the future. By "major tasks" I mean application functions that will ultimately be represented in a form or a report in your Access database. For example, "Enter customer orders" is a major task that you would accomplish by using a form created for that purpose, while "Calculate extended price" is most likely a subtask of "Enter customer orders" that you would accomplish by using the same form. Step 2: Charting Task Flow To be sure your application operates smoothly and logically, you should lay out the major tasks in topic groups and then order those tasks within groups on the basis of the sequence in which the tasks must be performed. For example, you probably want to separate employee-related tasks from sales-related ones. Within sales, an order must be entered into the system before you can print the order or examine commission totals. You might discover that some tasks are related to more than one group or that completing a task in one group is a prerequisite to performing a task in another group. Grouping and charting the flow of tasks helps you discover a "natural" flow that you can ultimately reflect in the way your forms and reports are linked in your finished application. Later in this chapter, you'll see how I laid out the tasks performed in one of the sample applications included with this book. Step 3: Identifying Data Elements After you develop your task list, perhaps the most important design step is to list the data required by each task and the changes that will be made to that data. A given task will require some input data (for example, a price to calculate an extended amount owed on an order); the task might also update the data. The task might delete some data elements (remove invoices paid, for example) or add new ones (insert new order details). Or the task might calculate some data and display it, but it won't save the data anywhere in the database. Step 4: Organizing the Data After you determine all the data elements you need for your application, you must organize the data by subject and then map the subjects into tables and queries in your database. With a relational database system such as Access, you use a process called normalization to help you design the most efficient and most flexible way to store the data. Step 5: Designing a Prototype and a User Interface VDC Media Page 7 11/27/01
Access Designing After you build the table structures needed to support your application, you can easily mock up the application flow in forms and tie the forms together using simple macros or Visual Basic for Applications (VBA) event procedures. You can build the actual forms and reports for your application "on screen," switching to Form view or Print Preview periodically to check your progress. If you're building the application to be used by someone else, you can easily demonstrate and get approval for the "look and feel" of your application before you write any complex code that's needed to actually accomplish the tasks. Step 6: Constructing the Application For very simple applications, you might find that the prototype is the application. Most applications, however, will require that you write code to fully automate all the tasks you identified in your design. You'll probably also need to create certain linking forms that facilitate moving from one task to another. For example, you might need to construct switchboard forms that provide the navigational road map to your application. You might also need to build dialog forms to gather user input to allow users to easily filter the data they want to use in a particular task. You might also want to build custom menus for most, if not all, of the forms in the application. Step 7: Testing, Reviewing, and Refining As you complete various components of your application, you should test each option that you provide. As you'll learn in this course later on, you can test macros by stepping through the commands you've written, one line at a time. If you automate your application using VBA, you'll have many debugging tools at your disposal to verify correct application execution and to identify and fix errors. If at all possible, you should provide completed portions of your application to users so that they can test your code and provide feedback about the flow of the application. Despite your best efforts to identify tasks and lay out a smooth task flow, users will invariably think of new and better ways to approach a particular task after they've seen your application in action. Also, users often discover that some features they asked you to include are not so useful after all. Discovering a required change early in the implementation stage can save you a lot of time reworking things later. The refinement and revision process continues even after the application is put into use. Most software developers recognize that after they've finished one "release," they often must make design changes and build enhancements. For major revisions, you should start over at step 1 to assess the overall impact of the desired changes so that you can smoothly integrate them into your earlier work. Typical Application Development Steps • Identifying tasks • Charting task flow VDC Media Page 8 11/27/01
Access Designing • Identifying data elements • Organizing the data • Designing a prototype and a user interface • Constructing the application • Testing, reviewing, and refining An Application Design Strategy The two major schools of thought on designing databases are process-driven design (also known as top-down design), which focuses on the functions or tasks you need to perform, and data-driven design (also known as bottom-up design), which concentrates on identifying and organizing all the bits of data you need. The method used here incorporates ideas from both philosophies. This method begins with you identifying and grouping tasks to decide whether you need only one database or more than one database. (This is a top-down approach.) As explained previously, databases should be organized around a group of related tasks or functions. For each task, you choose the individual pieces of data you need. Next you gather all the data fields for all related tasks and begin organizing them into subjects. (This is a bottom-up approach.) Each subject forms the foundation for the individual tables in your database. Finally, you apply the rules you will learn in the "Database Design Concepts" section of other lesson to create your tables. Analyzing the Tasks Let's assume that you've been hired by the Information Technology Group (ITG) at Microsoft to design a book catalog and order entry database for Microsoft Press. The database application must allow authorized users to enter and update book and author data. Potential customers who receive this catalog must be able to search for books of interest, select ones they want to order, search for nearby stores that carry Microsoft Press books, and print out an order that they can take to the store. VDC Media Page 9 11/27/01
Access Designing Database Design Concepts When using a relational database system such as Microsoft Access, you should begin by designing each database around a specific set of tasks or functions. For example, you might design one database for customers and orders that contains data about each customer, the products available for sale, the orders for each customer, and the product sales history. You might have another database that handles human resources for your company. It would contain all relevant data about the employees and their dependents, such as names, job titles, employment histories, departmental assignments, insurance information, and the like. At this point, you face your biggest design challenge: How do you organize data within each task-oriented database so that you take advantage of the relational capabilities of Access and avoid inefficiency and waste? If you followed the steps outlined earlier in this chapter for analysing application tasks and identifying database subjects, you're well on your way to creating a logical, flexible, and usable database design. But what if you just "dive in" and start laying out your tables without first analyzing tasks and subjects? The rest of this chapter shows you how to apply some rules to transform a makeshift database design into one that is robust and efficient. Waste Is the Problem A table stores the data you need for the tasks you want to perform. A table is made up of columns, or fields, each of which contains a specific kind of data (such as a customer name or a credit rating), and rows, or records, that collect all the data about a particular person, place, or thing. You can see this organization in the Customers table in the Microsoft Press Books database, as shown in Figure 3-1. FIGURE 3-1 The Customers table in Datasheet view. For the purposes of this design exercise, let's say you want to build a new database (named Books) for creating book orders without the benefit of first analyzing the tasks and subjects you'll need. You might be tempted to put all the data about the task you want to dokeeping track of customers and the books they orderin a single Customer-Orders table, whose fields are represented in Figure 3-2 on the following page. VDC Media Page 10 11/27/01
Access Designing There are many problems with this technique. For example: • Every time a customer adds another order, you have to duplicate the Customer Name and Customer Address fields in another record for the new order. Repeatedly storing the same name and address in your database wastes a lot of space and you can easily make mistakes if you have to enter basic information about a customer more than once. • You have no way of predicting how many titles will be ordered in any given order. If you keep track of each order in a single record, you have to guess the largest number of titles and leave space for Book 1, Book 2, Book 3, and so on, all the way to the maximum number. Again you're wasting valuable space in your database. If you guess wrong, you'll have to change your design just to accommodate an order that has more than the maximum number of titles. And later, if you want to find out which books were sold to what customers, you'll have to search each Book Name field in every record. • You have to waste space in the database storing data that can easily be calculated when it's time to print a report. For example, you'll certainly want to calculate the total order amount for each order, but you do not need to keep the result in a field. • Designing one complex field to contain all the parts of simple data items (for example, lumping together Street Address, City, State, and Postal Code) makes it difficult to search or sort on part of the data. In this example, it would be impossible to sort on customer zip code because that piece of information might appear anywhere within the more complex single address field. FIGURE 3-2. The design for the Books database using a single Customer-Orders table. Normalization Is the Solution You can minimize the kinds of problems noted above (although it might not always be desirable to eliminate all duplicate values), by using a process called normalization to organize data fields into a group of tables. The mathematical theory behind normalization is rigorous and complex, but the tests you can apply to determine whether you have a design that makes sense and that is easy to use are quite simpleand can be stated as rules. Rule 1: Field Uniqueness VDC Media Page 11 11/27/01
Access Designing Since wasted space is one of the biggest problems with an unnormalized table design, it makes sense to remove redundant fields from a table. So the first rule is about field uniqueness. Rule 1: Each field in a table should represent a unique type of information. This means that you should break up complex compound fields and get rid of the repeating groups of information. In this example, the complex address fields should be separated into simple fields and new tables designed to eliminate the repeating book information. When you create separate tables for the repeating data, you include some "key" information from the main table to create a link between the new tables and the original one. One possible result is shown in Figure 3-3. FIGURE 3-3. A design for the Books database that eliminates redundant fields. These tables are much simpler because you can process one record per book ordered. Also, you don't have to reserve room in your records to hold a large number of books per order. And, if you want to find out what book has the highest price, you can now search the separate Books table, where key information about each book is recorded only once. The duplicate data problem is now somewhat worse, however, because you are repeating the Order Date and Customer Name fields in each Order Details record. The potentially long Book Title field is also redundant in the Books, Book-Authors, and Order Details tables. This "duplicate" data is necessary, however, to maintain the links between the tables. You can solve this problem by following the second rule. Rule 2: Primary Keys In a good relational database design, each record in any table must be uniquely identified. That is, some field (or combination of fields) in the table must yield a unique value for each record in the table. This unique identifier is called the primary key. Rule 2: Each table must have a unique identifier, or primary key, that is made up of one or more fields in the table. VDC Media Page 12 11/27/01
Access Designing Whenever possible, you should use the simplest data that "naturally" provides unique values. Nearly all books published in the world have a relatively short (12 character) International Standard Book Number or ISBN that uniquely identifies each book. This makes the ISBN field a good "natural" primary key for the Books table. Although it appears that you've created duplicate data with the book ISBN field in three of the tables, you've actually significantly reduced the total amount of data stored. The lengthy book title data is stored only once for each book in the Books table and not for each detail line in an order. You've duplicated only a small piece of data, the ISBN field, which allows you to relate the order detail and author data to the appropriate book data. Relational databases are equipped to support this design technique by giving you powerful tools to bring related information back together easily. Whenever you build a table, Access always recommends that you define a primary key for that table. For many tables, you might need to create an artificial unique value to act as the primary key. The Books application will probably generate a unique order number or Order ID for each new order entered. (You'll see in the next chapter that Access provides a special data type, called AutoNumber, that generates a unique number for every new row in a table.) In the case of Order Details, the combination of the Access-generated Order ID and the book ISBN is most likely unique for each row in the table (you're not likely to create more than one order detail line for a particular title in a single order). The result of adding primary keys is shown in Figure 3-4. Figure 3-4.. The Books database tables with primary keys defined. Rule 3: Functional Dependence After you define a primary key for each table, you can check to see whether you included all the data relevant to the subject of the table. In other words, you should check to see whether each field is functionally dependent on the primary key that defines the subject of the table. Rule 3: For each unique primary key value, the values in the data columns must be relevant to, and must completely describe, the subject of the table. VDC Media Page 13 11/27/01
Access Designing This rule works in two ways. First, you shouldn't have any data in a table that is not relevant to the subject (as defined by the primary key) of the table. For example, although customer information is required for each order, customers are in fact a separate subject and should have their own table. Likewise, an author may write more than one book, so creating a separate table for authors makes sense. Second, the data in the table should completely describe the subject. For example, books in an order may be shipped to a person and location different from the customer who is buying the books (perhaps as a gift). Adding shipping information to the Orders table makes that information more complete. The result is shown in Figure 3-5. FIGURE 3-5. Creating additional subject tables in the Books database to ensure all fields in a table are functionally dependent on the primary key of the table. Rule 4: Field Independence The last rule checks to see whether you'll have any problems when you make changes to the data in your tables. Rule 4: You must be able to make a change to the data in any field (other than a field in the primary key) without affecting the data in any other field. Take a look again at the Orders table in Figure 3-5. As we applied the second and third rules, we left Store information with the Orders information because it seems reasonable that you need Store information to complete an order. Note that if you need to correct the spelling of a store name, you can do so without affecting any other fields in that record. If you misspelled the same store name for many orders, however, you might have to change many records. Also, if you entered the wrong store (the order is actually for Powell's Technical Bookstore, not University Bookstore), you can't change the store name without also changing that record's address and phone data. The Store Name, Store Address, and Store Phone fields are not independent of one another. In fact, Store Address, Store City, Store State, and Store Phone are functionally dependent on Store Name. (See Rule 3.) Although it wasn't obvious at VDC Media Page 14 11/27/01
Access Designing first, Store Name describes another subject that is different from the subject of orders. You can see how carefully applying this fourth rule helps you identify changes that you perhaps should have made when applying earlier rules. This situation calls for another table in your design: a separate Stores table, as shown in Figure 3-6 on the following page. Now, if you've misspelled a store name, you can simply change the store name in the Stores table. Also, instead of using the Store Name field (which might be 40 or 50 characters long) as the primary key for the Stores table, you can create a shorter Store ID field (perhaps a five-digit number) to minimize the size of the relational data you need in the Order table. Note also that the Order Total field has been removed from the Orders table and the Price and Extended Price fields have been removed from the Order Details table. Because the price of a book rarely changes, it makes little sense to carry the price in both the Books table and the Order Details table. As you'll see later when you learn about building queries, it's a simple matter to link the Order Details table with the Books table in a query to retrieve the price and calculate the extended price for each book. Likewise, Order Total is removed from the Orders table because any change to a price, order quantity, or discount will cause a change in the total. It's better to calculate the total order value when the order is completeperhaps as part of the report that prints the order. FIGURE 3-6. A design for the Books database that follows all the design rules. An alternative (but less rigorous) way to check for field independence is to see whether you have the same data repeated in your records. In the previous design, whenever you created an order for a particular store, you had to enter the store's name, address, state, zip, and phone number in the order record. With a separate Stores table, if you need to correct a spelling or change an address, you have to make the change only in one field of one record in the Stores table. If you entered the wrong store in an order, you have to change only the Store ID in the Orders table to fix the problem. VDC Media Page 15 11/27/01
Access Designing The actual Microsoft Press Books sample database includes ten tables, which are all shown in the Relationships window in Figure 3-7. Notice that additional fields were created in each table to fully describe the subject of each table and that other tables were added to support some of the other tasks identified earlier in this section. For example, fields were added to the Orders table to capture payment method and credit card number. New tables were added to provide a means to designate book categories and category classes, such as Intermediate (a category) and Book Audience (a class). FIGURE 3-7 Tables in the Microsoft Press Books sample database shown in the Relationships window. The Four Rules of Good Table Design Rule 1: Each field in a table should represent a unique type of information. Rule 2: Each table must have a unique identifier, or primary key, that is made up of one or more fields in the table. Rule 3: For each unique primary key value, the values in the data columns must be relevant to, and must completely describe, the subject of the table. Rule 4: You must be able to make a change to the data in any field (other than a field in the primary key) without affecting the data in any other field. Efficient Relationships Are the Result VDC Media Page 16 11/27/01
Access Designing When you apply good design techniques, you end up with a database that efficiently links your data. You probably noticed that when you normalize your data as recommended, you tend to get many separate tables. Before relational databases were invented, you had to either compromise your design or manually keep track of the relationships between files or tables. For example, you had to put customer data in your Orders table or write your program to first open and read a record from the Orders table and then search for the matching record in the Customers table. Relational databases solve these problems. With a good design you don't have to worry about how to bring the data together when you need it. Foreign Keys You might have noticed as you followed in the Microsoft Press Books example that each time you created a new table, you left behind a field that could link the old and new tables, such as the Customer ID and the Store ID fields in the Orders table. These "linking" fields are called foreign keys. I a well-designed database, foreign keys result in efficiency. You keep track of related foreign keys as you lay out your database design. When you define your tables in Access, you link primary keys to foreign keys to tell Access how to join the data when you need to retrieve information from more than one table. As you'll learn in Next Lesson , you can add indexes to your foreign key fields to improve performance. You can also ask Access to maintain the integrity of your table relationships for example, Access will ensure that you don't create an order for a product that doesn't exist. When you ask Access to maintain this referential integrity, Access automatically creates indexes for you. One-to-Many and One-to-One Relationships In most cases, the relationship between any two tables is one-to-many. That is, for any one record in the first table, there are many related records in the second table, but for any record in the second table, there is exactly one matching record in the first table. You can see several instances of this type of relationship in the design of the Microsoft Press Books database. For example, each customer might have several orders, but a single order record applies to only one customer. Occasionally, you might want to break down a table further because you use some of the data in the table infrequently or because some of the data in the table is highly sensitive and should not be available to everyone. For example, you might want to keep track of certain customer data for marketing purposes, but you don't need access to that data all the time. Or you might have data about credit ratings that should be accessible only to authorized people. In either case, you can create a separate table that also has a primary key of CustomerID. The relationship between the original Customers table and the Customer Info or Customer Credit table is one-to-one. That is, for each record in the first table, there is exactly one record in the second table. Creating Table Links VDC Media Page 17 11/27/01
Access Designing The last step in designing your database is to create the links between your tables. For each subject, identify those for which you wrote Many under "Relationship" on the worksheet. Be sure that the corresponding relationship for the other table is One. If you see Many in both places, you must create a separate intersection table to handle the relationship. (Access won't let you define a many-to-many relationship directly between two tables.) In the example of the Order Books task, a customer may have an order for "many" books, and a book can appear in many orders. The OrderDetails table in the Microsoft Press Books database is an intersection table that clears up this many-to-many relationship between orders and books. BookAuthor is another table that works as an intersection table because it has a one-to-many relationship with both Authors and Books. (A book can have more than one author, and an author might write more than one book.) After you straighten out the many-to-many relationships, you need to create the links between tables. To complete the links, place a copy of the primary key from the "one" tables in a field in the "many" tables. The same Worksheet tasks that you did last week you should writing down the relations for table, so that you can surmise that the primary key for the Orders table, OrderID, also needs to be a field in the OrderDetails table. When to Break the Rules As a starting point, for every application that you build, you should always analyze the tasks you need, decide on the data required to support those tasks, and create a well-designed (also known as normalized) database table structure. After you have a design that follows all the rules, you might discover changes that you need to make either to follow specific business rules or to make your application more responsive to the needs of your users. In every case for which you decide to "break the rules," you should know the specific reason for doing so, document your actions, and be prepared to add procedures to your application to manage the impact of those changes. The following sections discuss some of the reasons why you might need to break the rules. Improving Performance of Critical Tasks The majority of cases for breaking the rules involve manipulating the design to achieve better performance for certain critical tasks. For example, although modern relational database systems (like Microsoft Access) do a good job of linking many related tables back together to perform complex tasks, you might encounter situations in which the performance of a multiple-table link is not fast enough. Sometimes if you "demoralise" selected portions of the design, you can achieve the required performance. For example, instead of building a separate table of book classification codes that requires a link, you might place the classification descriptions directly in the book categories table. If you choose to do this, you will need to add procedures to the forms you provide to edit these categories to make sure that similar descriptions aren't duplicate entries. Another case for breaking the rules is the selective inclusion of calculated values in your database. For example, if a critical management report needs the calculated totals for all orders, but the data is retrieved too slowly when calculating the detailed values VDC Media Page 18 11/27/01
Access Designing for thousands of order detail records, you might want to add a field for order total in the Orders table. Of course, this also means adding procedures to your order-entry forms to ensure that any change in an order detail record is reflected in the calculated order total. Your application will spend a few extra fractions of a second processing each order so that month-end totals can be obtained quickly. Capturing Point-In-Time Data Sometimes you need to break the rules to follow known business rules. In the previous design exercise, we removed the Price field from the Order Details table because it duplicated the price information in the Books table. However, if your business rules say that the price of a book (or any product) can change over time, you may need to include the price in your order details to record the price at the point in time that the order was placed. If your business rules dictate this sort of change, you should add procedures to your application to automatically copy the "current" price to any new order detail row. You can see a similar case in the Microsoft Press Books database. Some of the shipping name and address information in the Orders table may duplicate information in the Customers table. If you examine the way the Books database works, you'll find some code that automatically copies the customer information to the shipping information when you create a new order. The user is free, however, to change the shipping information as required by the order. There's a similar example in the Entertainment Schedule database. In this database, the user creates new contracts that specify the commitment of an entertainment group to perform at a specific club on a specific date. The information about clubs remains fairly constant, but groups change their name and membership all the time. If you look at the database design for the Entertainment Schedule database, shown in figure below on the following page, you'll see what looks like lots of duplicate information in the Contracts table. For example, the club manager who arranged the contract is copied to the contract (the manager might be different next week!). In addition, all the information about the group (Group Name, Leader Name, and Member Names) is copied to the contract to capture the information at the point in time that the contract was signed. If you look in the Entertainment Schedule sample database, you'll find lots of code in the contract edit form (frmContracts) to make this happen automatically. VDC Media Page 19 11/27/01