System Design (LLD)ORM Considerations

Object-Relational Mapping Considerations

LevelIntermediate

Duration75 mins

TopicORM Considerations

3 / 4

Mapping Strategies

Bridging Two Worlds

The object-relational impedance mismatch manifests most acutely in mapping—the translation between object-oriented constructs and relational database structures. How do you represent a class hierarchy in flat tables? How do you store a composite object? How do you model bidirectional relationships when foreign keys are inherently unidirectional?

These are not trivial questions. The mapping strategy you choose affects query performance, storage efficiency, schema flexibility, and code maintainability. Different strategies optimize for different concerns, and the 'right' choice depends on your specific context.

This page explores the major mapping strategies employed by ORM frameworks, giving you the knowledge to make informed decisions about how your objects become tables.

What You Will Learn

By the end of this page, you will understand: (1) Inheritance mapping strategies (Single Table, Table Per Class, Joined), (2) Relationship mapping strategies (one-to-one, one-to-many, many-to-many), (3) Value type and embedded object mapping, (4) Column type mapping and custom conversions, and (5) How to choose the right mapping strategy for your context.

Inheritance Mapping Strategies

Object-oriented languages embrace inheritance as a fundamental modeling tool. Relational databases have no native inheritance concept. This mismatch requires explicit strategies to represent class hierarchies in database tables.

Three primary strategies exist, each with distinct trade-offs:

Single Table Inheritance (STI)

•Concept: All classes in the hierarchy share a single database table. A discriminator column identifies the concrete type of each row.
•Columns: The table contains columns for properties of ALL classes in the hierarchy. Rows only populate columns relevant to their type.
•Pros: Fast queries (no joins), simple schema, easy to add new subtypes
•Cons: Many nullable columns, wasted space, no database-level constraints on subtype-specific columns
•Best for: Shallow hierarchies with similar subtypes and few subtype-specific fields

single-table-inheritance.ts
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
// ==========================================
// SINGLE TABLE INHERITANCE (STI)
// ==========================================
 
// Object Model: Inheritance hierarchy
abstract class Payment {
    id: string;
    amount: number;
    currency: string;
    createdAt: Date;
}
 
class CreditCardPayment extends Payment {
    cardLast4: string;
    expiryMonth: number;
    expiryYear: number;
}
 
class BankTransferPayment extends Payment {
    bankName: string;
    accountLast4: string;
    routingNumber: string;
}
 
class CryptoPayment extends Payment {
    walletAddress: string;
    network: string;
    txHash: string;
}
 
// Database Schema: Single table with discriminator
// CREATE TABLE payments (
//     id UUID PRIMARY KEY,
//     payment_type VARCHAR(50) NOT NULL,  -- Discriminator column
//     amount DECIMAL(10,2) NOT NULL,
//     currency VARCHAR(3) NOT NULL,
//     created_at TIMESTAMP NOT NULL,
//     
//     -- CreditCardPayment fields (nullable for other types)
//     card_last4 VARCHAR(4),
//     expiry_month INTEGER,
//     expiry_year INTEGER,
//     
//     -- BankTransferPayment fields (nullable for other types)
//     bank_name VARCHAR(100),
//     account_last4 VARCHAR(4),
//     routing_number VARCHAR(20),
//     
//     -- CryptoPayment fields (nullable for other types)
//     wallet_address VARCHAR(100),
//     network VARCHAR(20),
//     tx_hash VARCHAR(100)
// );
 
// Example data:
// | id  | payment_type  | amount | card_last4 | bank_name | wallet_address |
// |-----|---------------|--------|------------|-----------|----------------|
// | a1  | credit_card   | 100.00 | 4242       | NULL      | NULL           |
// | a2  | bank_transfer | 500.00 | NULL       | Chase     | NULL           |
// | a3  | crypto        | 250.00 | NULL       | NULL      | 0x1234...      |
 
// ORM Mapping (TypeORM-style)
@Entity('payments')
@TableInheritance({ column: { type: 'varchar', name: 'payment_type' } })
abstract class Payment {
    @PrimaryColumn()
    id: string;
    
    @Column('decimal')
    amount: number;
    
    @Column()
    currency: string;
}
 
@ChildEntity('credit_card')
class CreditCardPayment extends Payment {
    @Column({ nullable: true })
    cardLast4: string;
}
 
// Queries are efficient - no joins needed:
// SELECT * FROM payments WHERE payment_type = 'credit_card'

Table Per Class (Concrete Table Inheritance)

•Concept: Each concrete class gets its own table. Abstract base classes have no table.
•Columns: Each table contains columns for all properties—both inherited and class-specific.
•Pros: No nullable columns, efficient queries for single type, natural table structure
•Cons: Duplicate columns across tables, polymorphic queries require UNION, hard to enforce unique constraints across hierarchy
•Best for: Distinct subtypes with minimal polymorphic access needs

table-per-class.ts
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
// ==========================================
// TABLE PER CLASS (CONCRETE TABLE INHERITANCE)
// ==========================================
 
// Same object model as before, but different schema:
 
// CREATE TABLE credit_card_payments (
//     id UUID PRIMARY KEY,
//     amount DECIMAL(10,2) NOT NULL,
//     currency VARCHAR(3) NOT NULL,
//     created_at TIMESTAMP NOT NULL,
//     card_last4 VARCHAR(4) NOT NULL,
//     expiry_month INTEGER NOT NULL,
//     expiry_year INTEGER NOT NULL
// );
 
// CREATE TABLE bank_transfer_payments (
//     id UUID PRIMARY KEY,
//     amount DECIMAL(10,2) NOT NULL,       -- Duplicated from base
//     currency VARCHAR(3) NOT NULL,         -- Duplicated from base
//     created_at TIMESTAMP NOT NULL,        -- Duplicated from base
//     bank_name VARCHAR(100) NOT NULL,
//     account_last4 VARCHAR(4) NOT NULL,
//     routing_number VARCHAR(20) NOT NULL
// );
 
// CREATE TABLE crypto_payments (
//     id UUID PRIMARY KEY,
//     amount DECIMAL(10,2) NOT NULL,       -- Duplicated from base
//     currency VARCHAR(3) NOT NULL,         -- Duplicated from base
//     created_at TIMESTAMP NOT NULL,        -- Duplicated from base
//     wallet_address VARCHAR(100) NOT NULL,
//     network VARCHAR(20) NOT NULL,
//     tx_hash VARCHAR(100) NOT NULL
// );
 
// Single-type queries are efficient:
// SELECT * FROM credit_card_payments WHERE amount > 100
 
// Polymorphic queries require UNION (expensive):
// SELECT id, amount, 'credit_card' as type FROM credit_card_payments
// UNION ALL
// SELECT id, amount, 'bank_transfer' as type FROM bank_transfer_payments
// UNION ALL
// SELECT id, amount, 'crypto' as type FROM crypto_payments
// ORDER BY amount DESC
 
// ORM mapping typically uses @TableInheritance({ type: 'CONCRETE' })
// or equivalent annotation

Joined Table Inheritance (Class Table Inheritance)

•Concept: Each class (including abstract) gets its own table. Subclass tables join to superclass tables.
•Columns: Base properties in base table, subclass properties in subclass table. Connected by foreign key.
•Pros: Normalized schema, no nulls, database constraints work naturally, schema reflects hierarchy
•Cons: Queries require joins, insert/update spans multiple tables, more complex schema
•Best for: Deep hierarchies, normalized database design preferences, subtype-specific constraints needed

joined-table-inheritance.ts
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
// ==========================================
// JOINED TABLE INHERITANCE (CLASS TABLE)
// ==========================================
 
// CREATE TABLE payments (
//     id UUID PRIMARY KEY,
//     payment_type VARCHAR(50) NOT NULL,  -- Discriminator still useful
//     amount DECIMAL(10,2) NOT NULL,
//     currency VARCHAR(3) NOT NULL,
//     created_at TIMESTAMP NOT NULL
// );
 
// CREATE TABLE credit_card_payments (
//     id UUID PRIMARY KEY REFERENCES payments(id),
//     card_last4 VARCHAR(4) NOT NULL,
//     expiry_month INTEGER NOT NULL,
//     expiry_year INTEGER NOT NULL
// );
 
