1

I also asked this question on Prisma forum.

** EDIT **: The forum has since been locked as read-only just 1 day after my question. It's sure getting scary because there is no official announcement of whether they'll stop developing Prisma 1 after implementing promised features or not. Surely they could have said something. And TBH, the unstable Prisma site does add to my shaking confidence in Prisma despite the number of stars in their repo.

I'm new to Prisma. So I have a 3-way relationship between User, Event and Role I would like to define. For each association of a User and an Event, there exists a Role for that association. If I were to design a database table for another ORM, I would have created a event_user table with user_id, event_id and role_id as columns to relate them.

Practically, a row of these 3 columns must be unique. Obviously, it would be good if Prisma can do the safeguarding of these constraints, but the obvious solution I see might not even come to Prisma 1.

My current design consists of the following:

type User {
  // ...
  eventUsers: [EventUser!]!
}

type Event {
  // ...
  eventUsers: [EventUser!]!
}

type EventUser {
  role: Role!
  event: Event!
  user: User!
}

This design will make render all xxWhereUniquexx unusable, which is definitely a hassle to maintain relationships. upserts will certainly be unusable for maintaining the relationships.

How would one create a relationship like this in Prisma?

For some context, each user would have a list of global roles as well, so there would already be an association between User and Role. The question concerns the "local roles" for each event.

Daniel Cheung
  • 4,779
  • 1
  • 30
  • 63

1 Answers1

1

If each user already have a value for their respective roles, there would be no need for a third table (unless there is more information you'd like to be stored in your modal layer in which case the role type should be in the Role table).

Relationships are set with the @relation directive. You can apply it to either one of two tables in a relation but for clarity I'm apply them to the Event table. The example assumes users can be part of several events.

Tables:

enum ROLE_TYPE {
 TYPE_ONE 
 TYPE_TWO
}

User {
 user_id: ID! @id
 events: [Event!]!

}

Event {
 event_id: ID! @id
 users: [User!]! @relation(link: INLINE)
}

Role {
 role_id: ID! @id
 type: ROLE_TYPE
 event: Event! @relation(link: INLINE)
 user: User! @relation(link: INLINE)
}

You can read more about relations on the Prisma site

user1738546
  • 619
  • 6
  • 15
  • Ok, so I see the only down side for this setup is that I needed to manage the `Role`s manually, otherwise, a User to Event relationship might not have a role. Honestly, I was waiting for https://github.com/prisma/prisma/issues/3060. But it seems the development of the Prisma repo has been slowed. – Daniel Cheung Sep 29 '19 at 14:29
  • To be honest, I'm personally currently thinking of something like this. – Daniel Cheung Sep 29 '19 at 14:34
  • Have you tried it? If this answered your question please consider upvoting. – user1738546 Sep 29 '19 at 15:16
  • I'm sorry, I think I'm still exploring more solutions under the current constraints. I'm not sure when Prisma 2 will be released or whether the issues impeding better implementations will be resolved in Prisma 1. But I digress. – Daniel Cheung Sep 29 '19 at 15:46
  • I'm looking if there is a solution where the constraint of a User and Event relationship must have a Role. And that a User can only uniquely connect with 1 Event with a specific Role. – Daniel Cheung Sep 29 '19 at 15:48
  • I implemented just that above. Here, a **User** can belong to several **Event**s ( you can make this 1:1 if you wish. However because **Role** has a an id of type ID (with either the directives ``@id`` or ``@unique`` works) and also requires non-null values for the foreign keys of **Event** and **User** no **User** can have the same combination of an **Event** and a **Role** – user1738546 Sep 29 '19 at 16:03
  • Yes, but with mismanagement, there might be a User Event link without Role. – Daniel Cheung Sep 29 '19 at 18:32
  • I don't see how since all three objects require (not null) a key from the other. – user1738546 Sep 30 '19 at 18:52
  • I think the original design in OP's question is superior to this. Kind of a mess either way, but completely managing a role's table fully independently seems pretty error prone. Still +1 since it is an interesting approach and could work well if the amount of roles (e.g. few people have an "event role") is relatively small. – Firo Oct 01 '19 at 01:42