Results 1 to 10 of 10
  1. #1
    Pilotwings_64 is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2010
    Posts
    22

    Many to Many Relationships among 8 tables

    I have 8 tables and each of them needs to have a many-to-many relationship with each of the 7 other tables. This would require that I make 28 junction tables and then deal with the mess that would be created on my relationships screen, which would make all the rest of what is being represented on the screen that much more difficult to understand.



    So I was thinking I could just make one junction table containing foreign keys for all of those 8 tables. Then to use any of the relationships in a form I can create a query that selects only the two foreign keys I want used in the form.

    Is this something that can work, or will it lead to problems?

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    28 of anything will lead to problems. and for that matter, EIGHT of anything.

    why are you using many to manys?? what does the data look like?

  3. #3
    Pilotwings_64 is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2010
    Posts
    22
    I'll give you an example of Four tables.

    1. People (some I know personally and some I don't)
    2. Communications (Any important exchange of information that I have with another person or with other people. In this table I track only what was said, or at least the gist of what was said, in the communication, not my thoughts about it or anything of that nature.)
    3. Arguments (This is simply a table that tracks arguments for any claims I am tracking)
    4. Journal (In this table I track files like my journal or just random notes on books, movies, lectures, communications etc.)

    So let’s say I talk to my friend Joe. Joe gives his argument for the world being flat, and I debate him. When we are done talking, I write a quick synopsis of our conversation, I record his argument the best I can in my arguments table(s).Then I write in my journal about my thoughts on the conversation.

    Now I have all of these things that need to be linked. When I'm looking at Joe in a form on my data base a month or two from now, I want to be able to see at least the dates for and titles I've given to any significant communications we have had. I also want to be able to see the same for any arguments he has put forth for any of his claims, and I would like to see the same again for any journal entries, notes, etc. I have written about him.

    If I only had four tables this would be no big deal. But I have at least 8 major ones that all need to be interconnected in the way I described above, and I have more than a few tables that link to each major table.

    For instance the tables in the Arguments set are
    Claims>Arguments>Premises>Evidence.

    I also have many-to-many relationships (and therefore junction tables between) People and Phone numbers, People and Addresses, and People and Email Addresses. I have the same three relationships substituting Companies for People.

    I have even more than what I have described here.

    So creating 28 junction tables would make everything look like a mess. I will do it if I need to, but I'm just exploring other options before I move on.

    Thanks for the response.

  4. #4
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    Pilot,

    You have the perfect setup for a million one-to-many relationships. many-to-many's are ridiculous...you don't need them.

    in that book you just wrote, all of the data relates back to the person you had a conversation with. And it doesn't matter if you have a million tables, because each new table should be connected with the previous one.

    e.g. -

    many conversations to a person,
    many arguments to a conversation,
    many thoughts to an argument,
    etc, etc, etc...

    and it goes on and on and on until you get to the end. Now there may be other ways of doing this, but this is a classes example of why databases were created. and if you have 8 tables like this, there is nothing wrong with viewing a master form that is bound to a query which has 8 INNER JOIN statements in it.

    as a matter of fact, you can do that AND update the data in one single form if you really wanted to!

  5. #5
    Pilotwings_64 is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2010
    Posts
    22
    I'm not sure I understand what you are saying; at least not completely.

    First of all, are you actually saying that I can get the same results I would with a many-to-many relationship without even using one? Can you elaborate on this? That would interest me very much, since I am using so many of them. Maybe you could give me an example (or point me to one) where one of the familiar "album collection" or "students and classes" situations I have usually seen given as prime candidates for M-M relationships can be accommodated without any.

    Secondly, I can't just center the whole DB around people. Let's say my friend Sam also made an argument for the world being flat. Let's also say that I have thousands of arguments stored in my DB, and I'm reading the one Joe made about the world being flat. Then I decide I would like to review all of the arguments about the world being flat and all of the notes (journal entries) I wrote about them, and I would like to know which of my friends subscribe to this belief. This search would center on arguments but it would need to include communications, people, and journal entries.

    Anyway, thank you for your response. If you are right about my not needing many-to-many relationships and you can convince me of that, you will have saved me a lot of time recreating this DB in the future.

  6. #6
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    It's not my job to convince you of anything.

    For a tier-like structure, take a look at one of the downloads in this thread: http://www.access-programmers.co.uk/...d.php?t=156032

    Your db structure should look very similar to that, amplified by 1000. And the example you have again given is no different than the one you first mentioned. I have never built a database that used anything other than one-to-many relationships between the tables.

    I think the brick wall you're running into is the fact that you want a 'catch-all' for your needs. That will never happen no matter what technology you use. But, the next best thing is to create your file the right way, and then use the additional functionality of the program to perform tasks that cannot be molded into the initial setup.

    for example, in your last post, you won't be able to bypass some of the leg work to get all of that done by using one-to-many's, but you can't do it with any other setup either. You will have to use lookups, queries, etc, etc...

    make sense?

  7. #7
    Pilotwings_64 is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2010
    Posts
    22
    I see that, as far as the relationships go, these tables are divided into 5 groups. I don’t see how they would relate to each other without relationships created between them.

  8. #8
    Pilotwings_64 is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2010
    Posts
    22
    I guess if I have the tables People and Communications and I want to record all of the people related to each communication and all of the communications each person was involved in I could just create two new tables PeopleCommunications and CommunicationsPeople (I know the names are bad, but I'm short on time).

    So to track which people were involved in each communication I could create a one to many relationship between Communications and CommunicationsPeople. Then I could use a combo box to pull the autonumber value from the key field of the People table and then put it into the CommunicationsPeople table.

    I could do the same with People and PeopleCommunications.

    It seems like the only functionality I would lose is the reinforce referential integrity (which would not be a problem since I would be pulling the values from the table to which I would otherwise be creating a relationship) and the cascade updates and deletes.

    Am I at least on the right track?

  9. #9
    Pilotwings_64 is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2010
    Posts
    22
    Okay, I made a huge mistake in my last post. If I connected a person to the communications table using the method I described, I would see that person when looking at that communication on a form/subform in which communications was the source of the main form, but I would not see that communication on a form/subform based on the people table.

    So if I want two tables to interact in a m-to-m way without having the relationship, I would still have to have a table between them, I would just leave out the relationship.

    Am I correct? And if I am, how would that be better than just creating the m-to-m relationship in the first place?

    Thanks again.

  10. #10
    Pilotwings_64 is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2010
    Posts
    22
    Maybe this will help make things simple.

    If I want to have a many-to-many-type interaction between the tables Communications and People, what would be a good way to do that without actually using a many to many relationship?

    If I can just see what you are talking about once, I think I will be able to reproduce it again.

    Thanks again for the help.

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

Similar Threads

  1. 8 Tables with Relationships
    By bigdogxv in forum Access
    Replies: 1
    Last Post: 12-09-2009, 09:37 PM
  2. Tables Vs Relationships
    By Bruzer in forum Database Design
    Replies: 8
    Last Post: 09-06-2009, 04:39 PM
  3. Help on Tables/relationships?
    By mistaken_myst in forum Database Design
    Replies: 3
    Last Post: 04-01-2009, 05:16 PM
  4. Relationships btw tables
    By metaDM in forum Queries
    Replies: 0
    Last Post: 03-05-2009, 12:15 PM
  5. I need help on relationships for tables.
    By justin.w in forum Access
    Replies: 0
    Last Post: 10-16-2006, 10:57 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