Database Relation Design (1:N vs M:N)

Why This Matters

Choosing the wrong relationship type doesn’t just make your ERD look messy — it directly impacts data integrity, query performance, and long-term maintainability. A relationship that should be M:N but is modeled as 1:N will eventually require a painful migration once the business discovers the limitation. Getting it right from the start saves everyone a headache.

The Reverse Check Rule

The most reliable way to choose between One-to-Many (1:N) and Many-to-Many (M:N) is to ask two directional questions about the relationship:

Direction	Question	Example
Forward (A → B)	Can one A have many B’s?	Can one generic drug have many substitutions?
Reverse (B → A)	Can one B belong to many A’s?	Can substitution “Sara 500mg” belong to a generic drug other than Paracetamol?

The reverse question is the deciding factor:

• Reverse = No → The child is exclusively owned by one parent → 1:N
• Reverse = Yes → The record is a shared resource used across many parents → M:N

One-to-Many (1:N) — The “Owner–Child” Pattern

• Forward → Yes (one parent owns many children)
• Reverse → No (each child belongs to exactly one parent)
• Physical shape: Table B (child) has a Foreign Key pointing to Table A (parent)

Many-to-Many (M:N) — The “Shared Resource” Pattern

• Forward → Yes (one A relates to many B’s)
• Reverse → Yes (one B also relates to many A’s)
• Physical shape: A Join Table is always required to map the pairs

---

Decision Flowchart

When in doubt, walk through this flowchart. It covers the edge cases that the simple two-question rule doesn’t — specifically the “snapshot vs. master data” distinction.

flowchart TD
    Start(["Need to create a relationship\nbetween Table A and Table B"]) --> Q1

    Q1{"Can one A\nhave many B's?"}
    Q1 -->|No| OneToOne["1:1 Relationship\n@OneToOne\nor merge columns\ninto one table"]
    Q1 -->|Yes| Q2

    Q2{"Can one B\nbelong to multiple A's\nat the same time?"}
    Q2 -->|No| Q3
    Q2 -->|Yes| ManyToMany

    Q3{"If A is deleted,\ndoes B still have meaning\nwithout A?"}
    Q3 -->|"No — delete together"| OneToMany_Exclusive["1:N — Exclusive\n@OneToMany / @ManyToOne\ncascade: true\nTable B has FK → A"]
    Q3 -->|"Yes — B survives"| Q4

    Q4{"Is B a Master Data record\nshared across\nmany different A's?"}
    Q4 -->|No| OneToMany_Exclusive
    Q4 -->|Yes| ManyToMany

    OneToMany_Exclusive --> ExNotes["Examples:\n• Order → Order Items\n• Organization → Department\n• GenericDrug → DrugSubstitution"]
    ManyToMany --> MNNotes["Examples:\n• GenericDrug ↔ Route\n• GenericDrug ↔ Frequency\n• Operator ↔ Resource\n• User ↔ Role"]

    ManyToMany["M:N Relationship\n@ManyToMany + @JoinTable\nAlways requires a join table"]

    style OneToMany_Exclusive fill:#d4edda,stroke:#28a745
    style ManyToMany fill:#cce5ff,stroke:#004085
    style OneToOne fill:#fff3cd,stroke:#856404

The Snapshot Trap

The most common mistake is confusing snapshot data with shared master data. If Table B captures a point-in-time record (e.g., a price at the time of mapping, a quantity at the time of ordering), it is a snapshot — even if the “source” data is shared. Snapshots are always 1:N because each snapshot row is exclusively owned by its parent transaction.

---

Comparison Table

Criteria	One-to-Many (1:N)	Many-to-Many (M:N)
Ownership	Child is exclusively bound to one parent	Data is a shared resource across many parents
On parent delete	Children are typically cascade-deleted	The other side must survive — it’s used elsewhere
Complexity	Low (FK column in child table)	Medium (requires a join table)
TypeORM Decorators	@OneToMany / @ManyToOne	@ManyToMany + @JoinTable
Key question	Can B only belong to one A?	Can B be shared across many A’s?

---

Code Example: GenericDrug Entity

