I have a database that has a series of many to many relationships and I'm now getting confused about the best way to structure them.
The central dilemma involves these files.
1. Books file, which lists books which consist of one or more titles.
2. Titles file. Titles can be in one or more books.
3. Contracts file. A contract only covers one title, but it might relate to more than one book. A title only has one contract irrespective of the number of books it is in.
4. Copyright holders file. Copyright holders might have one or more titles, one or more books and one or more contracts. And the reverse is true too: ie books, titles and contracts all might have one or more copyright holders.
Books and titles are currently joined by a multi-key field, which is not ideal, but works OK.
Contracts and Titles are a simple link.
Books and Contracts are joined through a join file and works fine.
For Copyright Holders and Contracts, I started by having a separate entry for every book. Thus a copyright holder with more than three books, would have three entries. This also connects to Books and Title files on a one to many relationship. It involves a little duplication, but not a lot, since the file only has very basic information -- a name and some ID codes. Personal details are held in a separate Address file.
Something about this feels like cheating, but if I were to have a separate join file, there would duplication, since it means a whole new set of records each with at least two codes. I'm just not confident though that there is some very obvious reason why I need to have a join file, rather than duplicate entries.
Can anyone help?