[Solved] Many to many to many relationships

Creating tables and queries
Post Reply
thetallguyuk
Posts: 2
Joined: Mon Apr 15, 2019 9:00 pm

[Solved] Many to many to many relationships

Post 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?
Last edited by Hagar Delest on Wed Apr 17, 2019 7:52 am, edited 1 time in total.
Reason: tagged solved
OpenOffice 4.0 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Many to many to many relationships

Post by Villeroy »

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Many to many to many relationships

Post 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.
thetallguyuk
Posts: 2
Joined: Mon Apr 15, 2019 9:00 pm

Re: Many to many to many relationships

Post 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!)
OpenOffice 4.0 on Windows 10
Post Reply