The GenericDrug entity is a perfect real-world example because it uses both 1:N and M:N in the same file. Study the reasoning behind each choice:

@Entity({ name: 'generic_drugs', database: AppDatabases.APP_CORE })
export class GenericDrug extends BaseEntity {

    // ==========================================
    // 1:N — GenericDrug → DrugSubstitution
    // ==========================================
    // Why 1:N? Because the substitution record "Sara 500mg"
    // with its snapshot price belongs ONLY to Paracetamol.
    // It cannot belong to Ibuprofen at the same time.
    // Reverse check: "No" → 1:N
    @OneToMany(
        () => DrugSubstitution,
        (sub) => sub.generic_drug,
        { cascade: true }, // Delete generic drug → delete its substitutions
    )
    substitutions?: DrugSubstitution[];

    // ==========================================
    // M:N — GenericDrug ↔ Routes
    // ==========================================
    // Why M:N? Because the route "Oral (PO)" is a master data record
    // shared by hundreds of drugs. Deleting Paracetamol must NOT
    // delete the "PO" route — other drugs still use it.
    // Reverse check: "Yes" → M:N
    @ManyToMany(() => Route)
    @JoinTable({
        name: 'generic_drugs_routes',
        joinColumn: { name: 'generic_drug_id', referencedColumnName: 'id' },
        inverseJoinColumn: { name: 'route_id', referencedColumnName: 'id' },
    })
    routes?: Route[];

    // M:N — GenericDrug ↔ Sig Patterns (dosage instructions)
    @ManyToMany(() => SigPattern)
    @JoinTable({ name: 'generic_drugs_sig_patterns', ... })
    sig_patterns?: SigPattern[];

    // M:N — GenericDrug ↔ Drug Properties (therapeutic effects)
    @ManyToMany(() => DrugProperty)
    @JoinTable({ name: 'generic_drugs_drug_properties', ... })
    properties?: DrugProperty[];

    // M:N — GenericDrug ↔ Frequencies (dosing frequencies)
    @ManyToMany(() => Frequency)
    @JoinTable({ name: 'generic_drugs_frequencies', ... })
    frequencies?: Frequency[];
}

Why each relation was chosen:

Relation	Type	Reasoning
substitutions	1:N	Each substitution (e.g., “Sara 500mg, catalog price: 3.00”) is a snapshot record owned exclusively by one generic drug. Cascade-delete on parent removal.
routes	M:N	”Oral (PO)” is master data shared across hundreds of drugs.
sig_patterns	M:N	”Take after meals, 3 times daily” is a template reused by many drugs.
properties	M:N	”Antipyretic” is a therapeutic property shared by Paracetamol, Aspirin, etc.
frequencies	M:N	”TID (3 times/day)” is a master data record used across all drug types.

Pattern Recognition

Notice the pattern: every M:N relation in this entity points to a master data table (routes, sig patterns, properties, frequencies). Every 1:N relation points to a snapshot/exclusive child (substitutions with snapshot prices). This pattern holds true across most domain models.

---

DTO Patterns: How 1:N and M:N Differ in Practice

The entity defines the database structure, but the DTO defines what the client actually sends. This is where the 1:N vs. M:N distinction becomes very concrete — the DTO shape and the transformation logic are fundamentally different for each pattern.

Side-by-Side Comparison

Aspect	1:N (Substitutions)	M:N (Routes, Frequencies, etc.)
What the client sends	Full child objects with all properties	An array of existing UUIDs
DTO type	CreateDrugSubstitutionDTO[] (nested DTO)	string[] → transformed to { id: string }[]
Validation	@ValidateNested({ each: true }) + @Type()	@Transform(mapRelations())
Data creation	Creates new child records (INSERT)	Links to existing master data records (JOIN TABLE INSERT)
Swagger example	Full object: { catalog_code: "SARA01", price: 3.00 }	UUID array: ["uuid-1", "uuid-2"]

1:N DTO Pattern — Nested Child Objects

For a 1:N relation, the child doesn’t exist yet. The client sends complete objects with all required fields, and TypeORM creates them as new rows in the child table:

// CreateGenericDrugDTO — 1:N section

