MS Access: Review of DB Structure & Table Relationships

Posted on 2011-04-20
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
    LVL 39

    Accepted Solution

    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
    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
    Thanks for your time.
    LVL 16

    Expert Comment

    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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now