# Complete Prisma schema-design & relations guide: design 1:1, 1:N, N:N, referential actions, relationMode, composite keys, and name mapping type-safely > An implementation guide to solidifying Prisma (v7) schema and relation design at production quality. Faithful to the official docs, with real code it explains 1:1/1:N/N:N (implicit and explicit join tables), the onDelete/onUpdate referential actions and their defaults, relationMode (foreignKeys/prisma), the composite constraints @@id/@@unique/@@index, @default functions, native types, self-relations, and mapping to an existing DB with @map/@@map. - Published: 2026-06-26 - Author: 友田 陽大 - Tags: Prisma, TypeScript, PostgreSQL, データモデリング, 型安全 - URL: https://tomodahinata.com/en/blog/prisma-schema-data-modeling-relations-design-guide - Category: Prisma ORM - Pillar guide: https://tomodahinata.com/en/blog/prisma-orm-production-guide-type-safe-database-v7-driver-adapters ## Key points - The schema is the single source of truth. A relation is always one pair of 2 fields (the @relation foreign-key side + the back-reference side). Express 1:1 by making the FK side @unique. - Choose N:N by 'whether the join table needs attributes.' If not, implicit (Prisma-managed); if so, an explicit join model + @@id([a,b]). - The defaults of referential actions are a trap. onDelete of an optional relation is SetNull, of a required relation Restrict. Always make the intent explicit. - relationMode=prisma is for FK-unsupported DBs (PlanetScale, etc.). In this case indexes aren't auto-generated, so stick @@index yourself. - Retrofit onto an existing DB by separating code name and physical name with @map/@@map. Take in the current state with db pull and tidy the naming on the type side. --- Database design decides an app's lifespan. Get the shape of the tables, how relations are drawn, and where constraints are placed wrong at the start, and a later fix becomes the highest-difficulty work of "fixing it without breaking data in a migration." That's exactly why **designing the schema first, correctly, in a form where the intent appears in the types** is worth it. This article is an implementation guide to solidifying **Prisma ORM (v7) schema and relation design** at production quality. `schema.prisma` is not a mere config file but **the source of types, the client, and migrations all (the single source of truth).** Designing this carefully decides the type safety of all subsequent code. The overall production operation of Prisma is summarized in the [Prisma ORM production-operations guide (v7)](/blog/prisma-orm-production-guide-type-safe-database-v7-driver-adapters). This article is positioned as the deep dive into "data modeling" within it. > **Rules for this article**: schema syntax and attributes are based on the **Prisma official documentation (as of June 2026, the v7 family).** The material is PostgreSQL. Because support and default native types differ by provider (MySQL/SQLite/SQL Server/MongoDB/CockroachDB), always confirm the latest specs in the [official documentation](https://www.prisma.io/docs/orm/prisma-schema/data-model/models) before production rollout. --- ## 0. Mental model: place constraints on the DB, not on "the app's goodwill" In a product where I was thorough about type safety, I make it a habit to design so that an illegal state **can't be expressed in the first place.** The database is the same. The premise that "the app will always put correct values in" is always broken by the second year of operation. **Place uniqueness, NOT NULL, foreign keys, and referential actions on the schema (= DB constraints), not on an app if-statement** — this is the first principle of data modeling that doesn't break down. Prisma's schema is the place to declaratively write this "lean constraints onto the DB." And the constraints you write are reflected directly into the **types** too. `String?` is `string | null`, `@unique` is the argument of `findUnique`, a relation is the shape of `include` — your schema-design judgments come back as the code's types. --- ## 1. A relation is "one pair of 2 fields" The first thing to make sink in with Prisma relations is that **one relation is always expressed by 2 fields.** The `@relation(fields, references)` of the side that has the foreign key (FK), and its back-reference side. Understand this correspondence and all relations come to look like variations of the same principle. ### 1.1 1:N (most common) The most-used form. Place the FK on the "N" side. ```prisma model User { id Int @id @default(autoincrement()) posts Post[] // 逆参照（リストになる） } model Post { id Int @id @default(autoincrement()) author User @relation(fields: [authorId], references: [id]) // FK側 authorId Int // 実際の外部キー列 } ``` `Post.author` and `Post.authorId` are **the FK-holding side**, and `User.posts` is **the back-reference side** (an array). To make `author` **optional**, add `?` to both. ```prisma model Post { id Int @id @default(autoincrement()) author User? @relation(fields: [authorId], references: [id]) authorId Int? // 著者なしの投稿を許す } ``` ### 1.2 1:1 The difference from 1:N is **just one** — add `@unique` to the FK column. Enforce "at most 1 profile per user" on both the type and the DB with a unique constraint. ```prisma model User { id Int @id @default(autoincrement()) profile Profile? // FKを持たない側は任意(?)にする } model Profile { id Int @id @default(autoincrement()) user User @relation(fields: [userId], references: [id]) userId Int @unique // ← これが 1対1 の鍵 } ``` Without `userId @unique`, this is just a 1:N. **Whether it's "1:1 or 1:N" is decided by the presence of `@unique`** — remember this one point and you won't get lost in design. --- ## 2. N:N: implicit or explicit N:N (article ⇄ tag, etc.) has 2 designs. **The only axis is "whether you want to give the join table itself attributes."** ### 2.1 Implicit (a Prisma-managed join table) If the join table needs no extra columns, leave it to Prisma. Just place lists on both models and Prisma auto-generates the join table (e.g., `_CategoryToPost`). ```prisma model Post { id Int @id @default(autoincrement()) title String categories Category[] } model Category { id Int @id @default(autoincrement()) name String posts Post[] } ``` The implicit join table requires that both models **have a single `@id`**, and you can't specify `fields` / `references` / `onDelete` / `onUpdate`. It's optimal for a simple relation. ### 2.2 Explicit (define the join model yourself) If you need **attributes of the relation itself** like "when and by whom it was linked," make the join model explicit. This is overwhelmingly the more common form in practice. ```prisma model Post { id Int @id @default(autoincrement()) title String categories CategoriesOnPosts[] } model Category { id Int @id @default(autoincrement()) name String posts CategoriesOnPosts[] } model CategoriesOnPosts { post Post @relation(fields: [postId], references: [id]) postId Int category Category @relation(fields: [categoryId], references: [id]) categoryId Int assignedAt DateTime @default(now()) // 関連の属性 assignedBy String @@id([postId, categoryId]) // 複合主キー } ``` Declare a **composite primary key** with `@@id([postId, categoryId])` and guarantee "the same combination only once." The strength of the explicit form is being able to give the relation audit info like `assignedAt` / `assignedBy`. > **Design judgment (YAGNI)**: when in doubt, start from implicit and migrate to explicit the moment the relation needs attributes. Pre-emptively making everything an explicit model only increases the boilerplate. --- ## 3. Referential actions: don't trust the defaults, make the intent explicit When you delete/update a parent record, what to do with the children — declare it with `onDelete` / `onUpdate`. **The defaults here can be counterintuitive and a hotbed of accidents.** ```prisma model Post { id Int @id @default(autoincrement()) author User @relation(fields: [authorId], references: [id], onDelete: Cascade) authorId Int } ``` The specifiable values are the 5 of **`Cascade` / `Restrict` / `NoAction` / `SetNull` / `SetDefault`.** The default when omitted changes by whether the relation is optional or required. | Clause | Optional relation (FK is `?`) | Required relation | | --- | --- | --- | | `onDelete` | `SetNull` | `Restrict` | | `onUpdate` | `Cascade` | `Cascade` | Three points. - **`onDelete: Cascade`** is "delete a parent and the children too." It's reasonable for "children subordinate to the parent" like comments or line items, but **don't casually attach it to 'children that must not disappear' like audit logs or payment records.** - **`SetNull`** can't be used unless the FK is **optional (`?`).** **`SetDefault`** needs a `@default` on the FK column. - The default `Restrict` (refuse deletion if children remain on the parent) is on the safe side, but I recommend **making `onDelete` explicit every time, not "vaguely the default."** The intent appears in the schema and can be judged in review. > **Portability note**: `Restrict` can't be used in SQL Server, where you use `NoAction`. Self-relations and circular references also need `NoAction` in SQL Server / MongoDB. --- ## 4. relationMode: hold FKs in the DB or in Prisma `relationMode` is the choice of whether to protect referential integrity **with the DB's foreign-key constraints** or **pseudo-protect it with Prisma Client's query logic.** ```prisma // 既定：本物のFK制約をDBに張る（PostgreSQL等） datasource db { provider = "postgresql" relationMode = "foreignKeys" } ``` ```prisma // FK非対応/無効なDB向け（PlanetScale、シャーディング等） datasource db { provider = "mysql" relationMode = "prisma" } ``` - **`foreignKeys` (default)**: enforce real FK constraints and referential actions at the DB level. Since the DB guarantees integrity, this is usually the only choice. - **`prisma`**: in an environment that doesn't support / has disabled FKs (MongoDB, PlanetScale not drawing foreign keys, etc.), Prisma emulates integrity. The **most important pitfall** of `prisma` mode: in this mode, Migrate / `db push` **doesn't auto-generate indexes for FK columns.** An FK without an index invites a full scan on every join, slow and high-cost. **You need to stick `@@index` yourself.** ```prisma model Post { id Int @id @default(autoincrement()) userId Int user User @relation(fields: [userId], references: [id]) @@index([userId]) // prismaモードでは必須 } ``` --- ## 5. IDs, constraints, defaults ### 5.1 Primary keys and composite constraints ```prisma model User { id Int @id @default(autoincrement()) email String @unique } // 複合主キー model Member { orgId Int userId Int role String @@id([orgId, userId]) } // 複合ユニーク・複合インデックス model Post { id Int @id @default(autoincrement()) authorId Int title String @@unique([authorId, title]) // 同一著者の同名記事を禁止 @@index([authorId, title]) // 検索を高速化 } ``` `@id` / `@unique` are **column-level**, and `@@id` / `@@unique` / `@@index` are **block-level that bundle multiple columns.** Every model must have a unique identifier with either a single `@id` or a composite `@@id`. ### 5.2 Default-value generation functions ```prisma model Post { id Int @id @default(autoincrement()) uuid String @default(uuid(7)) // 時系列ソート可能なUUIDv7 cuid String @default(cuid(2)) // 衝突しにくい短いID createdAt DateTime @default(now()) published Boolean @default(false) } ``` Representative functions are `autoincrement()` / `cuid()` (`cuid(2)` also OK) / `uuid()` (`uuid(4)` / `uuid(7)`) / `now()` / `dbgenerated(...)`, etc. **The primary-key strategy is a design judgment** — for an ID in a distributed environment or exposed in a URL, `uuid(7)` / `cuid(2)` (hard to guess, and can have time-series property) falls to the safe side over a sequence. When you need a DB-specific default expression, leave it to the DB with `dbgenerated("...")`. --- ## 6. Scalar types, native types, enum, self-relations ### 6.1 Types and native types Prisma's scalar types are the 9 of `String` / `Boolean` / `Int` / `BigInt` / `Float` / `Decimal` / `DateTime` / `Json` / `Bytes`. You can override the **DB's physical type** with `@db.*`. ```prisma model Article { id Int @id @default(autoincrement()) slug String @db.VarChar(200) // text ではなく varchar(200) body String @db.Text price Decimal // 金額は Float ではなく Decimal（丸め誤差を避ける） metadata Json // PostgreSQL では jsonb にマップ tags String[] // スカラーのリスト（PostgreSQL/CockroachDB/MongoDB） } ``` > **Design tip**: **don't use `Float` for values requiring accuracy** like amounts and quantities. Use `Decimal` (or an integer minor unit). On the payment foundation I unified amounts into integer minor units and structurally eliminated rounding errors. The choice of type is the design of correctness itself. ### 6.2 enum and self-relations ```prisma enum Role { USER ADMIN } model User { id Int @id @default(autoincrement()) role Role @default(USER) // 自己参照（1対多）：上司と部下 managerId Int? manager User? @relation("OrgChart", fields: [managerId], references: [id]) reports User[] @relation("OrgChart") } ``` For a self-relation, Prisma recognizes the pair by **both sides sharing the same `@relation("name")`.** enum maps to the DB's enum type in PostgreSQL/MySQL/MongoDB/CockroachDB (SQLite/SQL Server are not natively supported). --- ## 7. Retrofit onto an existing DB: @map and db pull The case of **loading Prisma onto an existing DB** rather than a new one is common, and here `@map` / `@@map` works. You can **separate** the name in the code (camelCase, etc.) and the physical name (snake_case, etc.). ```prisma model User { id Int @id @default(autoincrement()) firstName String @map("first_name") // 列名は first_name posts Post[] @@map("users") // テーブル名は users } ``` To raise a schema from an existing DB, first take in the current state with **introspection.** ```bash npx prisma db pull # 既存DB → schema.prisma を生成（上書き） npx prisma generate # 型付きクライアントを生成 ``` Against the raw schema generated by `db pull`, tidy the app-side naming with `@map` and make relations explicit with `@relation` — this is the standard for existing-DB introduction. Baselining (the procedure to start the migration history without breaking the existing DB) is detailed in the [Prisma Migrate production-operations guide](/blog/prisma-migrate-production-zero-downtime-cicd-guide). > **Multi-schema**: in PostgreSQL/SQL Server, you can assign a model to a specific schema with `@@schema("public")` (assuming the `multiSchema` feature and the `schemas` specification in datasource). --- ## 8. Schema-design checklist The items I always confirm in a schema before shipping to production. - [ ] Every model has a unique identifier with `@id` or `@@id` - [ ] A relation has **both** the FK side `@relation(fields, references)` and the back-reference side - [ ] 1:1 has `@unique` on the FK column (forgetting it = effectively 1:N) - [ ] N:N chooses implicit/explicit by "whether the join table needs attributes" - [ ] **`onDelete` / `onUpdate` explicit every time** (especially judge whether a child may have Cascade). No casual Cascade on audit/payment children - [ ] `@@index` on FK columns / search-condition columns (mandatory if `relationMode = "prisma"`) - [ ] Amounts/quantities are `Decimal` or integer minor units (avoid `Float`) - [ ] Consider `uuid(7)` / `cuid(2)` over a sequence for URL/external-exposed IDs - [ ] For an existing DB, separate code name and physical name with `@map`/`@@map` and take in the current state with `db pull` - [ ] Design unique constraints down to a composite `@@unique` so they can be used for idempotency (`upsert` keys) --- ## Conclusion Prisma schema design is the work of "**placing constraints on the DB, not the app, and declaring their intent in a form that appears in the types.**" A relation is one pair of 2 fields, 1:1 is `@unique`, N:N is implicit/explicit by the presence of attributes, referential actions are explicit not relying on the default, and in `relationMode = "prisma"` you stick indexes yourself — the accumulation of these design judgments makes a data model that doesn't break later. Once the schema is solid, next is **how to keep applying it safely to the production DB.** For non-stop schema changes and CI/CD, head to the [Prisma Migrate production-operations guide](/blog/prisma-migrate-production-zero-downtime-cicd-guide). "**I want to solidify everything end-to-end from data-model design to DB operation that doesn't break in production**" — if you have such a requirement, I can help from design review through implementation.