// CREATE TABLE bank_transfer_payments (
//     id UUID PRIMARY KEY REFERENCES payments(id),
//     bank_name VARCHAR(100) NOT NULL,
//     account_last4 VARCHAR(4) NOT NULL,
//     routing_number VARCHAR(20) NOT NULL
// );
 
// CREATE TABLE crypto_payments (
//     id UUID PRIMARY KEY REFERENCES payments(id),
//     wallet_address VARCHAR(100) NOT NULL,
//     network VARCHAR(20) NOT NULL,
//     tx_hash VARCHAR(100) NOT NULL
// );
 
// Loading a CreditCardPayment requires a join:
// SELECT p.*, cc.*
// FROM payments p
// JOIN credit_card_payments cc ON p.id = cc.id
// WHERE p.id = 'some-id'
 
// Polymorphic query also requires joins but cleaner than UNION:
// SELECT p.id, p.amount, p.payment_type
// FROM payments p
// LEFT JOIN credit_card_payments cc ON p.id = cc.id
// LEFT JOIN bank_transfer_payments bt ON p.id = bt.id
// LEFT JOIN crypto_payments cp ON p.id = cp.id
// WHERE p.amount > 100
 
// ORM Mapping (TypeORM-style)
@Entity('payments')
@TableInheritance({ type: 'JOINED' })
abstract class Payment {
    @PrimaryColumn()
    id: string;
    
    @Column('decimal')
    amount: number;
}
 
@ChildEntity()
@Entity('credit_card_payments')
class CreditCardPayment extends Payment {
    @Column()
    cardLast4: string;
}

Inheritance Strategy Comparison
Strategy	Query Performance	Storage Efficiency	Schema Complexity	Polymorphic Queries
Single Table	Excellent (no joins)	Poor (many nulls)	Simple	Excellent
Table Per Class	Excellent (type-specific)	Good (no nulls)	Medium	Poor (UNION)
Joined Table	Moderate (joins needed)	Excellent (normalized)	Complex	Moderate

Relationship Mapping Strategies

Objects reference each other through memory pointers. Databases connect rows through foreign keys. Mapping relationships requires translating between these fundamentally different mechanisms.

The cardinality and ownership semantics of relationships determine the mapping strategy:

One-to-One Relationships

•Same Table: Embed the related entity's columns directly in the owning entity's table. Best for tightly coupled entities always accessed together.
•Separate Tables with FK: Each entity has its own table; a foreign key column links them. Allows independent loading and optional relationships.
•Shared Primary Key: Both tables use the same primary key value. The child table's PK is also the FK to the parent. Strong coupling but elegant.

one-to-one-mapping.ts
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
// ==========================================
// ONE-TO-ONE: Three Mapping Approaches
// ==========================================
 
// Object Model
class User {
    id: string;
    email: string;
    profile: UserProfile;  // One-to-one
}
 
class UserProfile {
    bio: string;
    avatarUrl: string;
    timezone: string;
}
 
// -----------------------------------------
// APPROACH 1: Same Table (Embedding)
// -----------------------------------------
 
// CREATE TABLE users (
//     id UUID PRIMARY KEY,
//     email VARCHAR(255) NOT NULL,
//     -- Profile fields embedded directly
//     profile_bio TEXT,
//     profile_avatar_url VARCHAR(500),
//     profile_timezone VARCHAR(50)
// );
 
// Prisma schema equivalent:
// model User {
//   id             String  @id @default(cuid())
//   email          String  @unique
//   profileBio     String?
//   profileAvatar  String?
//   profileTimezone String?
// }
 
// Pro: No joins, atomic operations
// Con: Profile not independently queryable, all fields nullable
 
// -----------------------------------------
// APPROACH 2: Separate Tables with FK
// -----------------------------------------
 
// CREATE TABLE users (
//     id UUID PRIMARY KEY,
//     email VARCHAR(255) NOT NULL
// );
 
// CREATE TABLE user_profiles (
//     id UUID PRIMARY KEY,
//     user_id UUID NOT NULL REFERENCES users(id) UNIQUE,
//     bio TEXT,
//     avatar_url VARCHAR(500),
//     timezone VARCHAR(50)
// );
 
// ORM Mapping (TypeORM-style)
@Entity('users')
class User {
    @PrimaryColumn()
    id: string;
    
    @Column()
    email: string;
    
    @OneToOne(() => UserProfile, profile => profile.user)
    profile: UserProfile;
}
 
@Entity('user_profiles')
class UserProfile {
    @PrimaryColumn()
    id: string;
    
    @Column()
    userId: string;
    
    @OneToOne(() => User, user => user.profile)
    @JoinColumn({ name: 'user_id' })
    user: User;
    
    @Column()
    bio: string;
}
 
// -----------------------------------------
// APPROACH 3: Shared Primary Key
// -----------------------------------------
 
// CREATE TABLE users (
//     id UUID PRIMARY KEY,
//     email VARCHAR(255) NOT NULL
// );
 
// CREATE TABLE user_profiles (
//     user_id UUID PRIMARY KEY REFERENCES users(id),  -- PK = FK
//     bio TEXT,
//     avatar_url VARCHAR(500),
//     timezone VARCHAR(50)
// );
 
// Pro: Elegant, enforces 1:1 at database level
// Con: Profile cannot exist without user

One-to-Many Relationships

•Standard FK Approach: The 'many' side holds a foreign key to the 'one' side. This is the dominant approach.
•Ownership Considerations: The FK always lives on the child/many side. From ORM perspective, you configure whether the parent 'owns' the relationship (cascade operations).
•Bidirectional Navigation: ORMs can provide navigation both ways: parent.children and child.parent. But the database only stores the FK on one side.

one-to-many-mapping.ts
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
// ==========================================
// ONE-TO-MANY: Author has many Books
// ==========================================
 
// Object Model
class Author {
    id: string;
    name: string;
    books: Book[];  // One-to-many
}
 
class Book {
    id: string;
    title: string;
    author: Author;  // Many-to-one (inverse)
}
 
// Database Schema
// CREATE TABLE authors (
//     id UUID PRIMARY KEY,
//     name VARCHAR(100) NOT NULL
// );
 
// CREATE TABLE books (
//     id UUID PRIMARY KEY,
//     title VARCHAR(255) NOT NULL,
//     author_id UUID NOT NULL REFERENCES authors(id)  -- FK on many side
// );
 
// ORM Mapping (TypeORM-style)
@Entity('authors')
class Author {
    @PrimaryColumn()
    id: string;
    
    @Column()
    name: string;
    
    @OneToMany(() => Book, book => book.author, {
        cascade: true,          // Operations propagate to children
        orphanRemoval: true,    // Delete children when removed from collection
    })
    books: Book[];
}
 
@Entity('books')
class Book {
    @PrimaryColumn()
    id: string;
    
    @Column()
    title: string;
    
    @Column()
    authorId: string;  // FK column explicitly mapped
    
    @ManyToOne(() => Author, author => author.books)
    @JoinColumn({ name: 'author_id' })
    author: Author;
}
 
// Loading with eager/lazy strategies
// Eager: Include in initial query
const author = await authorRepo.findOne({
    where: { id: authorId },
    relations: ['books']  // JOIN books in same query
});
 
// Lazy: Load on access (if ORM supports)
const author = await authorRepo.findOne({ where: { id: authorId } });
const books = await author.books;  // Triggers SELECT at access time

Many-to-Many Relationships

•Junction Table: M:N relationships require an intermediate table (also called join table, link table, or association table) with FKs to both sides.
•Simple Junction: Just two FK columns, representing pure relationship without additional data.
•Rich Junction: Junction table includes additional columns (e.g., enrollment date for student-course relationship). May become its own entity.
•Bidirectional Mapping: Both sides can navigate to each other, but the junction table is the source of truth.

many-to-many-mapping.ts
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
// ==========================================
// MANY-TO-MANY: Students enroll in Courses
// ==========================================
 
// Object Model
class Student {
    id: string;
    name: string;
    courses: Course[];  // Many-to-many
}
 
