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.
Measuring-Instruments-3-4-20-11.mdb
Thanks in advance.
Measuring-Instruments-3-4-20-11.mdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Measuring-Instruments-3-4-20-11.mdb
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
ASKER