We help IT Professionals succeed at work.

MS Access: Review of DB Structure & Table Relationships

James Coats
James Coats asked
on
Medium Priority
305 Views
Last Modified: 2012-08-13
I have just redesigned a db from the ground up based on suggestions from EE and would like for someone to review what I have done and make suggestions for improvement. This db is for the recording of calibration tests on precision instruments. At this point I can make any changes that are needed. I had to scrap the old db because the design was not well thought out and a lot of effort was wasted. I want to start out with a good design and structure with good table relationships.

Thanks in advance.
Measuring-Instruments-3-4-20-11.mdb
Comment
Watch Question

CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
James CoatsComputer Info. Sys. Student

Author

Commented:
OK, I have just always given every table a primary ID. I can see your point. Now I see something else. The connection between "tblToolNames" and "tblTools" is good but the connection between "tblTools" and "tblInspections" shouldn't the foreign key "Tool_FID" be named "ToolNameID" ?
James CoatsComputer Info. Sys. Student

Author

Commented:
Thanks for your time.

Commented:
OK This question is closed but noting that the design is from my original suggestion that I have already been awarded points for I will throw in a few suggestions.

I have made some changes to your sample namely:

Insert a new table called tblToolType which is a lookup table for the type of tool

Deleted the type and description field from the tool table and added them to the toolname table

In the toolname table I have made tooltype a number type data field that looks up to table tooltype.

I have added the suffix fld in front of the field names in the tables that I have modified. You have to do that to all the fields in all the tables. Also avoid space in field names. This will reduce a lot of frustration in future stage when you start getting into vba codes to purform some of the actions that you will undoubtedly want you database to do. Read a bit about MS Access naming convention. Plenty of stuffs on google. There is a good article on uk.database, I send you the like to this site in previous post.

Change relationship between tool and inspection tables. Primary key should be the primary key in the tool table

As mentioned before, I am concerned about the numbers used as fields in the deflection and dimension tables. Are these numbers the only possible values that you can get for all the tools. It looks wrong but without know the exact nature of the test I can't say for sure.
 
Measuring-Instruments-3-4-20-11.mdb
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.