class Course {
    id: string;
    title: string;
    students: Student[];  // Many-to-many (inverse)
}
 
// -----------------------------------------
// SIMPLE JUNCTION TABLE
// -----------------------------------------
 
// CREATE TABLE students (
//     id UUID PRIMARY KEY,
//     name VARCHAR(100) NOT NULL
// );
 
// CREATE TABLE courses (
//     id UUID PRIMARY KEY,
//     title VARCHAR(255) NOT NULL
// );
 
// CREATE TABLE student_courses (  -- Junction table
//     student_id UUID REFERENCES students(id),
//     course_id UUID REFERENCES courses(id),
//     PRIMARY KEY (student_id, course_id)
// );
 
// ORM Mapping (TypeORM-style)
@Entity('students')
class Student {
    @PrimaryColumn()
    id: string;
    
    @ManyToMany(() => Course, course => course.students)
    @JoinTable({
        name: 'student_courses',
        joinColumn: { name: 'student_id' },
        inverseJoinColumn: { name: 'course_id' }
    })
    courses: Course[];
}
 
@Entity('courses')
class Course {
    @PrimaryColumn()
    id: string;
    
    @ManyToMany(() => Student, student => student.courses)
    students: Student[];  // No @JoinTable - inverse side
}
 
// -----------------------------------------
// RICH JUNCTION TABLE (with extra data)
// -----------------------------------------
 
// CREATE TABLE enrollments (
//     id UUID PRIMARY KEY,
//     student_id UUID NOT NULL REFERENCES students(id),
//     course_id UUID NOT NULL REFERENCES courses(id),
//     enrolled_at TIMESTAMP NOT NULL,
//     grade VARCHAR(2),
//     status VARCHAR(20) NOT NULL,
//     UNIQUE(student_id, course_id)
// );
 
// When junction has its own data, model it as an entity:
@Entity('enrollments')
class Enrollment {
    @PrimaryColumn()
    id: string;
    
    @ManyToOne(() => Student)
    @JoinColumn({ name: 'student_id' })
    student: Student;
    
    @ManyToOne(() => Course)
    @JoinColumn({ name: 'course_id' })
    course: Course;
    
    @Column()
    enrolledAt: Date;
    
    @Column({ nullable: true })
    grade: string;
    
    @Column()
    status: string;
}
 
// Now navigate through Enrollment entity:
class Student {
    @OneToMany(() => Enrollment, e => e.student)
    enrollments: Enrollment[];
}

When to Use Rich Junction Tables

If your junction table needs more than just the two foreign keys—timestamps, status, metadata—promote it to a full entity. This provides better query capabilities, clearer semantics, and room to grow. Many M:N relationships eventually need additional data; starting with a rich junction prevents future migrations.

Value Type and Embedded Object Mapping

Not everything is an entity with its own identity. Value objects (from DDD) represent concepts defined entirely by their attributes—like Money, Address, or DateRange. These don't need their own tables; they can be embedded within the entities that contain them.

Embedded mapping (also called component mapping) stores a value object's properties as additional columns in the owning entity's table. No separate table, no foreign key—just inline column expansion.

embedded-mapping.ts
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
// ==========================================
// EMBEDDED VALUE OBJECTS
// ==========================================
 
// Value Objects (no identity, compared by value)
class Money {
    constructor(
        public readonly amount: number,
        public readonly currency: string
    ) {}
    
    add(other: Money): Money {
        if (this.currency !== other.currency) {
            throw new Error('Currency mismatch');
        }
        return new Money(this.amount + other.amount, this.currency);
    }
    
    equals(other: Money): boolean {
        return this.amount === other.amount && 
               this.currency === other.currency;
    }
}
 
class Address {
    constructor(
        public readonly street: string,
        public readonly city: string,
        public readonly state: string,
        public readonly postalCode: string,
        public readonly country: string
    ) {}
}
 
// Entity containing embedded value objects
class Order {
    id: string;
    customerId: string;
    shippingAddress: Address;  // Embedded
    billingAddress: Address;   // Embedded
    totalAmount: Money;        // Embedded
}
 
// Database Schema - value objects become columns
// CREATE TABLE orders (
//     id UUID PRIMARY KEY,
//     customer_id UUID NOT NULL,
//     
//     -- Shipping Address (embedded with prefix)
//     shipping_street VARCHAR(255),
//     shipping_city VARCHAR(100),
//     shipping_state VARCHAR(50),
//     shipping_postal_code VARCHAR(20),
//     shipping_country VARCHAR(50),
//     
//     -- Billing Address (embedded with prefix)
//     billing_street VARCHAR(255),
//     billing_city VARCHAR(100),
//     billing_state VARCHAR(50),
//     billing_postal_code VARCHAR(20),
//     billing_country VARCHAR(50),
//     
//     -- Money (embedded)
//     total_amount DECIMAL(10,2),
//     total_currency VARCHAR(3)
// );
 
// ORM Mapping (TypeORM-style)
@Entity('orders')
class Order {
    @PrimaryColumn()
    id: string;
    
    @Column()
    customerId: string;
    
    @Embedded(() => Address, { prefix: 'shipping_' })
    shippingAddress: Address;
    
    @Embedded(() => Address, { prefix: 'billing_' })
    billingAddress: Address;
    
    @Embedded(() => Money, { prefix: 'total_' })
    totalAmount: Money;
}
 
@Embeddable()
class Address {
    @Column()
    street: string;
    
    @Column()
    city: string;
    
    @Column()
    state: string;
    
    @Column()
    postalCode: string;
    
    @Column()
    country: string;
}
 
@Embeddable()
class Money {
    @Column('decimal')
    amount: number;
    
    @Column()
    currency: string;
}
 
// Benefits of Embedding:
// 1. No joins needed - data co-located
// 2. Atomic updates - single row operation
// 3. Clear value semantics - not an independent entity
// 4. Column prefixes prevent naming collisions

When to Embed vs. When to Reference

•Embed when: The value has no independent existence, is always accessed with the parent, has no identity of its own, and the total column count remains reasonable.
•Reference when: The value needs independent querying, has its own lifecycle, is shared across entities, or the embedding would create too many columns.
•Consider JSON: For complex nested structures rarely queried individually, JSON/JSONB columns offer flexibility without column explosion.

Prisma and Embedded Types

Prisma doesn't support embedded types directly. Common workarounds include: (1) Flatten value object fields into the model, (2) Use JSON columns for complex embedded data, (3) Create separate models with 1:1 relationships. Each approach has trade-offs in type safety and query capabilities.

Column Type Mapping and Conversions

Programming languages and databases have different type systems. ORMs must translate between them, handling edge cases like:

Dates and times: JavaScript Date vs. SQL TIMESTAMP vs. TIMESTAMPTZ
Numbers: Integer overflow, decimal precision, BigInt handling
Booleans: True/false vs. 0/1 vs. 'Y'/'N'
Enums: Language enums vs. database enum types vs. string columns
JSON: Native JSON types vs. serialized strings

Understanding these conversions prevents subtle bugs that manifest as incorrect data or runtime errors.

Common Type Mapping Considerations
Application Type	PostgreSQL Type	Key Considerations
string	VARCHAR(n) / TEXT	Length limits, encoding (UTF-8), collation
number (integer)	INTEGER / BIGINT	Overflow at ~2B (INT) or ~9 quintillion (BIGINT)
number (decimal)	DECIMAL(p,s) / NUMERIC	Precision and scale; avoid FLOAT for money
boolean	BOOLEAN	NULL handling (three-state logic)
Date	TIMESTAMP / TIMESTAMPTZ	Timezone handling critical; prefer TIMESTAMPTZ
enum	ENUM type / VARCHAR	ENUM type safer but less flexible to modify
array	ARRAY / JSON	PostgreSQL arrays or JSON array; query patterns differ
object	JSONB	Schema-less but indexed; balance flexibility vs. structure
UUID	UUID	Native UUID type; generation strategy matters
BigInt	BIGINT	JavaScript BigInt requires serialization consideration

type-converters.ts
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
// ==========================================
// CUSTOM TYPE CONVERTERS
// ==========================================
 
