Results 1 to 11 of 11
  1. #1
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147

    Multiple Relationships based on two fields

    Hi
    I think I have seen it done somewhere, but what it is called and how to do it access escapes me.



    So I have a tblSources which has SourceID, SourceType, Page, and Issue with other fields. SourceType should have a value that determines what Issue is the FK of what (Book, Journal, Websitej. I could put in three fields, (JournalFK, BookFK, SiteFK) but with any record two should be null (That doesn’t feel right).

    I know I got something messed up.

    Thanks in adavance
    Western Neil

  2. #2
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Maybe I miss your point, but it seems your relationships are backwards. tlbSource should be the one side, containing NO FKs, while tblJournal, tblBook and tblSite should have tblSource_Fk on the many side.

  3. #3
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    Hmm. Looking farther up the chain, tblFacts though tblFactSources is linked to tblSources. Are you saying that really tblSources are the three tables?

    A Fact maybe stated in many Sources (Book, Journal, Sites) and a Source may state many Facts. So is this two join tables back to back?

    Fact. FactSource. FactSourceIssue. Book

    Confused in the West
    Neil

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Possible data entities: Sources, Authors, Facts. A very simple normalized structure could be like:

    tblAuthors
    AuthorID
    AuthorLast
    AuthorFirst
    AuthorBirthDate

    tblSources
    SourceID
    SourceType (Journal, Book, Site)
    SourceDesc (journal name, book name, site URL)

    tblFacts
    FactID
    SourceID_FK
    AuthorID_FK
    PageNum
    Fact

    Might want to include IssueOrEdition in either tblSources or tblFacts.
    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.

  5. #5
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    What relationship does a Fact and Author have? Not trying to be difficult, but missing the point.

    Restating

    tblFacts. Something happened
    FactID

    tblFactWritten. It has been written about in
    FactWrittenID
    FactFK
    WrittenFK

    tblWritten. In Books, Journals and on web sites
    WrittenID
    SourceFK

    tplSources. Where this would Book Data, Journal Information, or Web Stite Listing
    SourceId

    My labels are wrong and the relationships still not popping out at me. Some thing is wrong with Written or is it three tables?

    Still Confused
    Neil

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    The author you are citing that wrote the article in journal or wrote the book. WebSite might also have an author name. You could instead put AuthorID_FK in tblSources.

    If you don't care about documenting author details then that is not an entity in your relationships.

    Seems to me SourceFK could go in tblFactWritten and call the table tblFactSources. 3 tables are minimal for a many-to-many relationship.
    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.

  7. #7
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    I agree that three tables are needed for many-to-many relationship, so that covers Fact-WrittenIn, but how to link in Book, Journal, Sites? The fields required for the three are different. (ISBN, Vol/Issue, URL)

    Missing A Table Neil

  8. #8
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    So would that be seven tables then Fact-WrittenInBook(3 tables), Fact-WrittenInJournal (+2), Fact-WrittenOnSite (+2).

    I think I got it
    Western Neil

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    It is a balancing act between normalization and ease of data entry/output - "normalize until it hurts, denormalize until works". Even though some fields would not be used for every record, consider a single table for sources.
    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.

  10. #10
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    So you are saying to go back to the start? Fact, FactSource with type, and the three source tables (Books, Journals, Sites).

    The Source tables are different enough that combining them will produce to many nulls. FactSource with Type means two nulls for each source used, but being Integers is a fairly small amount space.

    I still kind like the seven table solution, but does feel awkward.

    Western Neil

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Do what works.
    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.

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

Similar Threads

  1. Replies: 3
    Last Post: 02-20-2017, 08:27 AM
  2. Replies: 4
    Last Post: 02-14-2017, 08:10 AM
  3. Replies: 3
    Last Post: 07-26-2016, 02:12 PM
  4. Replies: 0
    Last Post: 06-15-2012, 05:58 AM
  5. Replies: 12
    Last Post: 05-07-2012, 12:41 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