.Before you use Microsoft Access to actually build the tables, forms, and other objects that will make up your database, it is important to take time to design your database. Whether you are using a Microsoft Access database or a Microsoft Access project, good database design is the keystone to creating a database that does what you want it to do effectively, accurately, and efficiently.
Learn more about Microsoft Access databases and how they work.
Learn more about Microsoft Access projects.
This topic provides reference information about these basic steps in designing a database:
The first step in designing a database is to determine its purpose and how it's to be used. You need to know what information you want from the database. From that, you can determine what subjects you need to store facts about (the tables) and what facts you need to store about each subject (the fields in the tables).
Talk to people who will use the database. Brainstorm about the questions you and they would like the database to answer. Sketch out the reports you'd like it to produce. Gather the forms you currently use to record your data. Examine well-designed databases similar to the one you are designing.
Return to top
Determining the tables can be the trickiest step in the database design process. That's because the results you want from your database — the reports you want to print, the forms you want to use, the questions you want answered — don't necessarily provide clues about the structure of the tables that produce them.
You don't need to design your tables using Microsoft Access. In fact, it may be better to sketch out and rework your design on paper first. When you design your tables, divide up pieces of information by keeping these fundamental design principles in mind:
When each piece of information is stored in only one table, you update it in one place. This is more efficient, and it also eliminates the possibility of duplicate entries that contain different information. For example, you would want to store each customer address and phone number only once, in one table.
When each table contains facts about only one subject, you can maintain information about each subject independently from other subjects. For example, you would store customer addresses in a different table from the customers' orders, so that you could delete one order and still maintain the customer information.
Learn about tables in a Microsoft Access database and how they work. Learn about optimizing general table performance in an Access database.
Learn about optimizing performance in a Microsoft Access project.
Return to top
Each table contains information about the same subject, and each field in a table contains individual facts about the table's subject. For example, a customer table may include company name, address, city, state, and phone number fields. When sketching out the fields for each table, keep these tips in mind:
Learn about available field data types in an Access database.
Compare field data types in an Access database with those in an Access project.
Return to top
In order for Microsoft Access to connect information stored in separate tables — for example, to connect a customer with all the customer's orders — each table in your database must include a field or set of fields that uniquely identifies each individual record in the table. Such a field or set of fields is called a primary key.
Learn about primary keys in an Access database.
Return to top
Now that you've divided your information into tables and identified primary key fields, you need a way to tell Microsoft Access how to bring related information back together again in meaningful ways. To do this, you define relationships between tables in a Microsoft Access database.
You may find it useful to view the relationships in an existing well-designed database. For example, open the Northwind sample database and click Relationships on the Tools menu to see the relationships between its tables. Or you may want to view the relationships in the Northwind sample Access project and Microsoft SQL Server database.
Learn more about referential integrity.
Return to top
After you have designed the tables, fields, and relationships you need, it's time to study the design and detect any flaws that might remain. It is easier to change your database design now than it will be after you have filled the tables with data.
Use Microsoft Access to create your tables, specify relationships between the tables, and enter enough sample data in your tables so you can test your design. To test the relationships in your database, see if you can create queries to get the answers you want. Create rough drafts of your forms and reports and see if they show the data you expect. Look for unnecessary duplications of data and eliminate them. If you find problems, refine the design.
Learn more about queries in an Access database and how they work.
Return to top
When you are satisfied that the table structures meet the design principles described here, then it's time to go ahead and add all your existing data to the tables. You can then create any queries, forms, reports, data access pages, macros, and modules that you may want.
See a comparison of database objects in a Microsoft Access project and database objects in an Access database.
Learn more about importing data into an Access database.
Return to top
Microsoft Access includes two tools that can help you to refine the design of your Microsoft Access database. The Table Analyzer Wizard can analyze the design of one table at a time, can propose new table structures and relationships if appropriate, and can divide a table into new related tables if that makes sense. For information on running the Table Analyzer Wizard, click .
The Performance Analyzer can analyze your entire database and make recommendations and suggestions for improving it. The wizard can also implement these recommendations and suggestions. For information on using the Performance Analyzer, click .
For additional ideas on designing a Microsoft Access database, you may want to look at the design of the Northwind sample database or of one of the databases that you can create with the Database Wizard. For information on using the Database Wizard, click .
See Microsoft SQL Server documentation for additional ideas on analyzing your Microsoft SQL Server database to improve its performance.
Return to top