// When standard conversions aren't enough, ORMs allow custom converters
 
// Example: Encrypting sensitive data before storage
class EncryptedStringTransformer {
    to(value: string): string {
        // Encrypt before writing to database
        return encrypt(value, process.env.ENCRYPTION_KEY);
    }
    
    from(value: string): string {
        // Decrypt after reading from database
        return decrypt(value, process.env.ENCRYPTION_KEY);
    }
}
 
@Entity('users')
class User {
    @Column({
        transformer: new EncryptedStringTransformer()
    })
    ssn: string;  // Stored encrypted, used decrypted
}
 
// Example: Storing Money as integer cents
class MoneyCentsTransformer {
    to(money: Money): number {
        // Store as integer cents (no floating point issues)
        return Math.round(money.amount * 100);
    }
    
    from(cents: number): Money {
        // Reconstruct Money object
        return new Money(cents / 100, 'USD');
    }
}
 
@Entity('products')
class Product {
    @Column({
        type: 'integer',
        transformer: new MoneyCentsTransformer()
    })
    price: Money;  // Stored as cents, used as Money
}
 
// Example: JSON serialization for complex objects
class JsonTransformer<T> {
    to(value: T): string {
        return JSON.stringify(value);
    }
    
    from(value: string): T {
        return JSON.parse(value) as T;
    }
}
 
interface OrderMetadata {
    source: string;
    affiliateId?: string;
    utmParameters: Record<string, string>;
}
 
@Entity('orders')
class Order {
    @Column({
        type: 'text',
        transformer: new JsonTransformer<OrderMetadata>()
    })
    metadata: OrderMetadata;
}
 
// Prisma uses different approaches:
// - JSON columns with native support
// - Enums mapped to database enums
// - Custom scalar types with middleware

Timezone Handling is Critical

Datetime handling is the source of countless bugs. Best practice: (1) Store all timestamps as UTC in TIMESTAMPTZ columns, (2) Convert to user's timezone only at display time, (3) Be explicit about timezone in all date operations, (4) Test with users in multiple timezones. Never store local times without timezone information.

Advanced Mapping Patterns

Beyond basic mappings, sophisticated ORM usage requires understanding advanced patterns for handling special cases like soft deletes, audit trails, multi-tenancy, and polymorphic associations.

Soft Delete Mapping

•Pattern: Instead of DELETE, set a deletedAt timestamp or isDeleted flag. All queries automatically filter out 'deleted' records.
•Implementation: ORM middleware/filters that inject WHERE deletedAt IS NULL into all queries.
•Considerations: Unique constraints need adjustment, cascade behavior changes, and you need explicit queries to access deleted records.

advanced-mapping-patterns.ts
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
// ==========================================
// SOFT DELETE PATTERN
// ==========================================
 
@Entity('users')
class User {
    @PrimaryColumn()
    id: string;
    
    @Column()
    email: string;
    
    @Column({ nullable: true })
    deletedAt: Date | null;  // Soft delete marker
    
    // Unique constraint must include deletedAt
    // CREATE UNIQUE INDEX users_email_unique 
    // ON users(email) WHERE deleted_at IS NULL;
}
 
// Repository automatically filters deleted records
@Injectable()
class UserRepository {
    findAll(): Promise<User[]> {
        return this.repo.find({
            where: { deletedAt: IsNull() }  // Always exclude deleted
        });
    }
    
    softDelete(user: User): Promise<void> {
        user.deletedAt = new Date();
        return this.repo.save(user);
    }
    
    // Explicit method to include deleted
    findAllIncludingDeleted(): Promise<User[]> {
        return this.repo.find({});  // No filter
    }
}
 
// ==========================================
// AUDIT TRAIL PATTERN  
// ==========================================
 
// Base class with audit fields
@Entity()
abstract class AuditedEntity {
    @Column()
    createdAt: Date;
    
    @Column()
    createdBy: string;
    
    @Column()
    updatedAt: Date;
    
    @Column()
    updatedBy: string;
    
    @Column({ nullable: true })
    deletedAt: Date | null;
    
    @Column({ nullable: true })
    deletedBy: string | null;
}
 
// Listener to automatically populate audit fields
@EventSubscriber()
class AuditSubscriber {
    beforeInsert(event: InsertEvent<AuditedEntity>) {
        const currentUser = getCurrentUser();
        event.entity.createdAt = new Date();
        event.entity.createdBy = currentUser.id;
        event.entity.updatedAt = new Date();
        event.entity.updatedBy = currentUser.id;
    }
    
    beforeUpdate(event: UpdateEvent<AuditedEntity>) {
        const currentUser = getCurrentUser();
        event.entity.updatedAt = new Date();
        event.entity.updatedBy = currentUser.id;
    }
}
 
// ==========================================
// MULTI-TENANCY MAPPING
// ==========================================
 
// Discriminator column approach
@Entity('products')
class Product {
    @PrimaryColumn()
    id: string;
    
    @Column()
    tenantId: string;  // Every row belongs to a tenant
    
    @Column()
    name: string;
}
 
// Repository scoped to tenant
class TenantScopedProductRepository {
    constructor(private readonly tenantId: string) {}
    
    findAll(): Promise<Product[]> {
        return this.repo.find({
            where: { tenantId: this.tenantId }  // Always filter by tenant
        });
    }
    
    create(data: ProductData): Promise<Product> {
        return this.repo.save({
            ...data,
            tenantId: this.tenantId  // Always set tenant
        });
    }
}
 
// ==========================================
// POLYMORPHIC ASSOCIATIONS
// ==========================================
 
// Comments that can belong to different entity types
@Entity('comments')
class Comment {
    @PrimaryColumn()
    id: string;
    
    @Column()
    commentableType: string;  // 'post' | 'photo' | 'video'
    
    @Column()
    commentableId: string;  // ID of the related entity
    
    @Column()
    content: string;
}
 
// No foreign key constraint (loses referential integrity)
// Must handle consistency at application level

ORM Limitations with Advanced Patterns

Some patterns push against ORM limitations. Polymorphic associations often lack full ORM support. Multi-tenancy may need raw SQL for schema-per-tenant models. Audit trails with full history often use separate tables or event sourcing. Know when ORM features are insufficient and design accordingly.

Choosing Mapping Strategies

Mapping strategy choices have long-term consequences for schema evolution, query performance, and code complexity. Consider these guidelines when making decisions:

Decision Framework

•For Inheritance: Start with Single Table for simple hierarchies (2-3 subtypes with similar fields). Move to Joined Table if subtypes diverge significantly or need their own constraints. Use Table Per Class rarely, mainly when subtypes are completely independent.
•For Relationships: One-to-one mappings default to separate tables unless the related entity is always needed. One-to-many is straightforward—FK on the many side. Many-to-many starts with a simple junction; promote to rich entity when it needs its own attributes.
•For Value Objects: Embed small, always-needed value objects (Money, Coordinates). Use JSON for complex nested structures. Create separate entities only when independent querying is required.
•For Types: Match database types to application semantics precisely. Use DECIMAL for money, TIMESTAMPTZ for times, native enums for constrained choices. Custom converters bridge remaining gaps.

Quick Reference: Mapping Decisions
Scenario	Recommended Strategy	Key Reason
Few subtypes, similar fields	Single Table Inheritance	Query simplicity
Many subtypes, unique fields	Joined Table Inheritance	Normalized, type-safe
Independent entity types	Table Per Class	Complete separation
Optional related entity	Separate table, nullable FK	Flexibility
Mandatory 1:1 composition	Embedded or shared PK	Coupling clarity
Standard M:N relationship	Junction table	Relational standard
M:N with metadata	Rich junction entity	Queryability
Immutable value object	Embedded columns	Co-location
Complex, rarely queried data	JSON column	Flexibility

Schema Evolution Matters

Consider how your schema will evolve. Single Table Inheritance is easy to extend with new subtypes (just add columns) but hard to split later. Separate tables are harder to set up initially but evolve more independently. Choose strategies that match your expected evolution path.

Summary: Mapping Strategies

