Results 1 to 4 of 4

Normalization

  1. #1
    kaylachris is offline Novice
    Windows Vista Access 2007
    Join Date
    May 2010
    Posts
    19

    Normalization


    Steve (a.k.a. ssanfu) kindly informed me on a previous post (http://www.accessforums.net/showthread.php?t=75955) that my structure lacked normalization. After, reading the material he provided I'm back at my attempt of normalized data. If one of you gurus could take a peak at the attached db and provide feedback on what areas still need work I'd greatly appreciate it. Also, if I could get a bit of help how I can implement my tbl_PersonnelCompPayGrade_J. It's meant to ensure that there is only one of each paygrade in each component... I just can't seem to figure out the best way to put it in place.
    [ATTACH]Table Structures[/ATTACH]
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    46,389
    Can you explain why students would have Component, PayGrade, and Rank?

    Can each faculty have multiple user names? If not, this data could just be fields in tbl_Faculty.

    My POV is normalization should be a consideration in db design but not a commandment - "Normalize until hurts and denormalize until it works".

    Paygrade and component are set as compound key in tbl_PersonnelCompPayGrade_J. This should assure each pair is unique. However, they don't have to be key fields, can be just compound index.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  3. #3
    kaylachris is offline Novice
    Windows Vista Access 2007
    Join Date
    May 2010
    Posts
    19
    Component is what branch of service a student is in (Army, Marine, etc..) and distinguishes if they are active duty, reserve, or National Guard.
    PayGrade is used to determine additional training that is required as well as a few additional forms required to be produced at graduation.
    Rank is determined by PayGrade but can very depending on Component (i.e. O6 in the Army is a Colonel but a Captain in the Navy). Furthermore, each PayGrade in Component can have multiple ranks depending on a persons position. (i.e. Air Force E9 can be a Chief Master Sergeant, 1st Sergeant, or Command Chief Master Sergeant) It's a required piece on many of the documents that must be produced.

    Each faculty is limited to only one Username. However, there will be users that aren't faculty. A table to be built at a later date once its determined who else should have access to this data.

    tbl_PersonnelCompPayGrade_J: component and paygrade ensures that only one of each paygrade exist for each component... but the table itself doesn't actual serve a purpose. Thinking about just deleting it and storing in separate fields in the Faculty and Student tables for now and revisit it later to figure it out.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    46,389
    Faculty are also linked to the same table for component/paygrade/rank (the reason for my confusion). All persons are military personnel? Would it be possible for faculty to be a student and vice versa?

    Comment about users table still applies. Yes, there could be a lot of blank cells in tbl_Faculty but that could be tolerated for simple structure, at least I probably would.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Normalization
    By jzacharias in forum Database Design
    Replies: 9
    Last Post: 05-23-2015, 11:26 PM
  2. Normalization
    By jlt199 in forum Database Design
    Replies: 10
    Last Post: 03-21-2014, 11:22 AM
  3. Need Help about Normalization
    By Book3s in forum Access
    Replies: 2
    Last Post: 10-24-2013, 01:03 AM
  4. Normalization
    By KPAW in forum Database Design
    Replies: 1
    Last Post: 06-09-2011, 05:24 PM
  5. Normalization
    By U810190 in forum Access
    Replies: 1
    Last Post: 03-30-2010, 03:55 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums