Results 1 to 2 of 2
  1. #1
    kroghr is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    1

    Employee Master File Database, Design Question

    Seeking help with developing tables



    TblEmployees, TblDependents, TblPassports

    Employees may have many passports (Expirations)
    Employees may have many dependents
    Dependents may have many passports

    Would the correct table structure be?

    EmployeeID (Primary)
    EmployeeName
    EmployeeEtc
    EmployeeEtc

    DependentID (Primary)
    EmployeeID (Foreign)
    DependentName
    DependentEtc

    PassportID (Primary)
    EmployeeID (Foreign)
    DependentID (Foreign)
    PassportNumber
    PassportDOB
    PassportPOB
    PassportExpirations
    PassportEtc

    Thank you for your time.

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Welcome to the forum

    The correct structure is whatever works for you for whatever the db is required to do.

    e.g. an alternative, since employees and dependants have much the same information, you could combine the two tables with the addition of an EmployeeFK field to indicate they are a dependant of that employee (it would be blank for an employee) Then you would not need the DependantFK field in tblPassports and would simplify the relationships.

    Either way, you need to do some 'what ifs' e.g.

    What if a dependant is also an employee?
    What happens to the dependants if an employee leaves?
    What if a dependant is a dependant to more than one employee?
    etc

    Why do you want the passport history? Wouldn't current passport details be sufficient?
    Wouldn't DOB be a field for the employee/dependant table?

    The above is not saying you have done it wrong, just questioning whether you have thought it through enough to meet your needs

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

Similar Threads

  1. Employee Time Tracking Database Design
    By shell159 in forum Database Design
    Replies: 1
    Last Post: 01-12-2016, 02:58 PM
  2. Replies: 2
    Last Post: 10-04-2014, 01:58 PM
  3. Replies: 3
    Last Post: 03-13-2014, 11:21 PM
  4. Database Design for employee
    By Ramya in forum Database Design
    Replies: 1
    Last Post: 07-29-2011, 11:57 AM
  5. Help with Table Design for Employee Task Database
    By shelbsassy in forum Database Design
    Replies: 6
    Last Post: 04-08-2011, 05:14 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