5 Replies Latest reply on Jun 21, 2013 9:54 AM by philmodjunk

    relationships question

    AdamReed

      Title

      relationships question

      Post

           I'm having trouble with the following:

           I'm working with authors, books, contracts, and payments.

           Authors to books is a many to many relationship, as is books to contracts.  Contracts to payments is a one to many relationship.

           Some of the payments are in contracts that involve more than one book, yet the payments are book specific.  So an author might have written The Iliad and The Oddyssey, get a single contract for both books, yet be paid on the delivery of each specific book.  This works fine in most cases, however I noticed that in cases of more than one author, the author ids appear once for each book in the contract (rather than the payment).  This is in a layout based on the "royalties table", and displaying records from the "book author link" table.

           So in a nutshell I'd like to relate a contract to more than one book, and relate a number of payments to that contract and specific books related to the contract.

           I hope that makes sense?

           Attached is a diagram of what I believe are the relevant tables.

      simplified_db.png

        • 1. Re: relationships question
          philmodjunk

               Aren't payments specific to each author as well as specific to each book. If Homer had a co-author, wouldn't the co author also get a payment fo the Illiad as well as homer--thus needing two payment records, one for each author?

          • 2. Re: relationships question
            AdamReed

                 Not at this stage -- there are actually two payment stages, one from the publisher to the agent, and then from the agent to the author(s).  The publisher will typically issue one payment and the agent will later divide the payment based on the share each author is to receive.

            • 3. Re: relationships question
              philmodjunk

                   It would seem that you need to link a payment to both the join table as you now have done so, but then it should also link to a record in contracts as it is specific to both a contract and a book. You'll need additional table occurrences to do that.

                   however I noticed that in cases of more than one author, the author ids appear once for each book in the contract (rather than the payment).  This is in a layout based on the "royalties table", and displaying records from the "book author link" table.

                   I'm not totally clear on what you are describing there. Presumably, you have a layout where each record is a royalty payment and you have a portal to Book Author Link. Do you see one row in that portal for every author for the book listed on the contract to which you linked that royalty record?

                   "Payment title" seems an odd field name here and suggests that you are linking records by book title instead of a serial number. That could be significant problem if you have more than one contract for the same book title.

                    

              • 4. Re: relationships question
                AdamReed

                     Thanks very much.

                     Payment title is an odd field name.  It was an attempt to indicate specific titles, but I had been using "book id" consistently, and agree I should use that.  I think it's an indication of the design problems in this area that I moved away from that, and will move back to "book id" now that I'm working through this.

                     After more testing, the situation seems to be that in the portal displaying records from "book author link" table, a portal row for each author appears once for each book with which the author is associated for that contract (rather than payment), so if there are two books in the contract, the author(s) appear twice.

                     This seems correct to me in terms of associating books and authors with the contract, however I've been unsucessful in limiting the association of a particular payment to a single book.

                     In a nutshell, I would like to have payments associated with a contract, yet limited to a particular book, and then display the author(s) associated with the contract, but limited to the particular book.  I've attached a simpler model -- is this along the lines of what you're suggesting?

                     Adam

                • 5. Re: relationships question
                  philmodjunk

                       Sometimes there's no simple answer.

                       What I had in mind was recording a book ID in your payment record and linking Payments to a new occurrence of Book.

                       But that doesn't mean that the approach you have set up here is incorrect by any means.