Results 1 to 3 of 3
  1. #1
    nunzii is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Apr 2013
    Posts
    1

    Question Relationship / Overall Design Question


    I do hope this is the proper section to be posting in but I'm looking for some input on my current database and relationships. I'm still very new to Access and have only learned what little I do know from books and online tutorials. I am trying to create a mixture of an equipment and personnel database..here is what my relationship table looks like.. http://i.imgur.com/4K5YUsf.png

    My overall goal is to have a form that can edit the Personnel Master, Driver Files, Training Files, and LCQS from one form but I'd like some input on how the database is laid out currently and if there are some things I am overlooking that could make this process smoother...hopefully the relationship view will give enough of an overview to tell?
    Attached Thumbnails Attached Thumbnails 4K5YUsf.png  

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The 1-to-1 related tables could be all one table unless exceed 255 fields. I know it doesn't quite follow full normalization if every personnel will not have training, driver, LCQS data but does eliminate need for joining in queries and subforms for data entry. Either way will work. LCQS table does not appear normalized. Multiple similar name fields is an indication of that. This could cause issues with filtering and sorting on whatever GCPT is. If there are only the 4 that can be dealt with but if you some day want to add more that means modifying table, queries, forms, reports. I think this is more critical than the split off of 1 to 1 data.

    Consider saving Status instead of StatusID in Equipment if the Status value is short. This eliminates issues related to lookup alias and join in query to retrieve Status.

    PersonnelMaster has no lookup for Status.

    How many Status values could there be?

    Avoid spaces and special characters/punctuation (underscore is exception) in names. If used can require names enclosed in [] when referenced in code and if the [] omitted code can fail.

    Otherwise, a very nice start.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    My comment is that using the SSN as a primary key is not a good idea. An autonumber type field would be better for the primary key.

    People do not like to have their SSN broadcast or displayed (in this day and age) - considered private information.
    SSNs are not guaranteed to be unique. they are in fact reissued in special cases.
    Not everyone has a SSN

    The SSN field is probably a text field - therefore it is very inefficient to index, difficult to use in joins, increases storage space, and cumbersome to replicate across multiple tables as foreign keys.

    My $0.02

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

Similar Threads

  1. Problem with table and relationship design
    By fekrinejat in forum Database Design
    Replies: 4
    Last Post: 03-19-2013, 12:11 PM
  2. Table and Relationship Design
    By GrayWolf in forum Access
    Replies: 6
    Last Post: 01-31-2012, 02:04 PM
  3. Multiple Table Relationship Design
    By neo651 in forum Database Design
    Replies: 1
    Last Post: 09-30-2011, 01:16 AM
  4. Relationship Design
    By krymer in forum Database Design
    Replies: 3
    Last Post: 11-28-2008, 09:09 PM
  5. design using relationship...
    By dsnyder in forum Database Design
    Replies: 2
    Last Post: 10-21-2008, 12:00 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
  •  
Other Forums: Microsoft Office Forums