12 Replies Latest reply on Apr 18, 2011 11:12 AM by AdamReed

    complicated relationship

    AdamReed

      Title

      complicated relationship

      Post

      Hello,

      I'm trying to assign authors, agents, and editors to books and contracts.  Authors and agents are assigned to books, editors are assigned to contracts, and contracts are only created after a book is created.

      I have three relevant tables (and linking tables because these are many-to-many relationships):

      - "person" "book" and "contract"

      - there are also second instances of the book and contract tables.

      Each of these tables contains a unique id field (person id, book id, and contract id), and then descriptive fields such as name, title, or territory, respectively.

      I also have three linking tables:

      - "book author" "book agent" and "book editor"

      These linking tables contain the unique ids of the relevant tables, so "book author" contains "person:person id" and "book:book id", whereas "book editor" contains "person:person id" and "contract:contract id".

      I'm using value lists to handle the different person types, based on a "type of person" field, then I'm displaying person id, and person name (that seems to be working correctly).

      The authors are fine -- I can create an author, a book, and assign the author to a book.  The editors, however, aren't coming through.  I am able to create them on my layouts, but the linking table is not being populated.

      (There is a relationship between book:book id and contract:contract id, as well.)

      That is a general summary.  Any help would be greatly appreciated.

      I've attached a screenshot of the tables and relationships case that's helpful, of course just let me know if more detailed information is needed.

      Thanks,

      Adam

      db_shot.png

        • 1. Re: complicated relationship
          philmodjunk

          "The authors are fine -- I can create an author, a book, and assign the author to a book.  The editors, however, aren't coming through.  I am able to create them on my layouts, but the linking table is not being populated."

          Exactly how are you assigning an editor to a contract?

          What layout and on what table occurrence is the layout based? If you are using a portal, on what table occurrence is it based?

          • 2. Re: complicated relationship
            AdamReed

            I've tried a couple of things (and am of course open to suggestions), but currently am trying to assign the editor in layout based on the book table, but through a portal that displays records from the contract table.

            • 3. Re: complicated relationship
              philmodjunk

              I'm assuming that you have color coded your relationship graph so that table occurrences with the same data source table are the same color. That's how I do it anyway...

              That explains the problem. The relationships you have defined won't work for that. It's a case of "can't get there from here" using a layout based on the Book Table occurrence (Upper green box in your graph).

              Make a new table occurrence of Book and link it to Book Editor Link by Book ID. Use a layout based on this table occurrence and it should work just like you link authors to books.

              • 4. Re: complicated relationship
                AdamReed

                Yes, the color codes are the table occurrences with the same data source table.

                I thought that might be the case, but do you have thoughts about how would I assign editors to contracts (rather than books)?  (A book may have many editors, but that is a relationship defined by the contract.)

                • 5. Re: complicated relationship
                  philmodjunk

                  That gives you this relationship:

                  Contracts----<Contract_Editor>-----Editors

                  A portal to Contract_Editor on a Contracts layout can be used to list and assign Editors. Put a portal to Contract_Editor on an editor layout and it will list all their contracts. In each case, you can add fields from the third table to the portal row to show additional information. Thus a portal on the Contracts layout could include the name field from the Editor field.

                  • 6. Re: complicated relationship
                    AdamReed

                    Whoa, I think I may have been really overcomplicating things...

                    I've just deleted the linking table, created a field "contract:editor id" and linked "person:person id" to "contract:editor id" and it seems to have worked.  Does that sound right, or is likely to create problems I'm not foreseeing?

                    • 7. Re: complicated relationship
                      philmodjunk

                      That allows for only one contract and one contract editor for each author. Couldn't an author have a different editor for every book they write?

                      • 8. Re: complicated relationship
                        AdamReed

                        Oh, yes, definitely.  In fact they can have 10 different editors for the same book.  These are essentially all many-to-many relationships except for editor and contract.

                        • 9. Re: complicated relationship
                          philmodjunk

                          And how do you show that if:

                          "I've just deleted the linking table, created a field "contract:editor id" and linked "person:person id" to "contract:editor id"

                          • 10. Re: complicated relationship
                            AdamReed

                            I'm not sure.  Just be clear, authors, agents and editors all have unique person ids.  I thought since the editor to contract relationship was a one-to-many relationship this might work.  A book can have many contracts, but a contract can only have one editor.  I assumed a single editor id could be related to many contract ids.  Is that not right?

                            Sorry for my inexperience...

                            Ideally I would like to be able to assign the editor on a layout based on the contract table.  I'll keep working at this, too.  I appreciate your help.

                            • 11. Re: complicated relationship
                              philmodjunk

                              It's more a case my unfamiliarity with the book publishing biz...

                              I figured multiple editors would be listed on the same contract for a given book---making it a many to many relationship.

                              Since it's one editor to a contract record, you can just record the editor's ID in the contract record.

                              • 12. Re: complicated relationship
                                AdamReed

                                Thanks very much for your help, that's great news!