## Overview

Table inheritance is a software design pattern that allows the modeling of hierarchical relationships between entities. Using table inheritance on the database level can also enable the use of union types in your JavaScript/TypeScript application or share a set of common properties across multiple models.

This page introduces two approaches to table inheritance and explains how to use them with Prisma ORM.

A common use case for table inheritance may be when an application needs to display a _feed_ of some kind of _content activities_. A content activity in this case, could be a _video_ or an _article_. As an example, let's assume that:

- a content activity always has an `id` and a `url`
- in addition to `id` and a `url`, a video also has a `duration` (modeled as an `Int`)
- in addition to `id` and a `url`, an article also a `body` (modeled as a `String`)

### Use cases

#### Union types

Union types are a convenient feature in TypeScript that allows developers to work more flexibly with the types in their data model.

In TypeScript, union types look as follows:

```ts no-copy
type Activity = Video | Article
```

While [it's currently not possible to model union types in the Prisma schema](https://github.com/prisma/prisma/issues/2505), you can use them with Prisma ORM by using table inheritance and some additional type definitions.

#### Sharing properties across multiple models

If you have a use case where multiple models should share a particular set of properties, you can model this using table inheritance as well.

For example, if both the `Video` and `Article` models from above should have a shared `title` property, you can achieve this with table inheritance as well.

### Example

In a simple Prisma schema, this would look as follows. Note that we're adding a `User` model as well to illustrate how this can work with relations:

```prisma file=schema.prisma showLineNumbers
model Video {
  id       Int    @id
  url      String @unique
  duration Int

  user   User @relation(fields: [userId], references: [id])
  userId Int
}

model Article {
  id   Int    @id
  url  String @unique
  body String

  user   User @relation(fields: [userId], references: [id])
  userId Int
}

model User {
  id       Int       @id
  name     String
  videos   Video[]
  articles Article[]
}
```

Let's investigate how we can model this using table inheritance.

### Single-table vs multi-table inheritance

Here is a quick comparison of the two main approaches for table inheritance:

- **Single-table inheritance (STI)**: Uses a _single_ table to store data of _all_ the different entities in one location. In our example, there'd be a single `Activity` table with the `id`, `url` as well as the `duration` and `body` column. It also uses a `type` column that indicates whether an _activity_ is a _video_ or an _article_.
- **Multi-table inheritance (MTI)**: Uses _multiple_ tables to store the data of the different entities separately and links them via foreign keys. In our example, there'd be an `Activity` table with the `id`, `url` column, a `Video` table with the `duration` and a foreign key to `Activity` as well as an `Article` table with the `body` and a foreign key. There is also a `type` column that acts as a discriminator and indicates whether an _activity_ is a _video_ or an _article_. Note that multi-table inheritance is also sometimes called _delegated types_.

You can learn about the tradeoffs of both approaches [below](#tradeoffs-between-sti-and-mti).

## Single-table inheritance (STI)

### Data model

Using STI, the above scenario can be modeled as follows:

```prisma
model Activity {
  id       Int          @id // shared
  url      String       @unique // shared
  duration Int? // video-only
  body     String? // article-only
  type     ActivityType // discriminator

  owner   User @relation(fields: [ownerId], references: [id])
  ownerId Int
}

enum ActivityType {
  Video
  Article
}

model User {
  id         Int        @id @default(autoincrement())
  name       String?
  activities Activity[]
}
```

A few things to note:

- The model-specific properties `duration` and `body` must be marked as optional (i.e., with `?`). That's because a record in the `Activity` table that represents a _video_ must not have a value for `body`. Conversely, an `Activity` record representing an _article_ can never have a `duration` set.
- The `type` discriminator column indicates whether each record represents a _video_ or an _article_ item.

### Prisma Client API

Due to how Prisma ORM generates types and an API for the data model, there will only to be an `Activity` type and the CRUD queries that belong to it (`create`, `update`, `delete`, ...) available to you.

#### Querying for videos and articles

You can now query for only _videos_ or _articles_ by filtering on the `type` column. For example:

```ts
// Query all videos
const videos = await prisma.activity.findMany({
  where: { type: 'Video' },
})

// Query all articles
const articles = await prisma.activity.findMany({
  where: { type: 'Article' },
})
```

#### Defining dedicated types

When querying for videos and articles like that, TypeScript will still only recognize an `Activity` type. That can be annoying because even the objects in `videos` will have (optional) `body` and the objects in `articles` will have (optional) `duration` fields.

If you want to have type safety for these objects, you need to define dedicated types for them. You can do this, for example, by using the generated `Activity` type and the TypeScript `Omit` utility type to remove properties from it:

```ts
import { Activity } from '../prisma/generated/client'

type Video = Omit<Activity, 'body' | 'type'>
type Article = Omit<Activity, 'duration' | 'type'>
```

In addition, it will be helpful to create mapping functions that convert an object of type `Activity` to the `Video` and `Article` types:

```ts
function activityToVideo(activity: Activity): Video {
  return {
    url: activity.url,
    duration: activity.duration ? activity.duration : -1,
    ownerId: activity.ownerId,
  } as Video
}

function activityToArticle(activity: Activity): Article {
  return {
    url: activity.url,
    body: activity.body ? activity.body : '',
    ownerId: activity.ownerId,
  } as Article
}
```

Now you can turn an `Activity` into a more specific type (i.e., `Article` or `Video`) after querying:

```ts
const videoActivities = await prisma.activity.findMany({
  where: { type: 'Video' },
})
const videos: Video[] = videoActivities.map(activityToVideo)
```

#### Using Prisma Client extension for a more convenient API

You can use [Prisma Client extensions](/orm/prisma-client/client-extensions) to create a more convenient API for the table structures in your database.

## Multi-table inheritance (MTI)

### Data model

Using MTI, the above scenario can be modeled as follows:

```prisma
model Activity {
  id   Int          @id @default(autoincrement())
  url  String // shared
  type ActivityType // discriminator

  video   Video? // model-specific 1-1 relation
  article Article? // model-specific 1-1 relation

  owner   User @relation(fields: [ownerId], references: [id])
  ownerId Int
}

model Video {
  id         Int      @id @default(autoincrement())
  duration   Int // video-only
  activityId Int      @unique
  activity   Activity @relation(fields: [activityId], references: [id])
}

model Article {
  id         Int      @id @default(autoincrement())
  body       String // article-only 
  activityId Int      @unique
  activity   Activity @relation(fields: [activityId], references: [id])
}

enum ActivityType {
  Video
  Article
}

model User {
  id         Int        @id @default(autoincrement())
  name       String?
  activities Activity[]
}
```

A few things to note:

- A 1-1 relation is needed between `Activity` and `Video` as well as `Activity` and `Article`. This relationship is used to fetch the specific information about a record when needed.
- The model-specific properties `duration` and `body` can be made _required_ with this approach.
- The `type` discriminator column indicates whether each record represents a _video_ or an _article_ item.

### Prisma Client API

This time, you can query for videos and articles directly via the `video` and `article` properties on your `PrismaClient` instance.

#### Querying for videos and articles

If you want to access the shared properties, you need to use `include` to fetch the relation to `Activity`.

```ts
// Query all videos
const videos = await prisma.video.findMany({
  include: { activity: true },
})

// Query all articles
const articles = await prisma.article.findMany({
  include: { activity: true },
})
```

Depending on your needs, you may also query the other way around by filtering on the `type` discriminator column:

```ts
// Query all videos
const videoActivities = await prisma.activity.findMany({
  where: { type: 'Video' }
  include: { video: true }
})
```

#### Defining dedicated types

While a bit more convenient in terms of types compared to STI, the generated typings likely still won't fit all your needs.

Here's how you can define `Video` and `Article` types by combining Prisma ORM's generated `Video` and `Article` types with the `Activity` type. These combinations create a new type with the desired properties. Note that we're also omitting the `type` discriminator column because that's not needed anymore on the specific types:

```ts
import {
  Video as VideoDB,
  Article as ArticleDB,
  Activity,
} from '../prisma/generated/client'

type Video = Omit<VideoDB & Activity, 'type'>
type Article = Omit<ArticleDB & Activity, 'type'>
```

Once these types are defined, you can define mapping functions to convert the types you receive from the queries above into the desired `Video` and `Article` types. Here's the example for the `Video` type:

```ts
import { Prisma, Video as VideoDB, Activity } from '../prisma/generated/client'

type Video = Omit<VideoDB & Activity, 'type'>

// Create `VideoWithActivity` typings for the objects returned above
const videoWithActivity = Prisma.validator<Prisma.VideoDefaultArgs>()({
  include: { activity: true },
})
type VideoWithActivity = Prisma.VideoGetPayload<typeof videoWithActivity>

// Map to `Video` type
function toVideo(a: VideoWithActivity): Video {
  return {
    id: a.id,
    url: a.activity.url,
    ownerId: a.activity.ownerId,
    duration: a.duration,
    activityId: a.activity.id,
  }
}
```

Now you can take the objects returned by the queries above and transform them using `toVideo`:

```ts
const videoWithActivities = await prisma.video.findMany({
  include: { activity: true },
})
const videos: Video[] = videoWithActivities.map(toVideo)
```

#### Using Prisma Client extension for a more convenient API

You can use [Prisma Client extensions](/orm/prisma-client/client-extensions) to create a more convenient API for the table structures in your database.

## Tradeoffs between STI and MTI

- **Data model**: The data model may feel more clean with MTI. With STI, you may end up with very wide rows and lots of columns that have `NULL` values in them.
- **Performance**: MTI may come with a performance cost because you need to join the parent and child tables to access _all_ properties relevant for a model.
- **Typings**: With Prisma ORM, MTI gives you proper typings for the specific models (i.e., `Article` and `Video` in the examples above) already, while you need to create these from scratch with STI.
- **IDs / Primary keys**: With MTI, records have two IDs (one on the parent and another on the child table) that may not match. You need to consider this in the business logic of your application.

## Third-party solutions

While Prisma ORM doesn't natively support union types or polymorphism at the moment, you can check out [Zenstack](https://github.com/zenstackhq/zenstack) which is adding an extra layer of features to the Prisma schema. Read their [blog post about polymorphism in Prisma ORM](https://zenstack.dev/blog/polymorphism) to learn more.

---

# Data model