@ApiPropertyOptional({
    type: [CreateDrugSubstitutionDTO],
    description: 'Catalog drug substitutions for this generic drug',
})
@IsOptional()
@IsArray()
@ValidateNested({ each: true })  // Validate EACH object in the array
@Type(() => CreateDrugSubstitutionDTO)  // Transform plain objects → DTO instances
substitutions?: CreateDrugSubstitutionDTO[];

The client sends a full object for each child:

{
    "generic_name_en": "Paracetamol",
    "substitutions": [
        {
            "catalog_drug_code": "CATALOG001",
            "catalog_drug_name": "Sara Paracetamol 500mg",
            "strength": "500 mg",
            "unit_price": 3.00,
            "manufacturer_name": "GPO",
            "is_preferred": true
        },
        {
            "catalog_drug_code": "CATALOG002",
            "catalog_drug_name": "Tylenol 500mg",
            "strength": "500 mg",
            "unit_price": 5.00,
            "manufacturer_name": "J&J",
            "is_preferred": false
        }
    ]
}

Key decorators explained:

• @ValidateNested({ each: true }) — Runs class-validator on each object inside the array. Without this, the nested objects won’t be validated at all.
• @Type(() => CreateDrugSubstitutionDTO) — Tells class-transformer to convert each plain JS object into a CreateDrugSubstitutionDTO instance. Without this, validation decorators on the nested DTO won’t fire.

M:N DTO Pattern — UUID Array with @Transform(mapRelations())

For an M:N relation, the related records already exist in the database as master data. The client only sends an array of UUIDs referencing those existing records:

// CreateGenericDrugDTO — M:N section

@ApiPropertyOptional({
    description: 'Route IDs (routes of administration)',
    example: ['550e8400-e29b-41d4-a716-446655440000'],
})
@Transform(mapRelations())  // ["uuid-1", "uuid-2"] → [{ id: "uuid-1" }, { id: "uuid-2" }]
@IsOptional()
@IsArray()
routes?: CreateRouteDTO[];

The client sends just UUIDs:

{
    "generic_name_en": "Paracetamol",
    "routes": ["550e8400-e29b-41d4-a716-446655440000", "550e8400-e29b-41d4-a716-446655440001"],
    "frequencies": ["660e8400-e29b-41d4-a716-446655440002"],
    "sig_patterns": ["770e8400-e29b-41d4-a716-446655440003", "770e8400-e29b-41d4-a716-446655440004"]
}

How mapRelations() Works

The mapRelations() utility is a class-transformer function that converts a flat UUID array into the object format that TypeORM expects for M:N relation saving:

libs/common/src/utils/map-relations.util.ts

export function mapRelations(key: string = 'id') {
    return ({ value }: TransformFnParams) => {
        if (Array.isArray(value)) {
            return value.map((id) => ({ [key]: id }));
        }
        return [];
    };
}

Transformation flow:

Client sends:     ["uuid-1", "uuid-2", "uuid-3"]
                           ↓
@Transform(mapRelations())
                           ↓
TypeORM receives: [{ id: "uuid-1" }, { id: "uuid-2" }, { id: "uuid-3" }]
                           ↓
TypeORM inserts:  INSERT INTO generic_drugs_routes (generic_drug_id, route_id)
                  VALUES ('parent-uuid', 'uuid-1'),
                         ('parent-uuid', 'uuid-2'),
                         ('parent-uuid', 'uuid-3');

Why mapRelations() Matters

Without @Transform(mapRelations()), TypeORM receives a raw string array ["uuid-1", "uuid-2"] and doesn’t know how to resolve them to existing entities. The transformation step converts each UUID into { id: "uuid-1" }, which TypeORM recognizes as “find the existing record with this ID and link it in the join table.” This is what makes M:N saves work with just UUIDs instead of full objects.

Full DTO Comparison at a Glance

export class CreateGenericDrugDTO {
    // ─── 1:N: Send FULL objects → creates NEW child rows ───
    @IsOptional()
    @IsArray()
    @ValidateNested({ each: true })
    @Type(() => CreateDrugSubstitutionDTO)
    substitutions?: CreateDrugSubstitutionDTO[];

