TypeORM Sharing One Entity Between Two Other Entities

Question

I am working on a NestJS app using TypeORM, and have a Person entity and a Business entity. Each of those entities can have 0 or more Addresses. The address fields are the same whether it's for a Business or Person. Because of that, I'd like to put all addresses in one table, instead of managing a PersonAddress and a BusinessAddress table (provided this is the "proper" way to design my database).

Currently, here is part of my Person and Address entities:

// person.entity.ts
export class Person {
  @OneToMany(() => Address, (address) => address.person, { cascade: true})
  addresses: PersonAddress[]
}

// address.entity.ts
export class Address {

  @ManyToOne(() => Person, (person) => person.addresses)
  person: Person
}

I believe this is the proper way to do this, for TypeORM, but I'd like to be able to remove the reference in Address to Person so that I can use that same entity for Business.

Now, my question is two parts:

1. Is my desired design the proper way to design a database? I have experience using databases and querying them, but not designing them. If what I'm trying to do is poor database design, then I'll continue with having a separate entity for the address for Business and Person.
2. If my desired design is correct, how can I define the entities in TypeORM to achieve this outcome?

Answer 1

1. Is my desired design the proper way to design a database?

Yes, you want to have a nomilized database. In general there are some rules on database normalization and in general you want to stick to those rules, unless you have a good reason. These rules are gerally already applied by your ORM, e.g. every table needs to have an id column.

2. How can I define the entities in TypeORM to achieve this outcome?

TypeOrm lets you define ManyToOne relations one sided, but not the other way around source

@OneToMany cannot exist without @ManyToOne. If you want to use @OneToMany, @ManyToOne is required. However, the inverse is not required: If you only care about the @ManyToOne relationship, you can define it without having @OneToMany on the related entity.

That would make:

person.entity.ts

export class Person {
  @Column()
  name: string

  // etc...

  @OneToMany(() => Address, (address) => address.person)
  addresses: Address[]
}

business.entity.ts

export class Business {
  @Column()
  name: string

  // etc...

  @OneToMany(() => Address, (address) => address.business)
  addresses: Address[]
}

address.entity.ts

export class Address {
  @Column()
  name: string

  // etc...

  @ManyToOne(() => Person, (p) => p.addresses)
  person: Person

  @ManyToOne(() => Business , (b) => b.addresses)
  business: Business
}

If you insist on not have the ManyToOne in your address you can circumvent this by making the relation ManyToMany. Keep in mind that this will create some overhead in the form of bigger queries and more tables. This is because the many to many will create a join table and requires an extra join to get your data from the database. This is all taken care of by the orm tho.

That would make:

person.entity.ts

export class Person {
  @Column()
  name: string

  // etc...

  @ManyToMany(() => Address)
  addresses: Address[]
}

business.entity.ts

export class Business {
  @Column()
  name: string

  // etc...

  @ManyToMany(() => Address)
  addresses: Address[]
}

address.entity.ts

export class Address {
  @Column()
  name: string

  // etc...
}

Answer 2

I assume there is no relationship between entities Business and Person.

There is one problem in the current database design -

Deleting a person would delete all the addresses which are associated with that particular person. This would cause a problem if the same address (which is getting deleted because of deletion of a person) is associated with some business.

I would rather create a separate linking table BusinessAndPersonAddress with businessId and personId being foreign keys. That way you can associate the same address to multiple businesses and persons.

In this case, deletion of person would delete only those entries of addresses in the linking table BusinessAndPersonAddress which are associated with a person which is getting deleted, and entries of addresses, if they are associated with some business, would remain unaffected.