We've explored the core mapping strategies that translate between object-oriented constructs and relational database structures. Let's consolidate the key insights:

Key Takeaways

•Inheritance mapping has three main strategies — Single Table (one table, discriminator), Table Per Class (concrete tables only), and Joined Table (table per class with joins). Each optimizes for different concerns.
•Relationships map through foreign keys — One-to-one uses FK or shared PK, one-to-many uses FK on many side, many-to-many uses junction tables. Rich junctions become entities.
•Value objects embed into owning entities — Embedded mapping stores value object properties as columns in the parent table, avoiding separate tables for non-identity objects.
•Type conversion bridges type systems — Standard conversions handle common types; custom transformers handle encryption, serialization, and domain-specific conversions.
•Advanced patterns address cross-cutting concerns — Soft deletes, audit trails, and multi-tenancy require thoughtful mapping strategies, often with ORM extensions or middleware.
•Strategy choice affects evolution — Consider how schemas will change; choose strategies that align with expected evolution patterns.

What's Next:

Mapping strategies define how data is structured; now we need to examine how data access performs. The next page explores performance considerations—understanding ORM overhead, detecting and preventing N+1 queries, optimizing query patterns, and knowing when to bypass ORM for direct database access.

Page Complete

You now understand how ORMs map objects to tables: inheritance strategies, relationship mappings, value object embedding, type conversions, and advanced patterns. This knowledge enables you to design effective database schemas and ORM configurations. Next, we'll focus on performance optimization for ORM-based systems.

3 / 4

Loading learning content...

System Design (LLD)ORM Considerations

Object-Relational Mapping Considerations

LevelIntermediate

Duration75 mins

TopicORM Considerations

3 / 4

Mapping Strategies

Bridging Two Worlds

This page explores the major mapping strategies employed by ORM frameworks, giving you the knowledge to make informed decisions about how your objects become tables.

What You Will Learn

Inheritance Mapping Strategies

Three primary strategies exist, each with distinct trade-offs:

Single Table Inheritance (STI)

•Concept: All classes in the hierarchy share a single database table. A discriminator column identifies the concrete type of each row.
•Columns: The table contains columns for properties of ALL classes in the hierarchy. Rows only populate columns relevant to their type.
•Pros: Fast queries (no joins), simple schema, easy to add new subtypes
•Cons: Many nullable columns, wasted space, no database-level constraints on subtype-specific columns
•Best for: Shallow hierarchies with similar subtypes and few subtype-specific fields

single-table-inheritance.ts
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
// ==========================================
// SINGLE TABLE INHERITANCE (STI)
// ==========================================
 
// Object Model: Inheritance hierarchy
abstract class Payment {
    id: string;
    amount: number;
    currency: string;
    createdAt: Date;
}
 
class CreditCardPayment extends Payment {
    cardLast4: string;
    expiryMonth: number;
    expiryYear: number;
}
 
class BankTransferPayment extends Payment {
    bankName: string;
    accountLast4: string;
    routingNumber: string;
}
 
class CryptoPayment extends Payment {
    walletAddress: string;
    network: string;
    txHash: string;
}
 
// Database Schema: Single table with discriminator
// CREATE TABLE payments (
//     id UUID PRIMARY KEY,
//     payment_type VARCHAR(50) NOT NULL,  -- Discriminator column
//     amount DECIMAL(10,2) NOT NULL,
//     currency VARCHAR(3) NOT NULL,
//     created_at TIMESTAMP NOT NULL,
//     
//     -- CreditCardPayment fields (nullable for other types)
//     card_last4 VARCHAR(4),
//     expiry_month INTEGER,
//     expiry_year INTEGER,
//     
//     -- BankTransferPayment fields (nullable for other types)
//     bank_name VARCHAR(100),
//     account_last4 VARCHAR(4),
//     routing_number VARCHAR(20),
//     
//     -- CryptoPayment fields (nullable for other types)
//     wallet_address VARCHAR(100),
//     network VARCHAR(20),
//     tx_hash VARCHAR(100)
// );
 
// Example data:
// | id  | payment_type  | amount | card_last4 | bank_name | wallet_address |
// |-----|---------------|--------|------------|-----------|----------------|
// | a1  | credit_card   | 100.00 | 4242       | NULL      | NULL           |
// | a2  | bank_transfer | 500.00 | NULL       | Chase     | NULL           |
// | a3  | crypto        | 250.00 | NULL       | NULL      | 0x1234...      |
 
// ORM Mapping (TypeORM-style)
@Entity('payments')
@TableInheritance({ column: { type: 'varchar', name: 'payment_type' } })
abstract class Payment {
    @PrimaryColumn()
    id: string;
    
    @Column('decimal')
    amount: number;
    
    @Column()
    currency: string;
}
 
@ChildEntity('credit_card')
class CreditCardPayment extends Payment {
    @Column({ nullable: true })
    cardLast4: string;
}
 
// Queries are efficient - no joins needed:
// SELECT * FROM payments WHERE payment_type = 'credit_card'

Table Per Class (Concrete Table Inheritance)

•Concept: Each concrete class gets its own table. Abstract base classes have no table.
•Columns: Each table contains columns for all properties—both inherited and class-specific.
•Pros: No nullable columns, efficient queries for single type, natural table structure
•Cons: Duplicate columns across tables, polymorphic queries require UNION, hard to enforce unique constraints across hierarchy
•Best for: Distinct subtypes with minimal polymorphic access needs

table-per-class.ts
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
// ==========================================
// TABLE PER CLASS (CONCRETE TABLE INHERITANCE)
// ==========================================
 
// Same object model as before, but different schema:
 
// CREATE TABLE credit_card_payments (
//     id UUID PRIMARY KEY,
//     amount DECIMAL(10,2) NOT NULL,
//     currency VARCHAR(3) NOT NULL,
//     created_at TIMESTAMP NOT NULL,
//     card_last4 VARCHAR(4) NOT NULL,
//     expiry_month INTEGER NOT NULL,
//     expiry_year INTEGER NOT NULL
// );
 
// CREATE TABLE bank_transfer_payments (
//     id UUID PRIMARY KEY,
//     amount DECIMAL(10,2) NOT NULL,       -- Duplicated from base
//     currency VARCHAR(3) NOT NULL,         -- Duplicated from base
//     created_at TIMESTAMP NOT NULL,        -- Duplicated from base
//     bank_name VARCHAR(100) NOT NULL,
//     account_last4 VARCHAR(4) NOT NULL,
//     routing_number VARCHAR(20) NOT NULL
// );
 
// CREATE TABLE crypto_payments (
//     id UUID PRIMARY KEY,
//     amount DECIMAL(10,2) NOT NULL,       -- Duplicated from base
//     currency VARCHAR(3) NOT NULL,         -- Duplicated from base
//     created_at TIMESTAMP NOT NULL,        -- Duplicated from base
//     wallet_address VARCHAR(100) NOT NULL,
//     network VARCHAR(20) NOT NULL,
//     tx_hash VARCHAR(100) NOT NULL
// );
 
// Single-type queries are efficient:
// SELECT * FROM credit_card_payments WHERE amount > 100
 
// Polymorphic queries require UNION (expensive):
// SELECT id, amount, 'credit_card' as type FROM credit_card_payments
// UNION ALL
// SELECT id, amount, 'bank_transfer' as type FROM bank_transfer_payments
// UNION ALL
// SELECT id, amount, 'crypto' as type FROM crypto_payments
// ORDER BY amount DESC
 
// ORM mapping typically uses @TableInheritance({ type: 'CONCRETE' })
// or equivalent annotation

Joined Table Inheritance (Class Table Inheritance)

•Concept: Each class (including abstract) gets its own table. Subclass tables join to superclass tables.
•Columns: Base properties in base table, subclass properties in subclass table. Connected by foreign key.
•Pros: Normalized schema, no nulls, database constraints work naturally, schema reflects hierarchy
•Cons: Queries require joins, insert/update spans multiple tables, more complex schema
•Best for: Deep hierarchies, normalized database design preferences, subtype-specific constraints needed

joined-table-inheritance.ts
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
// ==========================================
// JOINED TABLE INHERITANCE (CLASS TABLE)
// ==========================================
 