    // ─── M:N: Send UUID array → links EXISTING master data ───
    @Transform(mapRelations())
    @IsOptional()
    @IsArray()
    routes?: CreateRouteDTO[];

    @Transform(mapRelations())
    @IsOptional()
    @IsArray()
    frequencies?: CreateFrequencyDTO[];

    @Transform(mapRelations())
    @IsOptional()
    @IsArray()
    sig_patterns?: CreateSigPatternDTO[];

    @Transform(mapRelations())
    @IsOptional()
    @IsArray()
    properties?: CreateDrugPropertyDTO[];
}

Common DTO Mistakes

• Using @ValidateNested on M:N fields: Don’t validate nested properties when the client only sends UUIDs. The UUIDs are transformed into { id: "..." } objects — they don’t have the other properties that the DTO class requires.
• Using @Transform(mapRelations()) on 1:N fields: Don’t transform substitution objects into { id: "..." } — the client is sending full objects that need to be validated and created as new records.
• Forgetting @Type() on 1:N fields: Without @Type(() => ChildDTO), class-transformer won’t instantiate the nested DTO class, and class-validator decorators on the child DTO will be silently ignored.
• Forgetting @Transform(mapRelations()) on M:N fields: TypeORM will receive raw strings instead of { id } objects and the save will fail silently or throw a confusing error.

---

10 Real-World Use Cases (Enterprise Domain)

Case 1: Generic Drug & Drug Substitution → 1:N

Generic Drug: Paracetamol 500mg
├── Sara Paracetamol 500mg  (catalog price: 3.00)
├── Tylenol 500mg           (catalog price: 5.00)
└── Generic Paracetamol     (catalog price: 1.50)

Reverse check: Can “Sara 500mg (3.00)” also be a substitution for Ibuprofen? No — it is exclusively bound to Paracetamol with its own snapshot price → 1:N

Why not M:N?

Because DrugSubstitution captures a snapshot — the catalog-specific name and price at the time of mapping. This is not master data that can be shared. Each row is owned by exactly one generic drug.

---

Case 2: Generic Drug & Route of Administration → M:N

Paracetamol 500mg  ──┐
Ibuprofen 400mg    ──┼── Oral (PO)
Amoxicillin 250mg  ──┘
                         ── Intravenous (IV)
                         ── Intramuscular (IM)

Reverse check: Can the single “Oral (PO)” record be used by drugs other than Paracetamol? Yes — hundreds of drugs share it → M:N

If you update the label “PO” to “Per Oral”, every drug using that route reflects the change instantly.

---

Case 3: Generic Drug & Drug Properties → M:N

Paracetamol  ──┐
Aspirin      ──┼── Antipyretic (fever reducer)
Ibuprofen    ──┘   Analgesic (pain relief)
                    Anti-inflammatory

Reverse check: Can “Antipyretic” be shared across multiple drugs? Yes → M:N

---

Case 4: Engagement Order & Order Items → 1:N

Order ENG001
├── Line 1: Paracetamol 500mg × 10 units
├── Line 2: Amoxicillin 250mg × 21 units
└── Line 3: Vitamin C 500mg × 30 units

Reverse check: Can “Line 1: Paracetamol × 10” also appear in order ENG002? No — each line item is a snapshot (quantity, price at that moment) exclusively owned by one order → 1:N

Common Mistake

Do not confuse “the drug Paracetamol” (master data, shared) with “the order line item for Paracetamol in this engagement” (snapshot, exclusive). The order line captures a point-in-time record — it belongs to exactly one order. This distinction is the most frequent source of modeling errors.

---

Case 5: Operator & Resource → M:N

Operator Smith    ──┐
Operator Johnson  ──┼── Resource REF001
                   └── Resource REF002
                       Resource REF003

Reverse check: Can one resource have multiple assigned operators (e.g., primary, secondary, specialist)? Yes → M:N

---

Case 6: Resource & Restriction → M:N

Resource REF001  ──┐
Resource REF002  ──┼── Penicillin Restriction
Resource REF003  ──┘   Sulfa Drug Restriction
                        Aspirin Restriction

