As I have preached many times and so it seems other Access database experts are of the same opinion, designing an Access database requires a plan, time and a methodical approach.
I found this article which is another perspective but similar to my 7 step MS Access database plan that may be worth a read – check it out below…
Create an MS Access Database – 9 Simple Steps to a Productive Database
STEP 1: Define the Purpose
Before you try to create an MS Access database first decide what the purpose for the database is. Software is much like a vehicle. Cars transport people from one place to another. Trucks can carry a few people and lots of cargo. The database you create should have a mission and purpose. It may be to track jobs, log activity, record and monitor sales or any of thousands of purposes.
STEP 2: Define Your Workflow
Most people skip this step before trying to create an MS Access database. You’ll regret it if you do! You have work to get done and already have steps you are probably following now to accomplish the job. You’re not yet using an access database to accomplish these tasks. Write these processes down in a step by step fashion. This is your workflow. Workflow usually starts with the arrival of some type of information. That information will need to be entered into the database once it’s built. What are you currently doing with that information? Using spreadsheets? Using paper? What steps are you taking once you receive that information? Do you process it? Does it go to someone else for approvals? Write down the workflow you are currently using to process that information from beginning to end. This is essential before creating your Access database.
STEP 3: Identify Forms & Reports
Next, take that workflow and review it step by step. Mark beside each step where you will need to enter information or retrieve information. Really that’s all you can do with a computer – put information in or get information out. Write down, beside each step, on your workflow the points where you need:
- To enter data (form)
- To modify data (form)
- To View data on the screen (form)
- To print data on a report (report)
STEP 4: Design Forms & Reports
After marking up your workflow with forms and reports you should next should draw out, on paper, what you want each form and each report to look like. This will become your database design. I suggest you use paper first. This is where you get creative and using paper is quick and simple. On each form and report you’ll want to write the names of the fields you want to use or see. Draw the buttons you will need too. Each form or report needs a title also. This will help you think and discover exactly what fields you need and where they need to be placed. Then, look over your reports and make sure you know where the data came from for that field or column.
STEP 5: Design Your Tables
Now you can design your tables. Based on each of your forms you can see what tables you will need. Normally each form will have a table, and that table will contain the fields from your form. Some of those fields may come from other tables but the bulk will normally come from one table.
- List the tables you will need and the fields for each.
- Each table should have a unique identifier “Primary Key” for each record. For example; if the table is full of drivers then a “Driver ID” key would be assigned to each driver record in the table. No two primary key fields in the database should be named the same.
STEP 6: Discover Your Relationships
Relationships in Access are not designed, they are discovered based on your actual business practices. Look for one-to-many or parent-child relationships. And place a foreign key in each of the child tables. For example, if you had a State table and a County table you know each state has many counties. This is a parent-child relationship. The State table should have a Key field ID. Place this same field ID in your County table. Make sure it’s the same field type, but is not a key field. The County table should have its own Key field.
STEP 7: Build Your Tables
After designing your tables you can build your tables. Microsoft included lots of tools to help you build the components for your database. I will not get into all the details in this article. It’s most important though, that you build your tables before you build your forms.
- Create your tables
- Create your fields
- Set your field properties
- Establish your relationships
STEP 8: Build Your Forms
After building your tables then you build your forms. Follow the paper design so you know where your fields should be placed. You can use wizards or add them by hand. Make sure you build your tables first before you build your forms.
STEP 9: Build your reports
After building your forms enter some test data so you can see what you are doing on your reports. Building reports is usually the last step.
As you can see the process goes in reverse when you build your database. If you don’t design from the top down and build from the bottom up then you will run into the problems 95% of people do when they try to create an access database.
THE RIGHT WAY TO CREATE AN MS ACCESS DATABASE
- Design Forms and Reports on paper
- Design Tables on paper
- Build Tables
- Build forms and reports
THE WRONG WAY TO CREATE AN MS ACCESS DATABASE
- Start building forms on the fly
- Try to build the tables to work with the forms
- Change the forms to fit the tables
- Build the reports
- Change the tables to fit the reports
- Change the forms to fit the tables
- Keep repeating the above steps until you are completely confused and frustrated!
Joe Richardson is a professional database developer and has been building MS Access databases since 1996. He has more than 300 clients in 4 countries across varied industries.
Article Source: http://EzineArticles.com/5922913
Designing An Access Database: Some Simple Steps For Your New MS Access Database
The most pleasing aspect to this article is not just the detail it covers but how we all start with the end product in mind i.e. Forms and Reports and learn to reverse engineer down to the Tables defining fields, data types and their properties.
Designing an Access database can be considered an art and a skill where I know some IT experts do only just this as their profession.
If you want to know more about designing, creating and implementing all aspects to MS Access, why not start with my Microsoft Access eBook bundle offer?
Any hints and tips from you would of course be very welcomed (in the comment box below).