MS Access: Table Design & Structure of New DB

Posted on 2011-04-19
Medium Priority
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
  • 6
  • 2

Author Comment

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

Expert Comment

ID: 35428408

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

ID: 35428477
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

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 16

Expert Comment

ID: 35428497
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

ID: 35428706
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

Sheils earned 1500 total points
ID: 35428756
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
ID: 35428869
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

ID: 35431310
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.

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

809 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