With Microsoft Access or any relational database methodology, you can change a databases structure at any stage of the design or go-live phases of a project. However it is strongly advised that you get everything nailed down before you start creating forms, reports, queries and decision support questionnaires, otherwise your life as a database user, analyst or (heaven forbid) administrator will get considerably more challenging. That is because without good design, database management can become a monstrously time-consuming administrative task.
In order to create an effective relational database that meets your specific needs, either personal or as a group, it is of paramount importance that you start by defining the purpose of your database. In simple terms, consider these questions: ‘What exactly do we want to achieve with this database?’ and ‘What data do we need to extract?’ If you know the answers to these questions then, and only then, can you clearly define your objectives and decide what data needs to be stored, in what format for effective data retrieval via relational tables and within their corresponding fields.
Designing the database
The more time you spend planning and organizing your data structure the better served you will be in the long run. The key to designing an efficient database is to understand how any relational database system stores information and how it can be viewed.
Here are the important points
Point A:- Relational databases such as those written in Microsoft Access do not store data in a logical way that you would perceive.Microsoft Access requires a whole host of facts about subjects that are to be stored, the relationships between each subject and the indexes that separate them, but keep them connected.
Point B:- These said subjects and the relationships they share must maintain relevant data items, without duplication or redundancy and with considerable thought to validation. If data is duplicated or wasted blank space then it reduces the capacity of the database to provide you with the service you desire. Be it speed, resilience or aesthetics.
In the next part in this series we will discuss a phased approach to database design, looking at brainstorming workflows, drawing up realistic database goals and determining fields, tables and their relationships. Following this, it will then be possible to begin designing your prototype system, automating / validating data inputs and output mechanisms and finally building a test harness that is both pertinent and relevant to your bespoke requirements.
This post is part of the series: Designing a Database with Microsoft Access
- Microsoft Access: Designing a Relational Database (Part 1)
- Microsoft Access: Understanding Data Types
- Microsoft Access: Understanding Field Properties
- Microsoft Access: Understanding Lookup Fields
- Microsoft Access: Join Properties and Join Types
- Microsoft Access: Defining Relationship Types
- Microsoft Access: Setting Up Relationships Between Tables
- Microsoft Access: Designing a Relational Database (Part 2)