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