MS Access: Review of DB Structure & Table Relationships

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.
James CoatsComputer Info. Sys. StudentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

From the first view all seems to be OK, exept join between tables TblInspections and TblTools. May be I don't understand task, but now you don't need ToolID.

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
James CoatsComputer Info. Sys. StudentAuthor 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. StudentAuthor Commented:
Thanks for your time.
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.
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.