Data Relationships

Models in a database often have relationships, like objects in our code or even things in real life. There are a few types of relationships. We'll talk about three of the most common:

One-to-one

A person has one social security number, a social security number belongs to one person. Here's an example of how that could be represented in prisma.

model Person {
  id             String          @unique @default(cuid())
  name           String
  socialSecurity SocialSecurity?
}

model SocialSecurity {
  id       String  @unique @default(cuid())
  number   String
  person   Person @relation(fields: [personId], references: [id], onDelete: Cascade, onUpdate: Cascade)
  personId String @unique
}

You'll notice the @relation attribute on the SocialSecurity model. This configures the relationship between the two models with referential actions.

The fields argument specifies which fields in the model are used to refer to the other model. The references argument specifies which fields in the other model are used to refer to this model. The personId field is used to refer to the Person model and is called a foreign key.

The onDelete and onUpdate arguments specify what happens when the referenced model is deleted or updated. In this case, we're saying that if a Person is deleted or updated, the SocialSecurity should be deleted or updated as well.

One-to-many

A person has many phone numbers, a phone number belongs to one person. For example:

model Person {
  id           String        @unique @default(cuid())
  name         String
  phoneNumbers PhoneNumber[]
}

model PhoneNumber {
  id       String @unique @default(cuid())
  number   String
  person   Person @relation(fields: [personId], references: [id], onDelete: Cascade, onUpdate: Cascade)
  personId String
}

In this case, the biggest difference is that the personId field is not declared as @unique. This is because a person can have many phone numbers, so the same personId can appear multiple times in the PhoneNumber model. And on the Person model, the phoneNumbers field is declared as an array of PhoneNumber models, because a person can have many phone numbers.

Many-to-many

A blog post has many tags, a tag has many blog posts. For example:

model Post {
  id    String @id @default(cuid())
  title String
  tags  Tag[]
}

model Tag {
  id    String @id @default(cuid())
  name  String
  posts Post[]
}

You may have noticed something interesting about this relationship configuration... There's no @relationship configuration! There's no postId or tagId on these models to relate them to each other. This is due to a limitation in relational models. You cannot represent a many-to-many relationship between two database tables.

Instead, you have a third table that stores the relationship. Luckily for us, Prisma makes this table for us automatically!

Check out what prisma does with these models

-- CreateTable
CREATE TABLE "Post" (
    "id" TEXT NOT NULL PRIMARY KEY,
    "title" TEXT NOT NULL
);

-- CreateTable
CREATE TABLE "Tag" (
    "id" TEXT NOT NULL PRIMARY KEY,
    "name" TEXT NOT NULL
);

-- CreateTable
CREATE TABLE "_PostToTag" (
    "A" TEXT NOT NULL,
    "B" TEXT NOT NULL,
    CONSTRAINT "_PostToTag_A_fkey" FOREIGN KEY ("A") REFERENCES "Post" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT "_PostToTag_B_fkey" FOREIGN KEY ("B") REFERENCES "Tag" ("id") ON DELETE CASCADE ON UPDATE CASCADE
);

-- CreateIndex
CREATE UNIQUE INDEX "_PostToTag_AB_unique" ON "_PostToTag"("A", "B");

-- CreateIndex
CREATE INDEX "_PostToTag_B_index" ON "_PostToTag"("B");

It even handles cascading updates/deletes and creates indexes for us 💪

We're going to get to many-to-many relationships in the authentication workshop, here's a visualization of what our user/role/permission relationships will look like:

visualization of the user, role, permission relationships which are all many-to-many

Visualizing relationships

Sometimes it can be helpful to represent these relationships visually. Especially for very complex relationships. This is often done using an entity relationship diagram or ERD. Learn more about ERDs.

You can even use the prisma-erd-generator to generate an ERD from your prisma schema. For example:

📜 Prisma Schema Reference