Reverse check: Can one restriction apply to many resources? Yes. Can one resource have many restrictions? Yes → M:N

---

Case 7: Department & Room → 1:N

Operations Department (DEPT-OPS)
├── Room 101
├── Room 102
└── Room 103

Reverse check: Can Room 101 physically exist in two departments simultaneously? No — a room belongs to exactly one department → 1:N

Moving a room to another department simply means updating the department_id FK in the rooms table.

---

Case 8: Generic Drug & Sig Patterns → M:N

Paracetamol  ──┐
Ibuprofen    ──┼── "Take 1–2 tablets when needed"
               └── "Take after meals, 3 times daily"
                   "Take before bedtime, once daily"

Reverse check: Can “Take after meals, 3 times daily” be reused by drugs other than Paracetamol? Yes — it’s a template → M:N

---

Case 9: User & Role → M:N

Operator Smith     ──┐
Staff Johnson      ──┼── ROLE_OPERATOR
                    └── ROLE_STAFF
                        ROLE_FULFILLMENT

Reverse check: Can the “OPERATOR” role have many users? Yes. Can one user hold multiple roles? Yes → M:N

This aligns with the IAM Architecture which uses user_roles as the join table.

---

Case 10: Organization & Division → 1:N

Organization A
├── Operations Division
├── Engineering Division
└── Support Division

Reverse check: Can “Operations Division of Organization A” also belong to Organization B? No — each division is owned by one organization → 1:N

---

Do’s and Don’ts

Do’s

• Always apply the Reverse Check Rule before writing any @OneToMany or @ManyToMany decorator. Two questions, every time.
• Use cascade: true on 1:N relations when the child has no meaning without the parent (e.g., order items without an order).
• Use @JoinTable on the owning side of M:N relations. The owning side is typically the entity that “initiates” the relationship (e.g., GenericDrug owns the join table to Route).
• Name join tables descriptively: generic_drugs_routes, users_roles, generic_drugs_sig_patterns. The pattern is {parent_table}_{child_table}.
• Think about the delete scenario: “If I delete A, should B disappear too?” If yes → 1:N with cascade. If no → M:N.

Don’ts

Critical Don’ts

• Never use M:N for snapshot/transactional data. If the child row captures a point-in-time value (price, quantity, status at that moment), it is a 1:N relationship — even if the “source” entity is shared.
• Never use 1:N when the child is master data. If deleting the parent would delete a shared master data record (e.g., deleting a drug would delete the “Oral (PO)” route), your model is wrong.
• Never skip the join table for M:N. TypeORM’s @ManyToMany without @JoinTable on either side will silently fail or produce unexpected behavior.
• Never put cascade: true on M:N relations unless you intentionally want deleting one side to wipe out the shared resource — which you almost never do.

---

Summary Checklist

Before designing any relationship, walk through these four questions in order:

1. Is the child a “snapshot” or “exclusive record”?

   • Yes (e.g., price at mapping time, order line item) → Use 1:N

2. Is the child “master data” shared across many parents?

   • Yes (e.g., Route, Frequency, Role, Permission) → Use M:N

3. If the parent is deleted, does the child lose all meaning?

   • Loses meaning → Use 1:N with cascade: true
   • Still meaningful → Use M:N

4. When you update the child, should all parents reflect the change?

   • Yes → Use M:N (references a shared master data record)
   • No (each parent has its own copy) → Use 1:N

---

Quick Reference

The key question: "Can one B belong to multiple A's at the same time?"
  ├── No  → 1:N  (@OneToMany / @ManyToOne)
  └── Yes → M:N  (@ManyToMany + @JoinTable)

Pattern	TypeORM Decorators	Join Table	Use When
1:N	@OneToMany + @ManyToOne	Not needed	Child is exclusively owned by one parent
M:N	@ManyToMany + @JoinTable	Always required	Both sides can reference many of the other

---

Related Documentation

• Entity & DTO Principles — Entity definition standards and TypeORM patterns
• Database Architecture Overview — Three-database architecture and lookup table patterns
• Identity & Access Management — IAM system using user_roles as a join table
• Clean Architecture — Service layer patterns for managing relations