[Last Call] Learn how to a build a cloud-first strategyRegister Now


MS Access: Review of DB Structure & Table Relationships

Posted on 2011-04-20
Medium Priority
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.
Question by:James Coats
  • 2
LVL 40

Accepted Solution

als315 earned 1500 total points
ID: 35433212
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.

Author Comment

by:James Coats
ID: 35433636
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" ?

Author Closing Comment

by:James Coats
ID: 35434456
Thanks for your time.
LVL 16

Expert Comment

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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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 …

834 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