Database design is all about the preparation you commit to before you even touch the Access program. As a bonus, by walking through the database design process you’ll begin to understand how Access thinks and processes information. And, once you understand how a Relational Database Management System like Access thinks, everything else related to database design just falls into place.
Step One: Determine the Overall Database Purpose
The first step to designing a database is to determine its purpose and how it’s to be used:
Talk to people who will use your database. Brainstorm about the questions you and they would like the database to answer.
Sketch out the reports you’d like the database to produce.
Gather the forms you currently use to record your data.
As you determine the purpose of your database, a list of information you want from the database will begin to emerge. From that, you can determine what facts you need to store in the database and what subject each fact belongs to.
These facts correspond to the fields (columns) in your database, and the subjects that those facts belong to correspond to the tables.
Step Two: Business Process Review
During the Business Process Review, you’re simply looking to get your hands on anything that currently displays or tracks information that should be in your database.
Items you may look at include:
Internal Business Forms
External Business Forms
While the ideal is a “paperless” office, that’s probably not a realistic goal. However, you can attempt to store as much information as possible in your database. Remember, always consider the types of information (reports) that you’ll be pulling out of the database. The information you want to pull out will absolutely determine the information that will need to go into the database.
You can probably already see how looking at your current business process will help define the fields your database will contain.
Step Three: Field Determination
You should have a good idea of fields you’ll need after finishing your business process review. The rest of your fields can come from the people who will be using your database.
What sort of data do you expect the database to contain?
What kinds of reports would you like to be able to generate from the database?
What types of summary information do you expect the database to produce?
What kinds of questions do you want the database to be able to easily answer?
In every database development project, requirements will change. It’s so rare that a user can come up with every scenario when you first speak with them. So, it’s your job to make sure the user understands what your skills are as they relate to Access. This way, they will be prompted to think a little “outside of the box” up front. Which, for you, means less work down the road.
Each field is a fact about a particular subject. For example, you might need to store the following facts about your customers:
- company name
- phone number
You need to create a separate field for each of these facts. When determining which fields you need, keep these design principles in mind:
Store information in the smallest logical parts. For example, employee names are often split into two fields, FirstName and LastName, so that it’s easy to sort data by LastName.
Don’t include calculated data. For example, if you have a UnitPrice field and a Quantity field, don’t create an additional field that multiplies the values in these two fields.
Don’t create fields that are similar to each other. For example, in a Suppliers table, if you create the fields Product1, Product2, and Product3, it will be more difficult to find all suppliers who provide a particular product. Also, you will have to change the design of your database if a supplier provides more than three products. You need only one field for products if you put that field in the Products table instead of in the Suppliers table.
Step Four: Initial Table Layout
As you know, Tables are the heart of any Access database. Tables are where you data is stored and provide the backbone for:
Queries Forms Reports
Table Creation Guidelines
When in the database planning stages, you should spend a great deal of time and thought on which fields should go into which tables. Here are some table creation guidelines to consider when looking to place the fields you came up with Step Three into tables:
A table should hold information on only one subject
Don’t be concerned about having too many tables
Try to identify tables that correspond to tangible objects such as people, physical objects and so on
Table and Field names can be no more than 64 characters
Names cannot include (.), (!), (‘), () or (“)
It’s good design to eliminate spaces in the field and table names, although it isn’t required
Your list of fields will provide clues to the tables you need. For example, if you have a HireDate field, its subject is an employee, so it belongs in the Employees table. You might have a table for Customers, a table for Products, and a table for Orders.
Using Post-It™ Notes to Plan Your Tables
Post-it Notes are an easy, accessible way to organize your fields into tables. You simply write the name of each field on an individual Post-It note. Then, write the name of each table or group on another note.
Using a wall or the back of your office door, begin placing each field name under each table name/group. This way, you can visually see the layout of your tables before you are committed to any in a database table design.
When you decide which table each field belongs to, keep these design principles in mind:
Add the field to only one table.
Don’t add the field to a table if it will result in the same information appearing in multiple records in that table.
When each piece of information is stored only once, you update it in one place. This is more efficient, and it also eliminates the possibility of duplicate entries that contain different information.
If you determine that a field in a table will contain a lot of duplicate information, that field is probably in the wrong table.
Step Five: Data Normalization
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.
At this stage in the process, you’ll want to create your table structure in Access and enter enough sample data in your tables so you can test your design. In this step, you’re simply entering sample data in your table and looking for any potential problems, such as:
There should be no unnecessarily repeated data values
All fields in a table should pertain to every record
Each field should contain the smallest meaningful value
Normalized vs. Denormalized
Data is considered “normalized” when the above conditions are met.
Data is considered “denormalized” when a designer chooses to ignore one or more normalization conditions for any reason.
Step Six: Designate Primary and Foreign Keys
The primary and foreign keys determine how the tables in your database relate to each other.
A primary key is a field or combination of fields that contains a value that uniquely identifies a record
Primary key fields:
Have values that are never blank
Include as few fields as necessary
Never allow duplicates
Can be any datatype
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.
A foreign key field contains a value that relates to a primary key field.