Results 1 to 5 of 5
  1. #1
    fekrinejat is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    29

    Problem with table and relationship design

    Hi
    My DB has got 3 tables (Employee, JobHistory , Activity) at the end of every month, activity of employee and his/her job situation will be inserted ..

    My tables and relationships are:
    Employee (PK: Person_No)
    JobHistory(FK: Person_No)


    Activity (FK: Person_No)

    But i think in this design, there would be data dupication especially in Activity Table , each employee has at least one record in Activity table every month, that would be plenty of records after one year ..

    any idea about better design ?

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,822
    Many Employees and Many Activities suggests M:M.
    This would usually be resolved with a junction table
    eg tblEmployeeActivity

    EmployeeID(FK)
    ActivityID(FK)
    ActivityDate?

    But we haven't seen what fields you have in your tables, nor what a typical transaction would be.
    Where do Job and Activity fit?
    Can you tell us a little about "your business"?

  3. #3
    fekrinejat is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    29
    The business has some offices around the city. employees gonna work in each office and we would like to track their offices history, and also some of their activities like overtime hours, etc ..
    it looks like a many to many relationship but i don't think so .. because we have one Person
    and alot of activities belong to that person. even i have year and month fields in activity table ..

    for example after 5 months we have 1 person (one record in Employee Table) and 5 records in Activity table
    so it is One-Many relationship .. and every record in Activity table contains data dedicated to one person and might not be used for others.. although it might be wrong design , any better idea ?

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,822
    Do you have more than 1 employee that is involved in the business you will record?
    Do you have a list of all possible Activities?

    You may wish to watch this tutorial and see if it fits your situation.
    https://www.youtube.com/watch?v=JBG4SzNhA9A

    and/or
    https://www.youtube.com/watch?v=7XstSSyG8fw

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,822
    *********Duplicate post after system crash *************
    Last edited by orange; 03-19-2013 at 12:12 PM. Reason: Duplicate/system crashed

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

Similar Threads

  1. Replies: 2
    Last Post: 11-19-2012, 10:54 AM
  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. Table/Relationship Problem?
    By WallbrownF in forum Access
    Replies: 7
    Last Post: 05-26-2011, 12:22 PM
  5. Table design problem
    By wasim_sono in forum Database Design
    Replies: 0
    Last Post: 04-08-2009, 06:21 AM

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 - Senior Forums