// CREATE TABLE payments (
//     id UUID PRIMARY KEY,
//     payment_type VARCHAR(50) NOT NULL,  -- Discriminator still useful
//     amount DECIMAL(10,2) NOT NULL,
//     currency VARCHAR(3) NOT NULL,
//     created_at TIMESTAMP NOT NULL
// );
 
// CREATE TABLE credit_card_payments (
//     id UUID PRIMARY KEY REFERENCES payments(id),
//     card_last4 VARCHAR(4) NOT NULL,
//     expiry_month INTEGER NOT NULL,
//     expiry_year INTEGER NOT NULL
// );
 
// CREATE TABLE bank_transfer_payments (
//     id UUID PRIMARY KEY REFERENCES payments(id),
//     bank_name VARCHAR(100) NOT NULL,
//     account_last4 VARCHAR(4) NOT NULL,
//     routing_number VARCHAR(20) NOT NULL
// );
 
// CREATE TABLE crypto_payments (
//     id UUID PRIMARY KEY REFERENCES payments(id),
//     wallet_address VARCHAR(100) NOT NULL,
//     network VARCHAR(20) NOT NULL,
//     tx_hash VARCHAR(100) NOT NULL
// );
 
// Loading a CreditCardPayment requires a join:
// SELECT p.*, cc.*
// FROM payments p
// JOIN credit_card_payments cc ON p.id = cc.id
// WHERE p.id = 'some-id'
 
// Polymorphic query also requires joins but cleaner than UNION:
// SELECT p.id, p.amount, p.payment_type
// FROM payments p
// LEFT JOIN credit_card_payments cc ON p.id = cc.id
// LEFT JOIN bank_transfer_payments bt ON p.id = bt.id
// LEFT JOIN crypto_payments cp ON p.id = cp.id
// WHERE p.amount > 100
 
// ORM Mapping (TypeORM-style)
@Entity('payments')
@TableInheritance({ type: 'JOINED' })
abstract class Payment {
    @PrimaryColumn()
    id: string;
    
    @Column('decimal')
    amount: number;
}
 
@ChildEntity()
@Entity('credit_card_payments')
class CreditCardPayment extends Payment {
    @Column()
    cardLast4: string;
}

Inheritance Strategy Comparison
Strategy	Query Performance	Storage Efficiency	Schema Complexity	Polymorphic Queries
Single Table	Excellent (no joins)	Poor (many nulls)	Simple	Excellent
Table Per Class	Excellent (type-specific)	Good (no nulls)	Medium	Poor (UNION)
Joined Table	Moderate (joins needed)	Excellent (normalized)	Complex	Moderate

Relationship Mapping Strategies

Objects reference each other through memory pointers. Databases connect rows through foreign keys. Mapping relationships requires translating between these fundamentally different mechanisms.

The cardinality and ownership semantics of relationships determine the mapping strategy:

One-to-One Relationships

•Same Table: Embed the related entity's columns directly in the owning entity's table. Best for tightly coupled entities always accessed together.
•Separate Tables with FK: Each entity has its own table; a foreign key column links them. Allows independent loading and optional relationships.
•Shared Primary Key: Both tables use the same primary key value. The child table's PK is also the FK to the parent. Strong coupling but elegant.

one-to-one-mapping.ts
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
// ==========================================
// ONE-TO-ONE: Three Mapping Approaches
// ==========================================
 
// Object Model
class User {
    id: string;
    email: string;
    profile: UserProfile;  // One-to-one
}
 
class UserProfile {
    bio: string;
    avatarUrl: string;
    timezone: string;
}
 
// -----------------------------------------
// APPROACH 1: Same Table (Embedding)
// -----------------------------------------
 
// CREATE TABLE users (
//     id UUID PRIMARY KEY,
//     email VARCHAR(255) NOT NULL,
//     -- Profile fields embedded directly
//     profile_bio TEXT,
//     profile_avatar_url VARCHAR(500),
//     profile_timezone VARCHAR(50)
// );
 
// Prisma schema equivalent:
// model User {
//   id             String  @id @default(cuid())
//   email          String  @unique
//   profileBio     String?
//   profileAvatar  String?
//   profileTimezone String?
// }
 
// Pro: No joins, atomic operations
// Con: Profile not independently queryable, all fields nullable
 
// -----------------------------------------
// APPROACH 2: Separate Tables with FK
// -----------------------------------------
 
// CREATE TABLE users (
//     id UUID PRIMARY KEY,
//     email VARCHAR(255) NOT NULL
// );
 
// CREATE TABLE user_profiles (
//     id UUID PRIMARY KEY,
//     user_id UUID NOT NULL REFERENCES users(id) UNIQUE,
//     bio TEXT,
//     avatar_url VARCHAR(500),
//     timezone VARCHAR(50)
// );
 
// ORM Mapping (TypeORM-style)
@Entity('users')
class User {
    @PrimaryColumn()
    id: string;
    
    @Column()
    email: string;
    
    @OneToOne(() => UserProfile, profile => profile.user)
    profile: UserProfile;
}
 
@Entity('user_profiles')
class UserProfile {
    @PrimaryColumn()
    id: string;
    
    @Column()
    userId: string;
    
    @OneToOne(() => User, user => user.profile)
    @JoinColumn({ name: 'user_id' })
    user: User;
    
    @Column()
    bio: string;
}
 
// -----------------------------------------
// APPROACH 3: Shared Primary Key
// -----------------------------------------
 
// CREATE TABLE users (
//     id UUID PRIMARY KEY,
//     email VARCHAR(255) NOT NULL
// );
 
// CREATE TABLE user_profiles (
//     user_id UUID PRIMARY KEY REFERENCES users(id),  -- PK = FK
//     bio TEXT,
//     avatar_url VARCHAR(500),
//     timezone VARCHAR(50)
// );
 
// Pro: Elegant, enforces 1:1 at database level
// Con: Profile cannot exist without user

One-to-Many Relationships

•Standard FK Approach: The 'many' side holds a foreign key to the 'one' side. This is the dominant approach.
•Ownership Considerations: The FK always lives on the child/many side. From ORM perspective, you configure whether the parent 'owns' the relationship (cascade operations).
•Bidirectional Navigation: ORMs can provide navigation both ways: parent.children and child.parent. But the database only stores the FK on one side.

one-to-many-mapping.ts
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
// ==========================================
// ONE-TO-MANY: Author has many Books
// ==========================================
 
// Object Model
class Author {
    id: string;
    name: string;
    books: Book[];  // One-to-many
}
 
class Book {
    id: string;
    title: string;
    author: Author;  // Many-to-one (inverse)
}
 
// Database Schema
// CREATE TABLE authors (
//     id UUID PRIMARY KEY,
//     name VARCHAR(100) NOT NULL
// );
 
// CREATE TABLE books (
//     id UUID PRIMARY KEY,
//     title VARCHAR(255) NOT NULL,
//     author_id UUID NOT NULL REFERENCES authors(id)  -- FK on many side
// );
 
// ORM Mapping (TypeORM-style)
@Entity('authors')
class Author {
    @PrimaryColumn()
    id: string;
    
    @Column()
    name: string;
    
    @OneToMany(() => Book, book => book.author, {
        cascade: true,          // Operations propagate to children
        orphanRemoval: true,    // Delete children when removed from collection
    })
    books: Book[];
}
 
@Entity('books')
class Book {
    @PrimaryColumn()
    id: string;
    
    @Column()
    title: string;
    
    @Column()
    authorId: string;  // FK column explicitly mapped
    
    @ManyToOne(() => Author, author => author.books)
    @JoinColumn({ name: 'author_id' })
    author: Author;
}
 
// Loading with eager/lazy strategies
// Eager: Include in initial query
const author = await authorRepo.findOne({
    where: { id: authorId },
    relations: ['books']  // JOIN books in same query
});
 
// Lazy: Load on access (if ORM supports)
const author = await authorRepo.findOne({ where: { id: authorId } });
const books = await author.books;  // Triggers SELECT at access time

Many-to-Many Relationships

