table 구상
user-info
uid : unsigned int, auto_increment, primary_key, comment: user id
company : unsigned int, not null, comment: 법인
department : varchar(100), not null , comment: 부서
groupware-id : varchar(100), not null , comment: 그룹웨어 id
join-date : timestamp, comment: 가입
birthday : timestamp, comment: 생일
grade: unsigned int, not null, default : 1, comment : 등급
state: unsigned int, not null, default : 1, comment : 활동 여부 (활동 중, 휴면)
register_time : timestamp
update_time : timestamp
* index : idx_uid (uid), idx_birthday (birthday)
company
idx : unsigned int, auto_increment, primary_key, comment: 법인
name : varchar(20), not null , comment: 법인 이름
user-point
uid : unsigned int, auto_increment, primary_key, comment: user id
point : unsigned int, default 0, comment: 포인트
register_time : timestamp
update_time : timestamp
SQL문
-- ===========================
-- 1) company 테이블
-- ===========================
CREATE TABLE daracbang.company (
idx INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '법인',
name VARCHAR(20) NOT NULL COMMENT '법인 이름'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ===========================
-- 2) user_info 테이블
-- ===========================
CREATE TABLE daracbang.user_info (
uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'user id',
company INT UNSIGNED NOT NULL COMMENT '법인 idx (참조는 애플리케이션 레벨에서)',
department VARCHAR(100) NOT NULL COMMENT '부서',
groupware_id VARCHAR(100) NOT NULL COMMENT '그룹웨어 id',
join_date TIMESTAMP NULL COMMENT '가입일',
birthday TIMESTAMP NULL COMMENT '생일',
grade INT UNSIGNED NOT NULL DEFAULT 1 COMMENT '등급',
state INT UNSIGNED NOT NULL DEFAULT 1 COMMENT '활동 여부 (활동 중=1, 휴면=0 등)',
register_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '생성 시각',
update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '수정 시각',
INDEX idx_birthday (birthday),
INDEX idx_company (company)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ===========================
-- 3) user_point 테이블
-- ===========================
CREATE TABLE daracbang.user_point (
uid INT UNSIGNED NOT NULL COMMENT 'user_info.uid (참조는 애플리케이션 레벨에서)',
point INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '포인트',
register_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '생성 시각',
update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '수정 시각',
PRIMARY KEY (uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Entity
import { Entity, PrimaryGeneratedColumn, Column } from 'typeorm';
@Entity('company')
export class CompanyEntity {
@PrimaryGeneratedColumn({ type: 'int', unsigned: true, name: 'idx' })
idx: number;
@Column({ type: 'varchar', length: 20, nullable: false })
name: string;
}import { Entity, PrimaryGeneratedColumn, Column, Index } from 'typeorm';
@Entity('user_info')
@Index('idx_birthday', ['birthday'])
@Index('idx_company', ['company'])
export class UserInfoEntity {
@PrimaryGeneratedColumn({ type: 'int', unsigned: true, name: 'uid' })
uid: number;
@Column({ type: 'int', unsigned: true, name: 'company' })
company: number; // 법인 idx (참조는 서비스 로직에서 처리)
@Column({ type: 'varchar', length: 100, nullable: false })
department: string;
@Column({ type: 'varchar', length: 100, name: 'groupware_id', nullable: false })
groupwareId: string;
@Column({ type: 'timestamp', name: 'join_date', nullable: true })
joinDate: Date;
@Column({ type: 'timestamp', name: 'birthday', nullable: true })
birthday: Date;
@Column({ type: 'int', unsigned: true, default: 1 })
grade: number;
@Column({ type: 'int', unsigned: true, default: 1 })
state: number;
@Column({
type: 'timestamp',
name: 'register_time',
default: () => 'CURRENT_TIMESTAMP',
})
registerTime: Date;
@Column({
type: 'timestamp',
name: 'update_time',
default: () => 'CURRENT_TIMESTAMP',
onUpdate: 'CURRENT_TIMESTAMP',
})
updateTime: Date;
}import { Entity, PrimaryColumn, Column } from 'typeorm';
@Entity('user_point')
export class UserPointEntity {
@PrimaryColumn({ type: 'int', unsigned: true, name: 'uid' })
uid: number; // user_info.uid 값을 수동으로 넣어야 함
@Column({ type: 'int', unsigned: true, default: 0 })
point: number;
@Column({
type: 'timestamp',
name: 'register_time',
default: () => 'CURRENT_TIMESTAMP',
})
registerTime: Date;
@Column({
type: 'timestamp',
name: 'update_time',
default: () => 'CURRENT_TIMESTAMP',
onUpdate: 'CURRENT_TIMESTAMP',
})
updateTime: Date;
}** erd는 귀찮아서 생략해본다.... 시간이 되면 그려야
