Library Database : Portals for multiple Authors

[Rui Guerreiro]

Hello everyone,

Really excited with everything I have learned recently about FM. But I think I hit a brick wall.

I'm building a database for a private book library, and I got to the Author field, which turned out to be slightly more complex than I expected. Most books only have one Author, but there are quite a few with two or more Authors. And some of those are collaborators, not necessarily Authors, but I still want to mention them and their roles. Really complicated (or at least I think it is).

So, I understand that the best way is to have a single Table for Authors, where I will enter everyone, either they are main Authors, Co-Authors, Illustrators, etc. And then, I guess I should use a Portal to add Authors to each book. Do you agree? In the Authors table I would enter the name and role of each author, and then I would recall them in the Portal inside their respective book, with some sort of calculation. Makes sense?

I suppose this thread will go on for a while, so let's start with the Authors table. And then we can go through the necessary relationships and finally, hopefully, I'll get to create the Portal. With this in mind, how would you create the Authors table? So far, these are the fields I have in it:

• ID
• Name
• Role (Author, Illustrator, Preface, etc.)
• Year of Birth
• Year of Death

Ideally, for a single Author book, when recalling an Author in the Portal, they would show up as "James Joyce, 1882-1941".

When adding a book with multiple Authors, each would be recalled in the Portal and show in their respective line like so:

• "Hernest Hemingway, 1899-1961"
• "John Doe, Illustrator, 1920-2004"

I really hope I'm making sense, and that you can help me get started and along the way. It is very appreciated.

Thank you,

Rui

[comment]

For the arrangement you describe (a many-to-many relationship between Books and Authors), you will need three tables:

Books -< Roles >- Authors

The Roles table is what is known as a join table. In your example, it would have (at least) the following fields:

• BookID
• AuthorID
• Role

See a basic demo of a join table here:
http://fmforums.com/topic/50942-portal-grouping-problem/?do=findComment&comment=239210

 

 

[Rui Guerreiro]

Hello there,

That makes sense, but let me ask you this: will I be able to add more than one author in each book? And will a Portal be involved in that?

Thank you so much.