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?
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.
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.
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?
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.