•Junction Table: M:N relationships require an intermediate table (also called join table, link table, or association table) with FKs to both sides.
•Simple Junction: Just two FK columns, representing pure relationship without additional data.
•Rich Junction: Junction table includes additional columns (e.g., enrollment date for student-course relationship). May become its own entity.
•Bidirectional Mapping: Both sides can navigate to each other, but the junction table is the source of truth.

many-to-many-mapping.ts
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
// ==========================================
// MANY-TO-MANY: Students enroll in Courses
// ==========================================
 
// Object Model
class Student {
    id: string;
    name: string;
    courses: Course[];  // Many-to-many
}
 
class Course {
    id: string;
    title: string;
    students: Student[];  // Many-to-many (inverse)
}
 
// -----------------------------------------
// SIMPLE JUNCTION TABLE
// -----------------------------------------
 
// CREATE TABLE students (
//     id UUID PRIMARY KEY,
//     name VARCHAR(100) NOT NULL
// );
 
// CREATE TABLE courses (
//     id UUID PRIMARY KEY,
//     title VARCHAR(255) NOT NULL
// );
 
// CREATE TABLE student_courses (  -- Junction table
//     student_id UUID REFERENCES students(id),
//     course_id UUID REFERENCES courses(id),
//     PRIMARY KEY (student_id, course_id)
// );
 
// ORM Mapping (TypeORM-style)
@Entity('students')
class Student {
    @PrimaryColumn()
    id: string;
    
    @ManyToMany(() => Course, course => course.students)
    @JoinTable({
        name: 'student_courses',
        joinColumn: { name: 'student_id' },
        inverseJoinColumn: { name: 'course_id' }
    })
    courses: Course[];
}
 
@Entity('courses')
class Course {
    @PrimaryColumn()
    id: string;
    
    @ManyToMany(() => Student, student => student.courses)
    students: Student[];  // No @JoinTable - inverse side
}
 
// -----------------------------------------
// RICH JUNCTION TABLE (with extra data)
// -----------------------------------------
 
// CREATE TABLE enrollments (
//     id UUID PRIMARY KEY,
//     student_id UUID NOT NULL REFERENCES students(id),
//     course_id UUID NOT NULL REFERENCES courses(id),
//     enrolled_at TIMESTAMP NOT NULL,
//     grade VARCHAR(2),
//     status VARCHAR(20) NOT NULL,
//     UNIQUE(student_id, course_id)
// );
 
// When junction has its own data, model it as an entity:
@Entity('enrollments')
class Enrollment {
    @PrimaryColumn()
    id: string;
    
    @ManyToOne(() => Student)
    @JoinColumn({ name: 'student_id' })
    student: Student;
    
    @ManyToOne(() => Course)
    @JoinColumn({ name: 'course_id' })
    course: Course;
    
    @Column()
    enrolledAt: Date;
    
    @Column({ nullable: true })
    grade: string;
    
    @Column()
    status: string;
}
 
// Now navigate through Enrollment entity:
class Student {
    @OneToMany(() => Enrollment, e => e.student)
    enrollments: Enrollment[];
}

When to Use Rich Junction Tables

Value Type and Embedded Object Mapping

embedded-mapping.ts
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
// ==========================================
// EMBEDDED VALUE OBJECTS
// ==========================================
 
// Value Objects (no identity, compared by value)
class Money {
    constructor(
        public readonly amount: number,
        public readonly currency: string
    ) {}
    
    add(other: Money): Money {
        if (this.currency !== other.currency) {
            throw new Error('Currency mismatch');
        }
        return new Money(this.amount + other.amount, this.currency);
    }
    
    equals(other: Money): boolean {
        return this.amount === other.amount && 
               this.currency === other.currency;
    }
}
 
class Address {
    constructor(
        public readonly street: string,
        public readonly city: string,
        public readonly state: string,
        public readonly postalCode: string,
        public readonly country: string
    ) {}
}
 
// Entity containing embedded value objects
class Order {
    id: string;
    customerId: string;
    shippingAddress: Address;  // Embedded
    billingAddress: Address;   // Embedded
    totalAmount: Money;        // Embedded
}
 
// Database Schema - value objects become columns
// CREATE TABLE orders (
//     id UUID PRIMARY KEY,
//     customer_id UUID NOT NULL,
//     
//     -- Shipping Address (embedded with prefix)
//     shipping_street VARCHAR(255),
//     shipping_city VARCHAR(100),
//     shipping_state VARCHAR(50),
//     shipping_postal_code VARCHAR(20),
//     shipping_country VARCHAR(50),
//     
//     -- Billing Address (embedded with prefix)
//     billing_street VARCHAR(255),
//     billing_city VARCHAR(100),
//     billing_state VARCHAR(50),
//     billing_postal_code VARCHAR(20),
//     billing_country VARCHAR(50),
//     
//     -- Money (embedded)
//     total_amount DECIMAL(10,2),
//     total_currency VARCHAR(3)
// );
 
// ORM Mapping (TypeORM-style)
@Entity('orders')
class Order {
    @PrimaryColumn()
    id: string;
    
    @Column()
    customerId: string;
    
    @Embedded(() => Address, { prefix: 'shipping_' })
    shippingAddress: Address;
    
    @Embedded(() => Address, { prefix: 'billing_' })
    billingAddress: Address;
    
    @Embedded(() => Money, { prefix: 'total_' })
    totalAmount: Money;
}
 
@Embeddable()
class Address {
    @Column()
    street: string;
    
    @Column()
    city: string;
    
    @Column()
    state: string;
    
    @Column()
    postalCode: string;
    
    @Column()
    country: string;
}
 
@Embeddable()
class Money {
    @Column('decimal')
    amount: number;
    
    @Column()
    currency: string;
}
 
// Benefits of Embedding:
// 1. No joins needed - data co-located
// 2. Atomic updates - single row operation
// 3. Clear value semantics - not an independent entity
// 4. Column prefixes prevent naming collisions

When to Embed vs. When to Reference

•Embed when: The value has no independent existence, is always accessed with the parent, has no identity of its own, and the total column count remains reasonable.
•Reference when: The value needs independent querying, has its own lifecycle, is shared across entities, or the embedding would create too many columns.
•Consider JSON: For complex nested structures rarely queried individually, JSON/JSONB columns offer flexibility without column explosion.

Prisma and Embedded Types

Column Type Mapping and Conversions

Programming languages and databases have different type systems. ORMs must translate between them, handling edge cases like:

Dates and times: JavaScript Date vs. SQL TIMESTAMP vs. TIMESTAMPTZ
Numbers: Integer overflow, decimal precision, BigInt handling
Booleans: True/false vs. 0/1 vs. 'Y'/'N'
Enums: Language enums vs. database enum types vs. string columns
JSON: Native JSON types vs. serialized strings

Understanding these conversions prevents subtle bugs that manifest as incorrect data or runtime errors.

Common Type Mapping Considerations
Application Type	PostgreSQL Type	Key Considerations
string	VARCHAR(n) / TEXT	Length limits, encoding (UTF-8), collation
number (integer)	INTEGER / BIGINT	Overflow at ~2B (INT) or ~9 quintillion (BIGINT)
number (decimal)	DECIMAL(p,s) / NUMERIC	Precision and scale; avoid FLOAT for money
boolean	BOOLEAN	NULL handling (three-state logic)
Date	TIMESTAMP / TIMESTAMPTZ	Timezone handling critical; prefer TIMESTAMPTZ
enum	ENUM type / VARCHAR	ENUM type safer but less flexible to modify
array	ARRAY / JSON	PostgreSQL arrays or JSON array; query patterns differ
object	JSONB	Schema-less but indexed; balance flexibility vs. structure
UUID	UUID	Native UUID type; generation strategy matters
BigInt	BIGINT	JavaScript BigInt requires serialization consideration

type-converters.ts
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
// ==========================================
// CUSTOM TYPE CONVERTERS
// ==========================================
 
// When standard conversions aren't enough, ORMs allow custom converters
 
// Example: Encrypting sensitive data before storage
class EncryptedStringTransformer {
    to(value: string): string {
        // Encrypt before writing to database
        return encrypt(value, process.env.ENCRYPTION_KEY);
    }
    
