Designing a database with Microsoft Access: A Phased Approach to Relational Database Design

Page content

Previously we discussed the importance of good database design which you must do prior to even thinking about building tables, queries, reports, etc. As such, in order to design a database that suites you as an individual or workgroup it is best to break down the design process into the following phases.

Phase 1 - Determine what purpose the database will serve

At the very outset it is best to work with a core group of people who understand your information requirements so that you can ascertain what the database is required to achieve. Discuss the relevance of the database, information retrieval techniques which will work for you and the core levels of expertise which will be at your disposal. Then discuss what data items need capturing and maintaining for an up-to-date database that best suites your work flow and decision-making. Brainstorm your discussions and formalize a plan, all the while questioning why you need a database? What is it’s purpose? And how you think it will energize your information management?

Phase 2 - Determine what core data fields are required within the database

Following on from phase 1 you need to really focus on what specific data you need the database to store. For example a customer database would need a heavy contacts database structure including financial, demographic and marketing information, etc. A healthcare database on the other hand would need to include patients’ demographics, previous clinical episodes of care and case note tracking, etc. It is important that you break this information down into the smallest elements. For example individual fields for a personal demographic record would include Title, Forename, Surname, Address Line 1, Address Line 2, Address Line 3, City, State, Zip Code, Sex, Telephone No. and a whole host more. Do not cut corners when assessing what data fields you may need. This can become an exhaustive set of lists but it is crucial to both the build stability and and the databases long term relevance.

Phase 3 - Determine what tables are required within the database

Within any database, certainly one that is relational, tables need to be structured based on subject detail and field elements that drive them. Here you can drastically reduce the standard problems that many databases suffer from by eliminating redundant and repetitive data items that are commonly found across multiple tables. By tightening up your table design you will reduce the impact of duplicated fields in multiple tables, optimize the size of your database and create separated tables which can be indexed and linked for quick data retrieval.

Phase 4 - Determine the relationships of each table and the primary data fields

Now you can pull together the field and table structures by formulating a relational flow between tables based on primary and foreign keys. Look at which data items may be used for interlinking your tables and create identifier ID’s. It is at this stage that you should be able to clearly envisage how your data output is going to flow.

Phase 5 - Refining your database design structure

As you now have a clear picture of what data fields will be present in which tables, and how these tables need to relationally interact: this is a good stage to check back on the key objectives and process mappings of phase 1. Make sure you are covering your entire flow, check for gaps in data flows and deduce whether the required outputs can be achieved from the data model you have proposed.

Phase 6 - Designing your prototype forms

When you are happy with the design of your tables and the various entities connecting them, you need to establish what type of input forms your end users will need to view and use. They need to be easy to manipulate, user friendly and most of all encapsulate all of the information that you require populating, including validation of mandatory and non mandatory fields. Of course you can add as many whistles and bells to such form design as you like but remember the key reason for the database must be functionality, as such don’t get distracted from this endpoint.

Phase 7 - Automate the design

Having created your input forms with various controls, graphics, labels and textboxes, it may now be worthwhile to create an opening screen: commonly known as a switchboard of menus to ease the end users navigation around your database. Such navigational aids are non essential as you might have expert enough users who are happy running table queries and reports direct within the application. However a switchboard with clearly labeled sections for data input, table viewing and query / report running can save considerable time and effort for end users. It is definitely worth a thought.

Phase 8 - Creating a test harness

Your database design is now complete. All that is left is testing the database from top to toe. Testing the mechanisms you put in place for every form, report, query and relationships can be a complicated task. Thus carefully consider the test harness and test data that is going to give you the most comprehensive test of all possible outcomes. Test the process of data entry, all conditions that apply to each field and table, check that error messages are acknowledged and returned to aid problem-solving and troubleshooting. Run your outputs to check response times and validity of information. For example it is at this stage (before you populate your database with a million records!) that you want to make sure mandatory fields require an input, that date fields have the correct format and that currencies show with the correct symbol and validation. Crucially you also must test the relationships between tables so that they don’t have the wrong primary and foreign keys, which will render them useless - potentially even hanging your entire system.

So we’ve discussed the importance of database design, and phased the design stage for a relational database model. In the following articles we will look at the importance of understanding Data Types and Field Properties.

Microsoft Access: Designing a Relational Database (Part 1)

Microsoft Access: Understanding Data Types

Microsoft Access: Understanding Field Properties

Microsoft Access: Understanding Lookup Fields

This post is part of the series: Designing a Database with Microsoft Access

Need some help navigating Microsoft Access? This tutorial shows you how to design a database that works smoothly for all your projects.

  1. Microsoft Access: Designing a Relational Database (Part 1)
  2. Microsoft Access: Understanding Data Types
  3. Microsoft Access: Understanding Field Properties
  4. Microsoft Access: Understanding Lookup Fields
  5. Microsoft Access: Join Properties and Join Types
  6. Microsoft Access: Defining Relationship Types
  7. Microsoft Access: Setting Up Relationships Between Tables
  8. Microsoft Access: Designing a Relational Database (Part 2)