Database solutions a step-by-step guide to building databases free ebook

For most databases you will need more than one. For example, you might have a table that stores information about products, another table that stores information about orders, and another table with information about customers.

Each row is more correctly called a record , and each column, a field. A record is a meaningful and consistent way to combine information about something. A field is a single item of information — an item type that appears in every record. In the Products table, for instance, each row or record would hold information about one product. Each column or field holds some type of information about that product, such as its name or price.

Certain principles guide the database design process. The first principle is that duplicate information also called redundant data is bad, because it wastes space and increases the likelihood of errors and inconsistencies.

The second principle is that the correctness and completeness of information is important. If your database contains incorrect information, any reports that pull information from the database will also contain incorrect information. As a result, any decisions you make that are based on those reports will then be misinformed. Provides Access with the information it requires to join the information in the tables together as needed.

Gather all of the types of information you might want to record in the database, such as product name and order number. Divide your information items into major entities or subjects, such as Products or Orders.

Each subject then becomes a table. Decide what information you want to store in each table. Each item becomes a field, and is displayed as a column in the table. The primary key is a column that is used to uniquely identify each row. Look at each table and decide how the data in one table is related to the data in other tables. Add fields to tables or create new tables to clarify the relationships, as necessary.

Analyze your design for errors. Create the tables and add a few records of sample data. See if you can get the results you want from your tables. Make adjustments to the design, as needed. Apply the data normalization rules to see if your tables are structured correctly. Make adjustments to the tables, as needed. It is a good idea to write down the purpose of the database on paper — its purpose, how you expect to use it, and who will use it.

For a small database for a home based business, for example, you might write something simple like "The customer database keeps a list of customer information for the purpose of producing mailings and reports. The idea is to have a well developed mission statement that can be referred to throughout the design process. Having such a statement helps you focus on your goals when you make decisions. To find and organize the information required, start with your existing information.

For example, you might record purchase orders in a ledger or keep customer information on paper forms in a file cabinet. Gather those documents and list each type of information shown for example, each box that you fill in on a form. If you don't have any existing forms, imagine instead that you have to design a form to record the customer information. What information would you put on the form? What fill-in boxes would you create?

Identify and list each of these items. For example, suppose you currently keep the customer list on index cards. Examining these cards might show that each card holds a customers name, address, city, state, postal code and telephone number. Each of these items represents a potential column in a table.

Instead, list each item that comes to mind. If someone else will be using the database, ask for their ideas, too. You can fine-tune the list later. Next, consider the types of reports or mailings you might want to produce from the database. For instance, you might want a product sales report to show sales by region, or an inventory summary report that shows product inventory levels.

You might also want to generate form letters to send to customers that announces a sale event or offers a premium. Design the report in your mind, and imagine what it would look like. What information would you place on the report? List each item. Do the same for the form letter and for any other report you anticipate creating. Giving thought to the reports and mailings you might want to create helps you identify items you will need in your database.

For example, suppose you give customers the opportunity to opt in to or out of periodic e-mail updates, and you want to print a listing of those who have opted in. For each customer, you can set the field to Yes or No. The requirement to send e-mail messages to customers suggests another item to record. Once you know that a customer wants to receive e-mail messages, you will also need to know the e-mail address to which to send them.

Therefore you need to record an e-mail address for each customer. It makes good sense to construct a prototype of each report or output listing and consider what items you will need to produce the report. For instance, when you examine a form letter, a few things might come to mind. If you want to include a proper salutation — for example, the "Mr. This suggests you would typically want to store the last name separate from the first name.

A key point to remember is that you should break each piece of information into its smallest useful parts. In the case of a name, to make the last name readily available, you will break the name into two parts — First Name and Last Name. To sort a report by last name, for example, it helps to have the customer's last name stored separately.

In general, if you want to sort, search, calculate, or report based on an item of information, you should put that item in its own field. Think about the questions you might want the database to answer. For instance, how many sales of your featured product did you close last month?

