My application using Prisma has users who can create many projects. These projects are akin to text documents, but with some additional attributes that assist with organization.
These projects will always be "owned" by the user who originally created them, and that user has control over administering access, whether that be view, comment or even edit, to other users. Users who have access to another user's project(s) are considered "collaborators" in the application.
In the current application architecture, a user can create and own many collaborators, and the creating user has full control over which of their projects a collaborator can access and to what degree--view, comment, edit.
A user in the application carries the regular user-related metadata, like name and email. A project in the application carries project-related data, like the name of the project and other attributes.
I created a table that stores a user's collaborators (users) in order to display a list of those collaborators on a page where a user can perform CRUD actions for collaborators.
In order to show a user the projects they're associated with on the home page, I created another table that references a project and its related user and adds a role, which defines whether that user is the owner or a collaborator.
This allows me to display the projects that a user has access to on the home page of the application, whether the user created the project or is merely a collaborator.
So when a user logs in and goes to their home page, what they see is a full list of projects where they're either the owner or just a collaborator.
For projects in the list where the user is the owner I need to display a list of avatars that represent that project's collaborators.
Why should I do one of
model User {
id String @id @default(cuid())
name String?
email String? @unique
emailVerified DateTime?
image String?
accounts Account[]
sessions Session[]
collaborator UserCollaborator[] @relation("Collaborator")
userCollaborators UserCollaborator[] @relation("UserCollaborators")
collabInviteReceived UserCollaboratorInvite[] @relation("InviteReceived")
collabInviteSent UserCollaboratorInvite[] @relation("InviteSent")
userProjects UserProject[]
}
model Project {
id String @id @default(cuid())
name String?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
things Thing[]
userProjects UserProject[]
}
model UserProject {
projectId String
roleId Int
userId String
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
project Project @relation(fields: [projectId], references: [id], onDelete: Cascade)
role Role @relation(fields: [roleId], references: [id])
user User @relation(fields: [userId], references: [id])
userCollaboratorProjects UserCollaboratorProject[]
@@id([userId, projectId])
}
model UserCollaborator {
ownerId String
collaboratorId String
roleId Int
status CollaboratorStatusEnum
// this is the user who is the collaborator
collaborator User @relation("Collaborator", fields: [collaboratorId], references: [id])
// this is the user who created the collaborator
owner User @relation("UserCollaborators", fields: [ownerId], references: [id])
role Role @relation(fields: [roleId], references: [id])
userCollaboratorProjects UserCollaboratorProject[]
@@id([ownerId, collaboratorId])
}
model UserCollaboratorProject {
userProjectProjectId String
userProjectUserId String
userCollaboratorOwnerId String
userCollaboratorCollaboratorId String
userCollaborator UserCollaborator @relation(fields: [userCollaboratorOwnerId, userCollaboratorCollaboratorId], references: [ownerId, collaboratorId], onDelete: Cascade)
userProject UserProject @relation(fields: [userProjectUserId, userProjectProjectId], references: [userId, projectId], onDelete: Cascade)
@@id([userProjectUserId, userProjectProjectId, userCollaboratorOwnerId, userCollaboratorCollaboratorId])
}
One issue in your design is making the project owner distinct from a project collaborator. These overlap. For example, when you want to list all projects a user is participating in.
You can solve this by defining a new entity which I'll call "Project Participator". This is everyone participating in the project: owners, admins, viewers, editors, etc. And that is what you want to list.
Another issue is making collaborators "owned" by the user which owns the project. This is data duplication. It invites the possibility that the collaborator's owner might not be the project's owner.
Collaborators should instead be "owned" by the project. From there they can find the owner of the project. This eliminates the data duplication, there's no possibility of the collaborator owner being different from the project owner, and it leaves open the possibility of changing the definition of who "owns" the collaborator.
Business rules change, even if you've been promised they have not. The schema should strive to hard code only fundamental relationships. Business rules should be done in data or software. Even if the rules don't change, this makes your design more flexible for additional features.
I'm going to propose these entities...
User
id
name
email
...
has many ProjectParticipants
has many Projects through ProjectParticipants
Project
name
...
has many ProjectParticipants
has many Users through ProjectParticipants
ProjectRoles
id
name
ProjectParticipant
belongs to a User
belongs to a Project
has many ProjectRoles
Every User involved with a Project is a ProjectParticipant.
Every ProjectParticipant has one or more ProjectRoles. These would be view, edit, comment, and owner.
You can list all of a User's Projects by joining with ProjectParticipants.
When a User creates a Project you'd make them a ProjectParticipant with the owner role.
You can define a "collaborator" as a ProjectParticipant who does not have the owner role.
What the roles do is not defined in the schema, it is defined in software. In your case you'd only allow ProjectParticipants with the ProjectRole of "owner" in that Project to make changes to other ProjectParticipants. You can change this later to allow, for example, "admin" to make changes to ProjectParticipants.
ProjectRoles is simply a list of all possible roles. It's going to start as just an id and a words. It could be an enum, but it's better done as a join table. This lets you rename the roles without having to change everyone's data. It makes some queries easier to have a join table, like enumerating every role. It allows roles to become more complicated later by adding rows.