I am a bit confused, because I find it difficult to understand the real situation being tracked - esp. the difference between a book and a title.
In general, a join table provides two functionalities that multi-key does not:
(a) the ability to assign attributes to a specific join;
(b) the ability to produce any type of report that may be required.
There should be no duplication of information in a join table (on the contrary - that's the entire purpose of it). The join table should NOT hold the name (or any other attribute) of the joinees - only their ID's.
Sorry for not being clear.
Firstly, the difference between a title and a book. A title is a particular work that is owned by one or more copyright holders. A book might have several titles. Think of an anthology for instance that has lots of stories, but each story is owned separately and needs to be treated separately for payment purposes.
And that's what needs to be tracked. Payments are based on books sold, but are divided according to who owns the content in the book. Sometimes a title can be jointly written, so the payment needs to be shared. And if a book has more than one title, the payment also needs to be divided. Say a book consists of four titles, and one of them has two Copyright Holder (CH). The CH for the first three titles would each get 25% of the payment. The two CH for the fourth title would each get 12.5 %. There might also be an editor who gets a separate payment on top. The contract stipulates the level of payment (eg 10% of sales).
When I talked about duplication, I was talking about the codes themselves. For every join I need to create a record in the join file that has the relevant codes and the proportion of royalty. But if instead of a join field, I create a record in the Copyright Holder file for every instance that a Copyright Holder deserves payment with the proportion, then the name of the CH holder is the only extra information I need (and technically this could be in the address file anyway). It seems a lot less cumbersome.
What I was asking was whether there was something wrong-headed about doing this that would come back to bite me.
I should also have said that I'm using FM Pro 10.
It looks like you are on the right track
Id think about it like this
Apparently a book level pays out to an editor and its titles these records are basically equal and you could keep both record types in the same table but dont have to.
The titles then pay out to copyright holders at a contracted rate. so this CH and Contract rate can be in the same record the CH address etc information stays in its own table for repeated use.
You join Titles(editiors) to Books and join CH/Contracts to Titles. You can even keep the editor contract in this table
You then split the payment between titles and then split the title between its contracts.
You look like you are on the right track
For every join I need to create a record in the join file that has the relevant codes and the proportion of royalty. But if instead of a join field, I create a record in the Copyright Holder file for every instance that a Copyright Holder deserves payment with the proportion, then the name of the CH holder is the only extra information I need (and technically this could be in the address file anyway).
It seems to me that if you call your Address table "Copyright Holders" and your Copyright Holder table "a join table" you would be in the same position, give or take a field or two.
Thanks to you both for your help. My thinking was going round in circles, but of course, it is a join file and it will do the job.