5 Replies Latest reply on Jun 6, 2010 4:33 PM by vmtc

    Join files

    vmtc

      Title

      Join files

      Post

      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?













        • 1. Re: Join files
          comment_1

          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.



          • 2. Re: Join files
            vmtc

            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.




            • 3. Re: Join files
              aammondd

              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

               

               

              • 4. Re: Join files
                comment_1

                 


                Pumpkin wrote:

                 

                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.

                 


                • 5. Re: Join files
                  vmtc

                  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.