数据库表之间的关系和设计

一对一关系（One-to-One）

在这里就以一个用户表和抽奖次数表为例 一个用户拥有多少次抽奖机会 就是一对一的关系

user.entity.ts

import {Column, Entity, ManyToOne, PrimaryGeneratedColumn, JoinColumn, OneToMany, OneToOne} from 'typeorm'
import {RoleEntity} from '@entity/role.entity'
import {LotteryRecordEntity} from '@entity/lotteryRecord.entity'
import {LotteryChancesEntity} from '@entity/lotteryChances.entity'

@Entity({name: 'user'})
export class UserEntity {
  @PrimaryGeneratedColumn({type: 'int'})
  id: number
  @Column({type: 'varchar', length: 255, nullable: false})
  name: string
  @Column({nullable: true, default: 'man'})
  gender: string
  @Column({nullable: true, default: 0})
  age: number
  @Column({nullable: false, unique: true, type: 'bigint'})
  phone: string
  @Column({nullable: true})
  description: string
  @Column({nullable: true, type: 'datetime', name: 'create_time'})
  createTime: Date
  @Column({type: 'varchar', length: 255, nullable: false})
  password: string

  // many users can have one role. explicit inverse and join column (role_id)
  @ManyToOne(() => RoleEntity, role => role.users, {nullable: true})
  @JoinColumn({name: 'role_id'})
  role: RoleEntity | null

  // @OneToMany(() => LotteryRecordEntity, lotteryRecord => lotteryRecord.user)
  // lotteryRecords: LotteryRecordEntity[]

  @OneToOne(() => LotteryChancesEntity, lotteryChances => lotteryChances.user)
  lotteryChances: LotteryChancesEntity
}

需要在某个表中加入对应的外键JoinColumn

lotteryChances.entity.ts

import {Column, Entity, JoinColumn, OneToOne, PrimaryGeneratedColumn} from 'typeorm'
import {UserEntity} from '@entity/user.entity'

@Entity('lottery_chances')
export class LotteryChancesEntity {
  @PrimaryGeneratedColumn()
  id: number

  @OneToOne(() => UserEntity, user => user.lotteryChances)
  @JoinColumn({name: 'user_id'})
  user: UserEntity

  @Column()
  num: number
}

一对多关系（One-to-Many）

在这里就以用户表和角色表为例 多个用户可以拥有同一种角色 但是一个角色可以被多个用户拥有 这就是一对多的关系

根据对应关系，外键就放在了用户表中

role.entity.ts

import {Column, Entity, ManyToMany, OneToMany, PrimaryGeneratedColumn, JoinTable} from 'typeorm'
import {UserEntity} from '@entity/user.entity'
import {PermissionEntity} from '@entity/permission.entity'

@Entity({name: 'role'})
export class RoleEntity {
  @PrimaryGeneratedColumn({type: 'int'})
  id: number

  @Column({type: 'varchar', length: 255, nullable: false})
  name: string

  @Column({type: 'varchar', length: 255, nullable: true})
  description: string

  @OneToMany(() => UserEntity, user => user.role)
  users: UserEntity[]

  @ManyToMany(() => PermissionEntity, permission => permission.roles)
  @JoinTable({name: 'role_permission'})
  permissions: PermissionEntity[]
}

多对多关系（Many-to-Many）

在多对多的关系中，就不单单是加个外键就能搞定了，需要一张中间表来进行关联 比如角色表和权限表 一个角色可以拥有多个权限 一个权限也可以被多个角色拥有

在这里就需要使用 @ManyToMany 和 @JoinTable 来进行关联

permission.entity.ts

import {Column, Entity, JoinColumn, ManyToMany, ManyToOne, OneToMany, PrimaryGeneratedColumn} from 'typeorm'
import {RoleEntity} from '@entity/role.entity'

@Entity({name: 'permission'})
export class PermissionEntity {
  @PrimaryGeneratedColumn({type: 'int'})
  id: number
  @Column({type: 'varchar', nullable: false, length: 255})
  key: string

  @Column({type: 'varchar', nullable: false, length: 255})
  name: string

  @Column({type: 'int', nullable: true})
  parent_id: Nullable<number>

  @Column({nullable: true})
  icon: string

  // 自引用：多对一（子 -> 父）
  @ManyToOne(() => PermissionEntity, permission => permission.children)
  @JoinColumn({name: 'parent_id'})
  parent: PermissionEntity | null

  @ManyToMany(() => RoleEntity, role => role.permissions)
  roles: RoleEntity[]

  // 自引用：一对多（父 -> 子）
  @OneToMany(() => PermissionEntity, permission => permission.parent)
  children?: PermissionEntity[]
}