1. Nest.js 광부왕 게임 서버 DB 구성

- Main DB

DB : Mysql

사용 범위

• 유저 Table
• 아이탬 Table
• 게임 내역 Table 
• 회사 (길드) Table
• 회사 (길드) 회원 Table
• 선물 리스트 보관 Table
• 스테이지별 난이도 정보 Table

관계 설정

* 스테이별 난이도 정보 Table은 관계가 필요 없어서 설정이 되어 있지 않다.

 

1. 유저 테이블 관계

 

1 : N 관계

• 유저 정보 : 유저 아이탬
• 유저 정보 : 게임 내역
• 유저 정보 : 회사 회원
• 유저 정보 : 선물 리스트 관계

 

2. 회사 테이블 관계

 

1:N 관계

 

• 회사 : 회사원

 유저 Table

users.model.ts

import { ApiProperty } from '@nestjs/swagger';
import { IsBoolean, IsNotEmpty, IsString } from 'class-validator';
import { Entity, Column, PrimaryGeneratedColumn } from 'typeorm';

@Entity()
export class Users {
  @PrimaryGeneratedColumn('uuid')
  @IsString()
  @IsNotEmpty()
  @ApiProperty({ example: 'user-index', description: 'userIndex' })
  //유저 인덱스
  userIndex: string;

  @ApiProperty({
    example: 'testID',
    description: 'id',
    required: true,
  })
  @Column({ type: 'varchar', length: 10, unique: true })
  @IsString()
  @IsNotEmpty()
  //아이디
  id: string;

  @ApiProperty({ example: 'pwd123', description: 'password', required: true })
  @Column({ type: 'varchar', length: 128 })
  @IsString()
  @IsNotEmpty()
  //비밀번호
  password: string;

  @Column({ type: 'varchar', length: 20 })
  @IsString()
  @IsNotEmpty()
  // 암호값
  salt: string;

  @ApiProperty({
    example: 'testNickname',
    description: 'nickname',
    required: true,
  })
  @Column({ type: 'varchar', nullable: true, default: null })
  @IsString()
  // 닉네임
  nickname: string;

  @Column({ type: 'boolean', default: true })
  @IsBoolean()
  //상태 값
  status: boolean;

  @Column({ type: 'boolean', default: true })
  @IsBoolean()
  //유저 권한
  accessLevel: boolean;
}

아이탬 Table

items.model.ts

import {
  Entity,
  Column,
  PrimaryGeneratedColumn,
  JoinColumn,
  OneToOne,
} from 'typeorm';
import { Users } from './users.model';

@Entity()
export class Items {
  @PrimaryGeneratedColumn('uuid')
  index: string;

  @OneToOne(() => Users, { onDelete: 'CASCADE' })
  @JoinColumn({ name: 'user' })
  user: string;

  @Column({ type: 'int', default: 1 })
  drill: number;

  @Column({ type: 'int', default: 1 })
  oxygenRespirator: number;

  @Column({ type: 'int', default: 1 })
  dynamite: number;

  @Column({ type: 'int', default: 1 })
  coworker: number;

  @Column({ type: 'int', default: 0 })
  gold: number;
}

 

게임 내역 Table 

game-records.model.ts

import {
  Entity,
  Column,
  PrimaryGeneratedColumn,
  JoinColumn,
  OneToOne,
} from 'typeorm';
import { Users } from './users.model';

@Entity()
export class GameRecords {
  @PrimaryGeneratedColumn('uuid')
  index: string;

  @OneToOne(() => Users, { onDelete: 'CASCADE' })
  @JoinColumn({ name: 'user' })
  user: string;

  @Column({ type: 'int', default: 1 })
  stage: number;

  @Column({ type: 'int', default: 1 })
  level: number;

  @Column({ type: 'int', default: 0 })
  exp: number;
}

 

회사 (길드) Table

companies.model.ts

import { Entity, Column, PrimaryGeneratedColumn } from 'typeorm';

import { IsNotEmpty, IsString } from 'class-validator';
import { ApiProperty } from '@nestjs/swagger';

@Entity()
export class Companies {
  @PrimaryGeneratedColumn('uuid')
  index: string;

  @ApiProperty({
    example: 'companyTest',
    description: 'companyName',
    required: true,
  })
  @IsString()
  @IsNotEmpty()
  @Column({ unique: true, type: 'varchar', length: 20 })
  companyName: string;
}

 

회사 (길드) 회원 Table

company-users.model.ts