Where do your best customers live? Who is the supplier for your best-selling product? Anticipating these questions helps you zero in on additional items to record.

To divide the information into tables, choose the major entities, or subjects. For example, after finding and organizing information for a product sales database, the preliminary list might look like this:. The major entities shown here are the products, the suppliers, the customers, and the orders. Therefore, it makes sense to start out with these four tables: one for facts about products, one for facts about suppliers, one for facts about customers, and one for facts about orders.

You can continue to refine this list until you have a design that works well. When you first review the preliminary list of items, you might be tempted to place them all in a single table, instead of the four shown in the preceding illustration. You will learn here why that is a bad idea. Consider for a moment, the table shown here:. In this case, each row contains information about both the product and its supplier. Because you can have many products from the same supplier, the supplier name and address information has to be repeated many times.

This wastes disk space. Recording the supplier information only once in a separate Suppliers table, and then linking that table to the Products table, is a much better solution. A second problem with this design comes about when you need to modify information about the supplier. For example, suppose you need to change a supplier's address. Because it appears in many places, you might accidentally change the address in one place but forget to change it in the others.

When you design your database, always try to record each fact just once. If you find yourself repeating the same information in more than one place, such as the address for a particular supplier, place that information in a separate table.

Finally, suppose there is only one product supplied by Coho Winery, and you want to delete the product, but retain the supplier name and address information. How would you delete the product record without also losing the supplier information? You can't. Because each record contains facts about a product, as well as facts about a supplier, you cannot delete one without deleting the other. To keep these facts separate, you must split the one table into two: one table for product information, and another table for supplier information.

Deleting a product record should delete only the facts about the product, not the facts about the supplier. Once you have chosen the subject that is represented by a table, columns in that table should store facts only about the subject.

For instance, the product table should store facts only about products. Because the supplier address is a fact about the supplier, and not a fact about the product, it belongs in the supplier table.

To determine the columns in a table, decide what information you need to track about the subject recorded in the table. Each record in the table contains the same set of columns, so you can store Name, Address, City-State-Zip, Send e-mail, Salutation and E-mail address information for each record.

Each record contains data about one customer, and the address field contains the address for that customer. Once you have determined the initial set of columns for each table, you can further refine the columns. For example, it makes sense to store the customer name as two separate columns: first name and last name, so that you can sort, search, and index on just those columns.

If you want to perform a search, filter or sort operation by state, for example, you need the state information stored in a separate column. To ask other readers questions about Database Solutions , please sign up. Lists with This Book. This book is not yet featured on Listopia.

Add this book to your favorite list ». Community Reviews. Showing Average rating 4. Rating details. All Languages. More filters. Sort order. Nov 18, Yuii Aunchalee sripradit rated it it was amazing. Halima rated it it was amazing Apr 28, Ion rated it really liked it Feb 13, Ema Jones rated it really liked it Mar 06, Sana Mustafa rated it it was amazing Nov 13, Bilal rated it it was amazing Nov 05, Jonathan rated it it was amazing Nov 10, Ahmad Amin rated it really liked it Sep 15, Aamir rated it it was amazing Nov 09, John rated it really liked it Sep 30, Peter rated it liked it May 05, Phil Rogers rated it it was ok Jul 13, Mary Mwagalwa rated it it was ok Oct 03, Hieu Do rated it it was amazing Sep 02, Wayne rated it it was ok Mar 23, Elham Kohestani rated it it was amazing Mar 20, Iqra rated it it was amazing May 01, Minhthu added it Apr 03, Siddartha added it Jan 19, On the Home tab, in the Records group, click Delete.

To begin entering data, click in the first empty cell on the form and begin typing. Use the Navigation Pane to browse for other forms or reports that you might want to use. Some templates include a navigation form which allows you to move between the different database objects.

