Results 1 to 2 of 2
  1. #1
    ketbdnetbp is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Midwest
    Posts
    254

    One to One Relationships

    Good Evening All -

    Because I am STILL undecided on a particular data model, I have been reading posts and re-researching normalization. I have examined other similiar data models, tried various scenarios, and combined those with the actual business process, and I keep coming back to a One-to-One relationship. Further, I must say, there are many different points of view on 1/1 relationships. So, just for discussion sake and because I am currently leaning that way, what are the pros and cons of the following structure:

    tblEntity
    EntityID (PK)
    txtentityName
    etc.

    tblLocation
    LocationID (PK)
    txtLocationName
    etc.


    Entities can have many certifications
    and
    Locations can have many certifications

    therefore, M/M

    tblJTEntityLocations
    EntityLocationID (PK)
    lngLocationID (FK)
    lngEntityID (FK)

    EntityLocations can have many certifications 1/M

    tblCertifications
    CertificationID (PK)
    lngEntityLocationID (FK)
    dteCompletionDate

    The certification process is comprised of three separate completion milestones (transactions) and each one has very distinct and sizeable data requirements.
    So, the detail tables that follow are the ones I think have a 1/1 relationship with the tblCertifications. I'm fairly certain, there would never be more than one record in the detail table for each CertificationID.



    tblCertificationDetailsSection1
    CertificationDetailsSection1ID (PK)
    lngCertificationID (FK)

    tblCertificationDetailsSection2
    CertificationDetailsSection2ID (PK)
    lngCertificationID (FK)

    tblCertificationDetailsSection3
    CertificationDetailsSection3ID (PK)
    lngCertificationID (FK)

    I could combine the three detail tables with the Certification table and have one very large table but, I keep thinking that doesn't really follow the business process and I'm concerned about the simplicity of design as well as ease in which data is extracted for reporting purposes. Oh, I forgot to mention, the government is involved in the certification process and they have strict form completion and reporting requirements. Hopefully all this make sense. Please share any thoughts and suggestions...

    Thanks, in advance, for your help!

  2. #2
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,

    how about a tblCertificationsDetailsTable that has the Section as a field:
    tblCertificationsDetailsTable
    cdID (PK)
    cdCertification (FK)
    cdSection (1,2 or 3)

    In this case whenever the government adds a new section you wouldn't have to add a new table.

    grNG

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

Similar Threads

  1. Help with Many to Many Relationships
    By alpinegroove in forum Database Design
    Replies: 5
    Last Post: 03-24-2011, 01:12 PM
  2. Relationships
    By bopsgtir in forum Database Design
    Replies: 1
    Last Post: 01-10-2011, 12:44 PM
  3. Relationships again
    By BarbT in forum Access
    Replies: 2
    Last Post: 11-09-2010, 04:12 AM
  4. One to many relationships
    By CoachBarker in forum Database Design
    Replies: 1
    Last Post: 09-13-2010, 09:01 PM
  5. Relationships?
    By rosh41 in forum Database Design
    Replies: 2
    Last Post: 06-23-2010, 01:26 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