3 Replies Latest reply on Feb 2, 2011 2:31 PM by davidanders

    Relational Database Help



      Relational Database Help


      Hi, I'm needing to design a database that will take sales information for books and associate a royalties amount for each author. I have no real idea how to go about doing this, so I am yielding to the experts.

      One table (or separate database??) will have Title, Author, Total Units, Permissions, Royalty % and Owing. The other table will have all the info (Address etc) for each Author.

      The end result I need is a report that has the author's name, and the calculated royalty % they are owed. Another complication is that some authors have written more than one book, and some books have more than one author.

      Not sure if this helps, or if it can be done in FM.


        • 1. Re: Relational Database Help

          This can definitely be done in FileMaker, but creating such a database requires two areas of expertise: 1) You need someone who understands the business practices and what tasks the databas needs to be designed to support. 2) you need someone who understands how a database system like FileMaker should be designed to meet the needs of 1). One person can wear both hats, but more often, a database developer has to sit down with the client who can describe what they need.

          You'll need to sit down and very carefully figure out on paper what you need from the database. You've made a start here, but additional details likely need to be filled in here.

          Just focusing on what you've described, you need three tables: Authors, Titles, and Author_Titles.

          Authors---<Author_Titles>----Titles ( ---< means "one to many" )

          Authors::authorID = Author_Titles::AuthorID
          Titles::TitleID = Author_Titles::TitleID

          You'd enter information specific to each author in the authors table. information specific to each Title in the Titles table. Author_Titles serves as a "join" table so that you can multiple Authors to one title record to document multiple authors for one title and multiple titles to one author so that you can access a list of all titles for that author.

          Some data like royalt % will have to be considered carefully. If all authors of a given title always get the same royalty percentage, then this field should be defined in Titles. If different authors of the same Title can have different royalty percentages, then this data should be defined in the join table so that you can record a different percentage for each match of author to title.

          • 2. Re: Relational Database Help

            Thanks for your help! I have lots of planning thinking to do, and this is a very helpful start. 

            • 3. Re: Relational Database Help

              You might look at the White Paper for Filemaker Novices.


              There are an increasing number of videos about Filemaker on Youtube


              The Missing Manual series is good, I own several.