import {
  Entity,
  Column,
  PrimaryGeneratedColumn,
  OneToOne,
  JoinColumn,
  ManyToOne,
} from 'typeorm';
import { Users } from './users.model';
import { IsIn, IsNotEmpty, IsNumber, IsString } from 'class-validator';
import { ApiProperty } from '@nestjs/swagger';
import { Companies } from './companies.model';

@Entity()
export class CompanyUsers {
  @PrimaryGeneratedColumn('uuid')
  index: string;

  @OneToOne(() => Users, { onDelete: 'CASCADE' })
  @JoinColumn({ name: 'user' })
  user: string;

  @ApiProperty({
    example: 'dfgkjsldjrijgalmvlskmfg',
    description: 'companyIndex',
    required: true,
  })
  @IsString()
  @IsNotEmpty()
  @Column({ type: 'varchar' })
  @ManyToOne(() => Companies, { onDelete: 'CASCADE' })
  @JoinColumn({ name: 'companyIndex' })
  companyIndex: string;

  // 6 > 5 > 4 > 3 > 2 > 1
  // 승인 미정 > 이사 > 상무 > 전무 > 부사장 > 사장
  @ApiProperty({
    example: 3,
    description: 'position',
    required: true,
  })
  @IsNumber()
  @IsNotEmpty()
  @IsIn([1, 2, 3, 4, 5, 6])
  @Column({ type: 'int', default: 0, width: 6 })
  position: number;
}

 

선물 리스트 보관 Table

gifts.model.ts

import {
  Entity,
  Column,
  PrimaryGeneratedColumn,
  JoinColumn,
  ManyToOne,
} from 'typeorm';
import { Users } from './users.model';

@Entity()
export class Gifts {
  @PrimaryGeneratedColumn('uuid')
  index: string;

  @ManyToOne(() => Users, (users) => users.userIndex)
  @JoinColumn({ name: 'sendUser' })
  sendUser: string;

  @Column({ type: 'varchar', length: 36 })
  receiveUser: string;

  @Column({ type: 'int', default: 0 })
  gold: number;

  @Column({ type: 'varchar', length: 50, nullable: true })
  message: string;

  @Column({ type: 'boolean', default: false })
  status: boolean;
}

 

스테이지별 난이도 정보 Table

stages.model.ts

import { ApiProperty } from '@nestjs/swagger';
import { Type } from 'class-transformer';
import { IsNotEmpty, IsNumber } from 'class-validator';
import { Entity, Column, PrimaryGeneratedColumn } from 'typeorm';

@Entity()
export class Stages {
  @IsNumber()
  @Type(() => Number)
  @ApiProperty({
    example: 1,
    description: 'stage',
    required: true,
  })
  @PrimaryGeneratedColumn()
  stage: number;

  @ApiProperty({
    example: 1,
    description: 'coal',
    required: true,
  })
  @Column({ type: 'int', default: 0 })
  @IsNumber()
  @IsNotEmpty()
  coal: number;

  @ApiProperty({
    example: 3,
    description: 'bronze',
    required: true,
  })
  @IsNumber()
  @IsNotEmpty()
  @Column({ type: 'int', default: 0 })
  bronze: number;

  @ApiProperty({
    example: 2,
    description: 'silver',
    required: true,
  })
  @IsNumber()
  @IsNotEmpty()
  @Column({ type: 'int', default: 0 })
  silver: number;

  @ApiProperty({
    example: 6,
    description: 'emerald',
    required: true,
  })
  @IsNumber()
  @IsNotEmpty()
  @Column({ type: 'int', default: 0 })
  emerald: number;

  @ApiProperty({
    example: 4,
    description: 'amethyst',
    required: true,
  })
  @IsNumber()
  @IsNotEmpty()
  @Column({ type: 'int', default: 0 })
  amethyst: number;

  @ApiProperty({
    example: 1,
    description: 'diamond',
    required: true,
  })
  @IsNumber()
  @IsNotEmpty()
  @Column({ type: 'int', default: 0 })
  diamond: number;

  @ApiProperty({
    example: 2,
    description: 'lithium',
    required: true,
  })
  @IsNumber()
  @IsNotEmpty()
  @Column({ type: 'int', default: 0 })
  lithium: number;

  @ApiProperty({
    example: 1,
    description: 'useDynamite',
    required: true,
  })
  @IsNumber()
  @IsNotEmpty()
  @Column({ type: 'int', default: 0 })
  useDynamite: number;
}