test.md

Union Types and Prisma

---

Table of Contents

• Introduction to Union Types
  • Use Cases
• Modeling Unions in Plain SQL
• Prisma Implementation

---

Introduction to Union Types

Union types are a set of mutually exclusive types. In the context of typescript, the types are separated by the "|" operator. For example,

type Union = string | number | null

The union type above can either be a string, number or null

Use Cases

Union types are helpful when we have a limited number of possible states, but we don't know in advance which one we will use during runtime.

Some examples are

• type MoneyTransfer = Failed | Successful | Transit 

•  type Pet = Dog | Cat | Turtle | Bird

•  type PaymentDetails = StripePaymentDetails | PaypalPaymentDetails

Modelling Unions in Plain SQL

We will be modelling the following typescript UNION class in plain SQL for our samples

type Student = {
  type: "student";
  id: number;
  name: string;
  age: number;
  grade: string;
};

type Teacher = {
  type: "teacher";
  id: number;
  name: string;
  age: number;
  subject: string;
};

type Person = Student | Teacher;

Type Column

---
title: Type Column
---
erDiagram
  people {
    id int PK
    type varchar
    name varchar
    age int
  }

  students {
    id int PK,FK
    grade varchar
  }

  teacher {
    id int PK,FK
    subject varchar
  }

  people ||--|o students: belongs
  people ||--|o teacher: belongs

CREATE TABLE people (
    id SERIAL PRIMARY KEY,
    type TEXT NOT NULL, -- discriminant field either students or teachers
    name TEXT NOT NULL,
    age INTEGER NOT NULL
);

CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    grade TEXT NOT NULL,
    FOREIGN KEY (id) REFERENCES people(id),
    UNIQUE (id)
);

CREATE TABLE teachers (
    id INTEGER PRIMARY KEY,
    subject TEXT NOT NULL,
    FOREIGN KEY (id) REFERENCES people(id),
    UNIQUE (id)
);

Pros -

• Simple schema design with only one table for people.
• Easy to manage and understand.
• Less storage overhead compared to the join tables pattern.

Cons -

• Orphaned records - Parent records without child records, caused by improper deletion of child records.

Reverse belongs to

---
title: Reverse Belongs To
---

erDiagram
  people {
    id int PK
    name varchar
    age int
  }

  students {
    id int PK
    personId int FK
    grade varchar
  }

  teacher {
    id int PK
    personId int FK
    subject varchar
  }

  people ||--|o students: belongs
  people ||--|o teacher: belongs

CREATE TABLE people (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  age INTEGER NOT NULL
);

CREATE TABLE students (
  id SERIAL PRIMARY KEY,
  person_id INTEGER NOT NULL UNIQUE,
  grade TEXT NOT NULL,
  FOREIGN KEY (person_id) REFERENCES people(id)
);

CREATE TABLE teachers (
  id SERIAL PRIMARY KEY,
  person_id INTEGER NOT NULL UNIQUE,
  subject TEXT NOT NULL,
  FOREIGN KEY (person_id) REFERENCES people(id)
);

Pros -

1. Clear separation of concerns between the different roles (e.g., students and teachers).
2. Easy to extend the schema with additional roles or properties.
3. Easier to enforce referential integrity.

Cons -

1. Higher storage overhead compared to the type column pattern.

---

Json Strategy

For SQL documents supporting JSON fields, this strategy could be used.

CREATE TABLE people (
  id SERIAL PRIMARY KEY,
  data JSON NOT NULL,
  type TEXT NOT NULL
);

Pros -

• Flexible - you can add custom fields without altering the database
• No Joins - reduces complex joins, this can lead to faster query performance
• Compact - is more compact when compared to values stored in multiple columns

Cons -

• Data Integrity - harder to enforce data integrity constraints
• Scalability - when the stored data grows, due to data being stored on a single column, performance issues might be faced

Prisma Implementation

Getting Started

Make sure you have Postgres running and fill in the contents of the .env file

yarn 
yarn migrate
yarn seed
yarn dev

Go to http://localhost:4000 and use the Apollo Client to interact with the GraphQl server

Modeling Unions in Prisma

Check out a simple way to model union types in Prisma here

The ERD diagram for the approach is mentioned below

The idea is to create a one to one relationship for the subtypes. You can only query by the type field in the Activity table

await prisma.activity.findMany({
  where: {
    type: "Image",
  },
})

The approach

1. Keep all the common fields in the Activity table in this case it's the label
2. Delegate all the business logic in the application layer
   • Ensure that a subtype (Image|Post|Video) is created whenever an Activity is created
3. Handle types in the Application Layer

Pros of this approach

• Simple to implement
• Easy to add new subtables with Prisma

Drawback of this approach

• Manually the Activity table can be edited to have no additional data or an Activity can have multiple types such as Video or Post

  This could be solved by implementing a database trigger to ensure that the subfields are mutually exclusive and the Activity table has at least one of the values (Image| Video| Post), however, triggers are not supported by Prisma but could be achieved by writing out a manual query