AnsweredAssumed Answered

Complex Relationships

Question asked by eibcga on Feb 6, 2011
Latest reply on Feb 8, 2011 by eibcga


Complex Relationships


Let's say I'd like to track that a member could know many software tools, and a software tool may be used by many members.  Also, a member could have recommended many books, and a book could be recommend by many members.  Finally, what book a member recommends depends on what software tools the member knows.  Software could have many books recommended about it, and a book could deal with more than one software tool (I guess this is similar to the common agents-brands-products example, where an agent can represent many brands and many products, and each brand and each product represented by many agents, but each product depends on what brand, etc.).

I have three parent tables (grey) and four child tables (green) created in the attached relationships graph.  Essentially, there's two many-to-many relationships from the same table (MEMBER) of which these two relationships, based on business rules, are dependent on each other (Book and Software are not independent), thus creating a third many-to-many relationship between SOFTWARE and BOOK.

But, when I create the relationships in FileMaker from the ERD I had drawn out on paper, I noticed it's a "circular" or ambiguous relationship as far as FileMaker was concerned.  FileMaker resolves the ambiguity by having two aliases of the SOFTWARE_BOOK source table on the relationships graph (two Table Occurrences) to get SOFTWARE_BOOK and SOFTWARE_BOOK 2.

Am I correct in saying that these relationships will still work despite the fact there are two occurrences of the SOFTWARE_BOOK source table?  I guess the relationships graph does not have to look exactly like the ERD I had on paper, but the end result is the same?  Are these table occurrences also in what they would say in the data normalization world to be in fifth normal form (5NF)?

Thank you in advance!