    from(value: string): string {
        // Decrypt after reading from database
        return decrypt(value, process.env.ENCRYPTION_KEY);
    }
}
 
@Entity('users')
class User {
    @Column({
        transformer: new EncryptedStringTransformer()
    })
    ssn: string;  // Stored encrypted, used decrypted
}
 
// Example: Storing Money as integer cents
class MoneyCentsTransformer {
    to(money: Money): number {
        // Store as integer cents (no floating point issues)
        return Math.round(money.amount * 100);
    }
    
    from(cents: number): Money {
        // Reconstruct Money object
        return new Money(cents / 100, 'USD');
    }
}
 
@Entity('products')
class Product {
    @Column({
        type: 'integer',
        transformer: new MoneyCentsTransformer()
    })
    price: Money;  // Stored as cents, used as Money
}
 
// Example: JSON serialization for complex objects
class JsonTransformer<T> {
    to(value: T): string {
        return JSON.stringify(value);
    }
    
    from(value: string): T {
        return JSON.parse(value) as T;
    }
}
 
interface OrderMetadata {
    source: string;
    affiliateId?: string;
    utmParameters: Record<string, string>;
}
 
@Entity('orders')
class Order {
    @Column({
        type: 'text',
        transformer: new JsonTransformer<OrderMetadata>()
    })
    metadata: OrderMetadata;
}
 
// Prisma uses different approaches:
// - JSON columns with native support
// - Enums mapped to database enums
// - Custom scalar types with middleware

Timezone Handling is Critical

Advanced Mapping Patterns

Beyond basic mappings, sophisticated ORM usage requires understanding advanced patterns for handling special cases like soft deletes, audit trails, multi-tenancy, and polymorphic associations.

Soft Delete Mapping

•Pattern: Instead of DELETE, set a deletedAt timestamp or isDeleted flag. All queries automatically filter out 'deleted' records.
•Implementation: ORM middleware/filters that inject WHERE deletedAt IS NULL into all queries.
•Considerations: Unique constraints need adjustment, cascade behavior changes, and you need explicit queries to access deleted records.

advanced-mapping-patterns.ts
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
// ==========================================
// SOFT DELETE PATTERN
// ==========================================
 
@Entity('users')
class User {
    @PrimaryColumn()
    id: string;
    
    @Column()
    email: string;
    
    @Column({ nullable: true })
    deletedAt: Date | null;  // Soft delete marker
    
    // Unique constraint must include deletedAt
    // CREATE UNIQUE INDEX users_email_unique 
    // ON users(email) WHERE deleted_at IS NULL;
}
 
// Repository automatically filters deleted records
@Injectable()
class UserRepository {
    findAll(): Promise<User[]> {
        return this.repo.find({
            where: { deletedAt: IsNull() }  // Always exclude deleted
        });
    }
    
    softDelete(user: User): Promise<void> {
        user.deletedAt = new Date();
        return this.repo.save(user);
    }
    
    // Explicit method to include deleted
    findAllIncludingDeleted(): Promise<User[]> {
        return this.repo.find({});  // No filter
    }
}
 
// ==========================================
// AUDIT TRAIL PATTERN  
// ==========================================
 
// Base class with audit fields
@Entity()
abstract class AuditedEntity {
    @Column()
    createdAt: Date;
    
    @Column()
    createdBy: string;
    
    @Column()
    updatedAt: Date;
    
    @Column()
    updatedBy: string;
    
    @Column({ nullable: true })
    deletedAt: Date | null;
    
    @Column({ nullable: true })
    deletedBy: string | null;
}
 
// Listener to automatically populate audit fields
@EventSubscriber()
class AuditSubscriber {
    beforeInsert(event: InsertEvent<AuditedEntity>) {
        const currentUser = getCurrentUser();
        event.entity.createdAt = new Date();
        event.entity.createdBy = currentUser.id;
        event.entity.updatedAt = new Date();
        event.entity.updatedBy = currentUser.id;
    }
    
    beforeUpdate(event: UpdateEvent<AuditedEntity>) {
        const currentUser = getCurrentUser();
        event.entity.updatedAt = new Date();
        event.entity.updatedBy = currentUser.id;
    }
}
 
// ==========================================
// MULTI-TENANCY MAPPING
// ==========================================
 
// Discriminator column approach
@Entity('products')
class Product {
    @PrimaryColumn()
    id: string;
    
    @Column()
    tenantId: string;  // Every row belongs to a tenant
    
    @Column()
    name: string;
}
 
// Repository scoped to tenant
class TenantScopedProductRepository {
    constructor(private readonly tenantId: string) {}
    
    findAll(): Promise<Product[]> {
        return this.repo.find({
            where: { tenantId: this.tenantId }  // Always filter by tenant
        });
    }
    
    create(data: ProductData): Promise<Product> {
        return this.repo.save({
            ...data,
            tenantId: this.tenantId  // Always set tenant
        });
    }
}
 
// ==========================================
// POLYMORPHIC ASSOCIATIONS
// ==========================================
 
// Comments that can belong to different entity types
@Entity('comments')
class Comment {
    @PrimaryColumn()
    id: string;
    
    @Column()
    commentableType: string;  // 'post' | 'photo' | 'video'
    
    @Column()
    commentableId: string;  // ID of the related entity
    
    @Column()
    content: string;
}
 
// No foreign key constraint (loses referential integrity)
// Must handle consistency at application level

ORM Limitations with Advanced Patterns

Choosing Mapping Strategies

Mapping strategy choices have long-term consequences for schema evolution, query performance, and code complexity. Consider these guidelines when making decisions:

Decision Framework

•For Inheritance: Start with Single Table for simple hierarchies (2-3 subtypes with similar fields). Move to Joined Table if subtypes diverge significantly or need their own constraints. Use Table Per Class rarely, mainly when subtypes are completely independent.
•For Relationships: One-to-one mappings default to separate tables unless the related entity is always needed. One-to-many is straightforward—FK on the many side. Many-to-many starts with a simple junction; promote to rich entity when it needs its own attributes.
•For Value Objects: Embed small, always-needed value objects (Money, Coordinates). Use JSON for complex nested structures. Create separate entities only when independent querying is required.
•For Types: Match database types to application semantics precisely. Use DECIMAL for money, TIMESTAMPTZ for times, native enums for constrained choices. Custom converters bridge remaining gaps.

Quick Reference: Mapping Decisions
Scenario	Recommended Strategy	Key Reason
Few subtypes, similar fields	Single Table Inheritance	Query simplicity
Many subtypes, unique fields	Joined Table Inheritance	Normalized, type-safe
Independent entity types	Table Per Class	Complete separation
Optional related entity	Separate table, nullable FK	Flexibility
Mandatory 1:1 composition	Embedded or shared PK	Coupling clarity
Standard M:N relationship	Junction table	Relational standard
M:N with metadata	Rich junction entity	Queryability
Immutable value object	Embedded columns	Co-location
Complex, rarely queried data	JSON column	Flexibility

Schema Evolution Matters

Summary: Mapping Strategies

We've explored the core mapping strategies that translate between object-oriented constructs and relational database structures. Let's consolidate the key insights:

Key Takeaways

•Inheritance mapping has three main strategies — Single Table (one table, discriminator), Table Per Class (concrete tables only), and Joined Table (table per class with joins). Each optimizes for different concerns.
•Relationships map through foreign keys — One-to-one uses FK or shared PK, one-to-many uses FK on many side, many-to-many uses junction tables. Rich junctions become entities.
•Value objects embed into owning entities — Embedded mapping stores value object properties as columns in the parent table, avoiding separate tables for non-identity objects.
•Type conversion bridges type systems — Standard conversions handle common types; custom transformers handle encryption, serialization, and domain-specific conversions.
•Advanced patterns address cross-cutting concerns — Soft deletes, audit trails, and multi-tenancy require thoughtful mapping strategies, often with ORM extensions or middleware.
•Strategy choice affects evolution — Consider how schemas will change; choose strategies that align with expected evolution patterns.

What's Next:

Page Complete

3 / 4