Page 1 of 1

[Solved] Many to many to many relationships

PostPosted: Mon Apr 15, 2019 9:11 pm
by thetallguyuk
Hi,

I'm just learning Open Office Base and I've just figured out how to do junction tables.

But I'm struggling as the database I want to build is for books that will have multiple writers, and the writers can also be the artist.
So I've a content table
a content_personnel junction table
a personnel table
a personnel_role junction table to describe the many roles a unique person many have.
a role table to show all the roles a person may have, writer, artist, etc

But I'm struggling to create a way of designing this on a form, I've figured out how to do a subform to add the various people.

But I'm struggling to see how I can then show their role (which can be many) for that person on the book.

so many books, with many creators and many roles.

Do I need something like creating a primary key to make the person as writer unique in the junction table?

Re: Many to many to many relationships

PostPosted: Mon Apr 15, 2019 9:54 pm
by Villeroy

Re: Many to many to many relationships

PostPosted: Tue Apr 16, 2019 2:02 pm
by keme
From your description, I believe you need one triple-junction table:
content_personnel_role, with three foreign keys, all of which comprise the primary key for that table.

This allows you to register different roles for a person on each content item.

Re: Many to many to many relationships

PostPosted: Tue Apr 16, 2019 9:52 pm
by thetallguyuk
Thanks so much, hadnt seen a triple junction box yet, so something else I'll need to figure out how to build (all good fun!)