MS Access: Table Design & Structure of New DB

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.
James CoatsComputer Info. Sys. StudentAsked:
Who is Participating?
 
SheilsConnect With a Mentor Commented:
I,m off to work know hope that will give you something to work with and also refer to:

http://www.databasedev.co.uk/good_db_design.html
http://www.databasedev.co.uk/table_design_tips.html
http://www.databasedev.co.uk/1norm_form.html

Catch you later
0
 
James CoatsComputer Info. Sys. StudentAuthor Commented:
Forgot to add the file! sorry.
Measuring-Instruments-3.mdb
0
 
SheilsCommented:
OK

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

Deflection_Verification
-------------------------
DefVer_id
DefVer


Also most importantly you should be using the autonumbers to link the tables in the relationship.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
SheilsCommented:
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
0
 
SheilsCommented:
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..
0
 
SheilsCommented:
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

bmp
0
 
James CoatsComputer Info. Sys. StudentAuthor Commented:
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
0
 
SheilsCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.