For more information about working with templates, see the article Use a template to create an Access desktop database. If you are not interested in using a template, you can create a database by building your own tables, forms, reports, and other database objects. In most cases, this involves one or both of the following:. Entering, pasting, or importing data into the table that is created when you create a new database, and then repeating the process with new tables that you create by using the Table command on the Create tab.

On the File tab, click New , and then click Blank Database. Type a file name in the File Name box. To change the location of the file from the default, click Browse for a location to put your database next to the File Name box , browse to the new location, and then click OK. Access creates the database with an empty table named Table1, and then opens Table1 in Datasheet view.

The cursor is placed in the first empty cell in the Click to Add column. Begin typing to add data, or you can paste data from another source, as described in the section Copy data from another source into an Access table. Entering data in Datasheet view is designed to be very similar to working in an Excel worksheet. The table structure is created while you enter data. When you add a new column to the datasheet, a new field is defined in the table. Access automatically sets each field's data type, based on the data that you enter.

If you do not want to enter data in Table1 at this time, click Close. If you made any changes to the table, Access prompts you to save the changes. Click Yes to save your changes, click No to discard them, or click Cancel to leave the table open. Tip: Access looks for a file named Blank. If it exists, Blank. Any content it contains is inherited by all new blank databases. This is a good way to distribute default content, such as part numbers or company disclaimers and policies.

Important: If you close Table1 without saving it at least once, Access deletes the entire table, even if you have entered data in it. You can add new tables to an existing database by using the commands in the Tables group on the Create tab. Create a table, starting in Datasheet view In Datasheet view, you can enter data immediately and let Access build the table structure behind the scenes.

Field names are assigned numerically Field1, Field2, and so on , and Access automatically sets each field's data type, based on the data you enter. On the Create tab, in the Tables group, click Table. Access creates the table and selects the first empty cell in the Click to Add column. If you don't see the type that you want, click More Fields.

Access displays a list of commonly used field types. Click the field type that you want, and Access adds the new field to the datasheet at the insertion point. You can move the field by dragging it. When you drag a field in a datasheet, a vertical insertion bar appears where the field will be placed.

To add data, begin typing in the first empty cell, or paste data from another source, as described in the section Copy data from another source into an Access table. You should give a meaningful name to each field, so that you can tell what it contains when you see it in the Field List pane. To move a column, click its heading to select the column, and then drag the column to the location that you want. You can also select multiple contiguous columns and then drag them to a new location all at once.

To select multiple contiguous columns, click the column header of the first column, and then, while holding down SHIFT, click the column header of the last column. Create a table, starting in Design view In Design view, you first create the table structure.

You then switch to Datasheet view to enter data, or enter data by using some other method, such as pasting, or importing. On the Create tab, in the Tables group, click Table Design. For each field in your table, type a name in the Field Name column, and then select a data type from the Data Type list. If you want, you can type a description for each field in the Description column. The description is then displayed on the status bar when the cursor is located in that field in Datasheet view.

The description is also used as the status bar text for any controls in a form or report that you create by dragging the field from the Field List pane, and for any controls that are created for that field when you use the Form Wizard or Report Wizard. You can begin typing data in the table at any time by switching to Datasheet view and clicking in the first empty cell. You can also paste data from another source, as described in the section Copy data from another source into an Access table.

Set field properties in Design view Regardless of how you created your table, it is a good idea to examine and set field properties. While some properties are available in Datasheet view, some properties can only be set in Design view. To see a field's properties, click the field in the design grid.

The properties are displayed below the design grid, under Field Properties. To see a description of each field property, click the property and read the description in the box next to the property list under Field Properties.

You can get more detailed information by clicking the Help button. For Text fields, this property sets the maximum number of characters that can be stored in the field. The maximum is For Number fields, this property sets the type of number that will be stored Long Integer, Double, and so on. For the most efficient data storage, it is recommended that you allocate the least amount of space that you think you will need for the data.

You can adjust the value upwards later, if your needs change. This property sets how the data is displayed. It does not affect the actual data as it is stored in the field. You can select a predefined format or enter a custom format.