MS Access: Table Design & Structure of New DB

Posted on 2011-04-19
Last Modified: 2012-05-11
I am starting over from scratch on a DB. I would like some input based on my proposed diagram and table structure of the DB.

This database needs to:

Record the Inspection results on a variety of different precision instrument measurement tools and send that data to the respective tables of each instrument and then present:

1)      A minimum measurement allowance and a maximum measurement allowance for each instrument checked.
2)      Show a Range of those measurements taken.
3)      Present the Standard Deviation of the measurements taken.
4)      Present an average of the measurements taken.
5)      Present an Accuracy Rating of the measurements taken.
6)      Present a Repeatability Rating confidence factor.

I next will need to develop Forms that can handle new data input and sub forms that can present the info from prior inspections and present that info in the sub form that would be represented by the numbers 1 thru 7 for each instrument type.

What I would like is for someone to review my table structure and table relationships to see if I am making a good start with my design for this database. I have nothing else developed at this point so lots of changes can be made.
Question by:James Coats

    Author Comment

    by:James Coats
    Forgot to add the file! sorry.
    LVL 16

    Expert Comment


    First, the fact that you have numbers are field names signals a red flag. Better have


    Also most importantly you should be using the autonumbers to link the tables in the relationship.
    LVL 16

    Expert Comment

    Your table tool list contains duplicate. That should not be the case. Split that into two table say tbl_lkp_toollist (which is a lookup table without duplicate) and tblTool which lookup the id of the tool from tbl_lkp_toollist and store information about a particular item eg Location,brand, code model ect...

    Think in term of parent child. Eg A tool has a temperature, so the toolID should be used as the relationship between tool and temperature table not the temperature id
    LVL 16

    Expert Comment

    Standard deviation is a calculated value and should not be stored in tables. This is a job for queries. Table should only store data that does not need to be calculated. Eg tool name, measurements, dates, employee names ect..
    LVL 16

    Expert Comment

    Also I have found out that just saying the process out load often help figure out the structure.

    For example in this case:

    The tool is tested on a particular date and the record is store in an inspection log. The inspection consist of temperature, dimension,deflection,moisture ...... checks.

    So the relationship becomes clear:

    Tool table is linked to inspection table through the toolid
    The various checks are linked to the inspection table by the inspectionlogID.

    See attached

    LVL 16

    Accepted Solution

    I,m off to work know hope that will give you something to work with and also refer to:

    Catch you later

    Author Closing Comment

    by:James Coats
    So you are saying I should have a table called "tblToolName" which contains all the tool names & a second table "tblTools" which contains all the info about the tools linked via foreign keys to "tblInspections".

    Then the "tblInspections" links via foreign keys to "tblTemperature", tblMoisture, tblDeflection & "tblDimension"

    I can better understand this if you were able to use Access but maybe you don't have it installed on your computer. Any way I will attempt new design on your input maybe you can check it out later when I ask for more input on the new design. Thanks
    LVL 16

    Expert Comment

    I have access but I thought this diagram would have been enough to give you the gust of it. Looks like you did. What you said is exactly what I meant. Iwould have prefer a bit more discussion on the number field as I do not quiet understand what they mean and how they will be applied.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
    Creating and Managing Databases with phpMyAdmin in cPanel.
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now