billingProject
Tables (6)
Advertisement
- advertisementId — INT UNSIGNED — Primary key — NOT NULL — auto increment
- adPriority — INT UNSIGNED — NOT NULL — unique
AdvertisementDetail
- adDetailId — INT UNSIGNED — Primary key — NOT NULL — auto increment
- viewCount — INT UNSIGNED — NOT NULL
- videoId — INT UNSIGNED — Foreign key → Video.videoId — NOT NULL — Video PK
- adPriority — INT UNSIGNED — Foreign key → Advertisement.advertisementId — NOT NULL
User
- email — VARCHAR(200) — Primary key — NOT NULL
- password — VARCHAR(100) — NOT NULL
- refreshToken (refreshtoken) — VARCHAR(255) — NOT NULL — 100으로 설정했을 때 길이 초과
- role — TINY INT — NOT NULL
Video
- videoId — INT UNSIGNED — Primary key — NOT NULL — auto increment
- email — VARCHAR(200) — Foreign key → User.email — NOT NULL — User PK
- title — VARCHAR(50) — NOT NULL
- desc — VARCHAR(100) — NOT NULL
- duration — INT UNSIGNED — NOT NULL
- totalViewCount — INT UNSIGNED — NOT NULL — 해당 영상의 누적조회수
- totalAdViewCount — INT UNSIGNED — NOT NULL — 해당 영상의 누적 광고 재생횟수
- createdAt — DATETIME
VideoStatistic
- videoStatisticId — INT UNSIGNED — Primary key — NOT NULL — auto increment
- videoId — INT UNSIGNED — Foreign key → Video.videoId — NOT NULL — - 날짜별로는 unique 하다. - 복합인덱스 칼럼#1
- dailyAdProfit — INT UNSIGNED — NOT NULL
- dailyVideoProfit — INT UNSIGNED — NOT NULL — 일간 정산액
- dailyViewCout — INT UNSIGNED — NOT NULL
- dailyWatchedTime — BIGINT UNSIGNED — NOT NULL
- dailyAdViewCount — INT UNSIGNED — NOT NULL
- createdAt — DATE — NOT NULL — 복합인덱스 칼럼#2
WatchHistory
- watchHistoryId — VARCHAR(50) — Primary key — NOT NULL — 고유 문자열 생성
- createdAt — DATE — Primary key — NOT NULL — 파티셔닝 키로 PK 포함
- email — VARCHAR(200) — Foreign key → User.email — User PK
- videoId — INT UNSIGNED — Foreign key → Video.videoId — NOT NULL — Video PK
- playedTime — INT UNSGINED — NOT NULL
- lastWatched — INT UNSIGNED — NOT NULL
- deviceUUID — VARCHAR(50) — NOT NULL
- adViewCount — SMALLINT UNSIGNED — NOT NULL
- watchedAt — DATETIME — DESC정렬, 마지막 기록 찾기
- numericOrderKey — BIGINT UNSIGNED — NOT NULL — auto increment 동시성 이슈 해결을 위한 sortKey로 활용
- assignedServer — TINYINT(1) — NOT NULL