Planning a Database
Planning a Database Access is a special kind of database called a relational database . A relational database divides information into discrete subsets. Each subset groups information by a particular theme, such as customer information, sales orders, or product information. In Access, these subsets of data reside in individual tables like the one described previously. Access enables you to build relationships between tables. These relationships are based on a field that is common to two tables. Each table must have a field called the primary key (you learn how to specify a field as the primary key in Lessons 4 and 5). The primary key must uniquely identify each record in the table. So, the primary key field is typically a field that assigns a unique number (no duplicates within that table) to each record. For example, a Customers table might contain a Customer Identifica tion field (shown as CustomerID in Figure 2.1) that identifies each customer by a unique number (such as your Social Security number). You might also have a table that holds all your sales orders. To link the Sales table to the Customers table, you include the Customer Identification field in the Sales table. This identifies each sale by customer and links the Sales table data to the Customers table data. Plain English Relational Database A collection of individual tables holding discrete subsets of information that are linked by common data fields. You will find that even a simple database consists of several tables that are related . Figure 2.1 shows a database and the different table relationships. Lesson 11, "Creating Relationships Between Tables," provides information on creating table relationships. Figure 2.1. A relational database contains related tables. When you do create a new database, you want to make sure that the database is designed not only to meet your data entry needs, but also to meet your needs for viewing and reporting the data that is held in the various tables that make up the database. Taking a little time to plan your database before you create it can save you from headaches down the road. The sections that follow provide some tips on planning a database. Determining Your TablesTechnically, you need only one table to make a database. However, because Access is a relational database program, it's meant to handle many tables and create relationships among them. For example, in a database that keeps track of customer orders, you might have the following tables:
Using many tables that hold subsets of the database information can help you avoid making redundant data entries. For example, suppose you want to keep contact information on your customers along with a record of each transaction they make. If you kept it all in one table, you would have to repeat the customer's full name , address, and phone number each time you entered a new transaction. It would also be a nightmare if the customer's address changed; you would have to change the address in every transaction record for that customer. A better way is to assign each customer an ID number. Include that ID number in a table that contains names and addresses, and then use the same ID number as a link to a separate transactions table. Basically, then, each table in your database should have a particular themefor example, Employee Contact Information or Customer Transactions. Don't try to have more than one theme per table. A table design requirement is to be sure that every table that you create uses the first field (the first column of the table) as a way to uniquely identify each record in the table. This field can then serve as the table's primary key. For example, customers can be assigned a customer number, or sales transactions can be assigned a transaction number. The primary key is the only way that you can then link the table to another table in the database. It's a good idea to do some work on paper and jot down a list of tables that will be contained in the database and the fields that they will contain. Restructuring tables because of poor planning isn't impossible , but it isn't much fun, either. Determining Your FormsAs already mentioned in Lesson 1, forms are used for data entry. They allow you to enter data one record at a time (see Figure 2.2). Complex forms can also be constructed that actually allow you to enter data into more than one table at a time (this is because fields can be pulled from several tables in the same database into one form). Figure 2.2. A form allows you to enter data one record at a time. Planning the forms that you use for data entry is not as crucial as planning the tables that make up the database. Forms should be designed to make data entry easier. They are great in that they allow you to concentrate on the entry or editing of data one record at a time. You might want to have a form for each table in the database, or you might want to create composite forms that allow you to enter data into the form that is actually deposited into more than one table. The great thing about forms is that they don't have to contain all the fields that are in a particular table. For example, if you have someone else enter the data that you keep in an employee database, but you don't want that data entry person to see the employee salaries, you can design a form that does not contain the salary field. Determining Your QueriesQueries enable you to manipulate the data in your database tables. A query can contain criteria that allow you to delete old customer records, or it can provide you with a list of employees who have worked at the company for more than 10 years . Deciding the queries that you will use before all the data is entered can be difficult. However, if you are running a storea cheese shop, for exampleand know that it is important for you to keep close tabs on your cheese inventory, you will probably want to build some queries to track sales and inventory. Queries are an excellent way for you to determine the status of your particular endeavor. For example, you could create a query to give you total sales for a particular month. Queries are, in effect, questions. Use queries to get the answers that you need from your database information. Determining Your ReportsA report is used to publish the data in the database. It places the data on the page (or pages) in a highly readable format. Reports are meant to be printed (unlike tables and forms, which are usually used onscreen). For example, you might run a club and want a report of all people who haven't paid their membership dues or who owe more than $1,000 on their account. A report is usually for the benefit of other people who aren't sitting with you at your computer. For example, you might print a report to hand out to your board of directors to encourage them to keep you on as CEO. A report can pull data from many tables at once, perform calculations on the data (such as summing or averaging), and present you with neatly formatted results. Figure 2.3 shows a database report. Figure 2.3. Reports allow you to organize and summarize database information. You can create new reports at any time; you don't have to plan them before you create your database. However, if you know you will want a certain report, you might design your tables in the format that will be most effective for that report's use. Designing good databases is an acquired skill. The more databases that you work with, the better each will be. Now that you've gotten your feet wet with database planning, take a look at how to start Access. |
| Team-Fly |
| Top |
EAN: 2147483647
Pages: 160