Trouble with Many-to-Many relationships...

[Derek Hiemforth]

... including the fact that I'm not totally certain that what I'm looking to accomplish really *is* a MtM relationship. :

(Quick background... I used FMP quite a bit several years ago for various personal projects, but never had a need for a relational database. I recently bought FMP8 -- my last version was 4.1 -- and I'm working on my first relational DB. I've got "FileMaker Pro 8: The Missing Manual" as my documentation crutch, but I'm hoping some human interaction will help me get my head wrapped around this.)

Here's what I'm trying to accomplish...

I currently maintain a web page that lists info about books related to a particular game. (It's at http://rhinobunny.com/derek/hero/herogameslist.html if you want to see it, in all its current ugliness, to get an idea what I'm looking at.) I want to give it a big face-lift, adding more info (artists, years of publication, etc.), and making several different versions available, sorted by different criteria. Currently, it just groups by publisher and then by stock number. I want to add versions sorted by title, author, year, etc.

I'm gathering all this data into a database, so I can more easily manipulate it and export it back out in various configurations. Where I'm really getting stumped is in figuring out how to handle authors (and artists, but I'll discuss authors for the sake of this post). Let me offer a use-case example...

Let's say I've got three books:

• Awesome Adages by Andy Andrews
  
• Amazingly Brilliant Brainstorms by Andy Andrews and Bob Bannister
  
• Clever Anagrams, Boggles, and Clues by Andy Andrews, Bob Bannister, and Cathy Carlson
  

I want to be able to generate a list by title (as above) which is easy, but also a list by each individual author, listing any and all titles they worked on (which means the same title appearing more than once). For example...

• Andy Andrews: Awesome Adages
  
• Andy Andrews: Amazingly Brilliant Brainstorms
  
• Andy Andrews: Clever Anagrams, Boggles, and Clues
  
• Bob Bannister: Amazingly Brilliant Brainstorms
  
• Bob Bannister: Clever Anagrams, Boggles, and Clues
  
• Cathy Carlson: Clever Anagrams, Boggles, and Clues

Since one book can have many authors, and since each author can work on many books, I think I need to set up a many-to-many relationship between books and authors. But (A) I'm not positive that's right, and (: I'm having trouble grasping how it's going to work. I know that if it is a MtM relationship I need, then I need to set up a join table between the Books table and the Authors table, but I'm having trouble splitting the real-world relationship between books and authors into two one-to-many relationships for using with a join table.

Does anyone have any thoughts, or am I totally barking up the wrong tree? :

[Søren Dyhr]

You're not barking up the wrong tree, but filemaker has a feature to let you make the jointable a returndelimited list in a field in one of the tables. In fact is the scenario you descripe exactly what this old template shows:

ftp://ftp.fmfiles.com/download/devhaven/tipstricks/relate/ManytoMany.zip

...drop both the old files ontop of you FM8 app icon to convert it into a more contemporary version.

--sd

[comment]

I don't think this is such a good idea, given the required type of reporting:

a list by each individual author, listing any and all titles they worked on (which means the same title appearing more than once)

IMHO, this kind of report can be only produced from a join table.

[Derek Hiemforth]

filemaker has a feature to let you make the jointable a returndelimited list in a field in one of the tables. In fact is the scenario you descripe exactly what this old template shows

Excellent! This provided exactly what I needed. Thanks! :thumbup:

[Søren Dyhr]

Michael you're right if the task is reporting, but where is it mentioned in the description? Is it the word "Export" you're clinging to? Listing as such could in my book be portals as well!

--sd

[comment]

Poster is happy, so you must be right.

I still think a join table is more flexible, for reporting as well as for export. How would you produce an HTML document like this:

Author A

• Book One
  
• Book Two
  

Author B

• Book One
  
• Book Three
  

[Søren Dyhr]

Yes indeed, and I sould be mistaken if it isn't the case here as well, although it's disguised:

http://www.fmforums.com/forum/showtopic.php?tid/172431/post/187903/#187903/

...but I didn't dare since he carries higher rankings than mine:

http://www2.pfeiffer.edu/~lridener/courses/COLLINR2.HTML

--sd