Results 1 to 6 of 6

Trying to deicde whether I should create a 1-to-1 or put all data in one table

  1. #1
    brharrii's Avatar
    brharrii is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2012
    Location
    Seattle, WA
    Posts
    63

    Trying to deicde whether I should create a 1-to-1 or put all data in one table

    I am working on a database that keeps track of several Production facilities as well as the contact information for two key positions in each facility: Plant Manager and QC Manager. Each plant will have one and only one Plant Manager and each Plant manager will be assigned to one and only one plant. The same is true for QC managers.



    Initially I felt like I should have 2 different tables (facilities and managers) but am finding a little bit awkward to try to setup the data entry portion of the one-to-one relationship in a way that is intuitive to the user. Would it be more effective to do away with the Managers table all together and store all manager data (name, phone number, fax, email address) to the facility table like so:

    tblFacility
    - FacilityName
    - FacilityAddress
    - FacilityRegistrationNumber
    - FacilityPlantManagerName
    - FacilityPlantManagerPhone
    - FacilityPlantManagerFax
    - FacilityPlantManagerEmail
    - FacilityQCManagerName
    - FacilityQCManagerPhone
    - FacilityQCManagerFax
    - FacilityQCManagerEmail

    Or am I on the right track by keeping them in two seperate tables? If so, how should I establish the relationship between these two tables?

    Thanks for your thoughts,

    Bruce

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,550
    The only issue I can see with one table is if the managers change to a different facility. Instead of just changing a manager ID field, have to edit every field associated with the manager.

    If you split the table, should be one for Facilities and one for Managers (both manager types in one table, a record for each). Then either save manager IDs to two fields in Facilities or facility ID to Managers. This latter approach is a 1-to-many relationship. Each facility will be associated with 2 manager records but each manager will have only one facility.
    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
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Perhaps, using a junction table might work out better in the long run :
    tbFacilities
    FacilityID - PK

    tblManagers
    ManagerID - PK

    tblFacilityManager
    FacilityManagerID - PK
    FacilityID - FK
    ManagerID - FK
    StartDate
    EndDate

    Thanks

  4. #4
    brharrii's Avatar
    brharrii is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2012
    Location
    Seattle, WA
    Posts
    63
    Thanks for your responses. I'm considering going the junction table route because I found out that we actually do have a manager who manages two plants.

    Assuming I went the junction table route, and wanted to create a form that would allow the user to setup new plants including specifying the Plant and QC Managers, would I need to create two different subforms (One for QC manager and one for Plant manager)? Is there a better way to do this?

    Thanks

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,550
    A junction table might be over complicating.

    The two manager fields in Plants table probably workable. However, one complication could be in a search for a manager name but you are not sure which type of manager. Would this ever be a possible query - name is known but not the role?
    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.

  6. #6
    brharrii's Avatar
    brharrii is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2012
    Location
    Seattle, WA
    Posts
    63
    I don't forsee that being an issue.... I plan on adding a button that will allow the user to setup new managers and edit old ones so it will be possible for them to look up a manager if they need to.

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

Similar Threads

  1. Create Table from Form data
    By DCV0204 in forum Programming
    Replies: 8
    Last Post: 10-31-2013, 01:07 PM
  2. Replies: 3
    Last Post: 10-02-2012, 12:25 PM
  3. Replies: 1
    Last Post: 05-23-2012, 05:26 PM
  4. first create table in MS access data definition sql view
    By learning_graccess in forum Access
    Replies: 1
    Last Post: 10-14-2011, 06:23 AM
  5. Replies: 1
    Last Post: 05-21-2009, 02:52 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