Pin Me

Microsoft Access: Designing a Relational Database (Part 1)

written by: •edited by: Bill Fulks•updated: 4/2/2012

The most critical aspect of creating a database is excellent design. Without a core set of principles and structure you will be forever chasing your tail, reworking data and tables in an attempt to capture and extract the information you require. So lets see what makes good database design.

  • slide 1 of 2

    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.

  • slide 2 of 2

    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.






© Copyright 2016 brighthub.com.