OMP ERD

Office Hour Management Platform

Tables (19)

ArrangedSchedule

  • ArrangedSchedule ID (asid) — int — Primary key — NOT NULL — 매칭된 오피스아워 ID
  • Company ID (cpid) — int — Foreign key → Company.Company ID — NOT NULL — 기업 ID
  • Company User ID (cpuid) — int — Foreign key → Company.User ID — NOT NULL — 기업 유저 ID
  • Consultant ID (csid) — int — Foreign key → Consultant.Consultant ID — NOT NULL — 컨설턴트 ID
  • Consultant User ID (csuid) — int — Foreign key → Consultant.User ID — NOT NULL — 컨설턴트 유저 ID
  • Topic ID (tid) — int — Foreign key → Topic.Topic ID — NOT NULL — 토픽 ID
  • Office Hour - Project ID (opid) — int — Foreign key → OfficeHourProject.Office Hour - Project ID — NOT NULL — 오피스아워-사업 ID
  • Office Hour ID (oid) — int — Foreign key → OfficeHourProject.Office Hour ID — NOT NULL — 오피스아워 ID
  • Porject ID (pid) — int — Foreign key → OfficeHourProject.Porject ID — NOT NULL — 사업 ID
  • ArrangedSchedule (asschedule) — datetime — 취소되면 null 처리
  • ArrangedSchedule (asplacetype) — int — NOT NULL — 장소유형 0: 오프라인, 1: 온라인
  • Consultant Report (asreport) — text — 보고서
  • Attachment (asattachment) — varchar(128) — 첨부 파일 링크
  • Attachment (asattachmentname) — varchar — 첨부 파일명
  • Arranged Schedule Status (asstatus) — int — NOT NULL — 0: Arranged, 1: Finished, 2 ;Canceled
  • Billing Price (asbillingprice) — int — 지불 금액
  • Billing Date (asbilingdate) — datetime — 세금계산서 발행일자
  • Arranged Schedule Company Topic (ascompanytopic) — text — NOT NULL — 기업에서 요청하는 세부 아젠다 내용
  • Arranged Schedule Google Calendar Id (asgooglecalendar) — varchar — 구글 캘린더 ID

ArrangedScheduleAttachment

  • ArrangedScheduleAttachment ID (asaid) — int — Primary key — NOT NULL — 매칭된 오피스아워 기업 첨부파일 ID
  • ArrangedSchedule ID (asid) — int — Foreign key → ArrangedSchedule.ArrangedSchedule ID — NOT NULL — 매칭된 오피스아워 ID
  • ArrangedScheduleAttachment Link (asalink) — varchar — NOT NULL — 파일 링크
  • ArrangedScheduleAttachment Name (asaname) — varchar — NOT NULL — 파일명

ArrangedScheduleComment

  • ArrangedScheduleComment ID (ascid) — int — Primary key — NOT NULL — 매칭된 오피스아워 사전 알림 메시지 ID
  • ArrangedSchedule ID (asid) — int — Foreign key → ArrangedSchedule.ArrangedSchedule ID — NOT NULL — 매칭된 오피스아워 ID
  • ArrangedScheduleComment Message (ascmessage) — text — NOT NULL — 메세지

ArrangedScheduleLog

  • ArrangedScheduleLog ID (aslid) — int — Primary key — NOT NULL — 매칭된 오피스아워 로그 ID
  • ArrangedSchedule ID (asid) — int — Foreign key → ArrangedSchedule.ArrangedSchedule ID — NOT NULL — 매칭된 오피스아워 ID
  • ArrangedScheduleLog Type (asltype) — int — NOT NULL — 0: 일정 수정, 1: 담당자 변경, 2: 취소
  • ArrangedScheduleLog Causer (aslcauser) — int — NOT NULL — 로그 발생자
  • ArrangedScheduleLog Before Schedule (aslbeforeschedule) — datetime — 수정 전 스케줄
  • ArrangedScheduleLog Moved Schedule (aslmovedschedule) — datetime — 수정된 스케줄
  • ArrangedScheduleLog Before Consultant (aslbeforeconsultant) — int — 수정 전 컨설턴트
  • ArrangedScheduleLog After Consultant (aslafterconsultant) — int — 수정된 컨설턴트
  • ArrangedScheduleLog Message (aslmessage) — text — NOT NULL — 메세지

Banner

  • Banner ID (bid) — int — Primary key — NOT NULL — 배너 ID
  • Banner Rank (brank) — int — NOT NULL — 배너 우선순위
  • Banner Link (blink) — varchar — NOT NULL — 배너 이미지 링크
  • Banner Content (bcontent) — text — NOT NULL — 배너 이미지 텍스트
  • Banner Showing (bisshowing) — int — NOT NULL — 배너 표시 플래그

Company

  • Company ID (cpid) — int — Primary key — NOT NULL — 기업 ID
  • User ID (uid) — int — Foreign key → User.User ID — NOT NULL — 유저 ID
  • Company Name (cpname) — varchar — NOT NULL — 기업명
  • Company Number (cpnumber) — varchar — 사업자등록번호
  • Company Representative Name (cprepresentativename) — varchar — 대표자명
  • Company Representative Gender (cprepresentativegender) — int — 0: 남성, 1: 여성
  • Company Establishment Date (cpestablishmentdate) — datetime — 설립일
  • Company Address (cpaddress) — varchar — 주소
  • Company Homepage Url (cphomepageurl) — varchar — 홈페이지 주소
  • Company Number of Workers (cpworkers) — int — 근로자 수
  • Company Industry (cpindustry) — varchar — 업종
  • Company Business Item (cpbusinessitem) — varchar — 사업 아이템
  • Company Introduction (cpintroduction) — text — 사업 소개
  • Company Marketing Notification (cpmarketingnotification) — int — NOT NULL — 마케팅/소식 알림

CompanyProject

  • CompanyProject ID (cupid) — int — Primary key — NOT NULL — 기업-사업 ID
  • Porject ID (pid) — int — Foreign key → Project.Porject ID — NOT NULL — 프로젝트 ID
  • Company ID (cpid) — int — Foreign key → Company.Company ID — NOT NULL — 기업 ID
  • User ID (uid) — int — Foreign key → Company.User ID — NOT NULL — 유저 ID
  • Normal Tickets (cupnticket) — int — NOT NULL — 일반 티켓 수
  • Special Tickets (cupsticket) — int — NOT NULL — 특수 티켓 수

Consultant

  • Consultant ID (csid) — int — Primary key — NOT NULL — 컨설턴트 ID
  • User ID (uid) — int — Foreign key → User.User ID — NOT NULL — 유저 ID
  • Consultant Company (cscompany) — varchar — NOT NULL — 소속기업
  • Consultant Link (cslink) — varchar — 화상 링크
  • Consultant Phone (csphone) — varchar — 보조 휴대폰
  • Consultant Email (csemail) — varchar — 보조 이메일

ConsultantTopic

  • ConsultantTopic ID (ctid) — int — Primary key — NOT NULL — 컨설턴트 토픽 ID
  • Consultant ID (csid) — int — Foreign key → Consultant.Consultant ID — NOT NULL — 컨설턴트 ID
  • User ID (uid) — int — Foreign key → Consultant.User ID — NOT NULL — 유저 ID
  • Topic ID (tid) — int — Foreign key → Topic.Topic ID — NOT NULL — 토픽 ID
  • ConsultantTopic Active (ctactive) — int — NOT NULL — 토픽 활성화

Notification

  • Notification ID (nid) — int — Primary key — NOT NULL — 알림 ID
  • Notification Type (ntype) — int — NOT NULL — 0: 컨설턴트, 1: 참여기업, 2: 기타
  • Notification Channel (nchannel) — int — NOT NULL — 0: 이메일, 1: 카카오톡
  • Notification Template (ntemplate) — text — NOT NULL — 알림 템플릿

OfficeHour

  • OfficeHour ID (oid) — int — Primary key — NOT NULL
  • Creator User ID (uid) — int — Foreign key → User.User ID — NOT NULL — 오피스아워 생성자
  • OfficeHour Name (oname) — varchar — NOT NULL — 오피스아워명
  • OfficeHour Start Date (ostartdate) — datetime — NOT NULL — 오피스아워 시작시점
  • OfficeHour End Date (oenddate) — datetime — NOT NULL — 오피스아워 종료시점
  • OfficeHour Schedule Input Start Date (oscheduleinputstartdate) — datetime — 오피스아워 일정 입력 시작시점
  • OfficeHour Schedule Input End Date (oscheduleinputenddate) — datetime — 오피스아워 일정 입력 종료 시점
  • OfficeHour Status (ostatus) — int — NOT NULL — 0: Approve, 1: Cancel, 2: Apply
  • OfficeHour Type (otype) — int — NOT NULL — 0: Regular, 1: Irregular
  • OfficeHour Reject Message (orejectmessage) — text — 오피스아워 거절 메세지
  • OfficeHour Schedule Apply Start Date (oscheduleapplystartdate) — datetime — 오피스아워 신청 시작시점
  • OfficeHour Schedule Apply End Date (oscheduleapplyenddate) — datetime — 오피스아워 신청 종료시점
  • OfficeHour Topic (otid) — int — 비정기 오피스아워 희망 토픽
  • OfficeHour Topic Detail (ocompanytopic) — text — 비정기 오피스아워 토픽 상세
  • OfficeHour Place Type (oplacetype) — int — 비정기 오피스아워 희망 장소 유형, 0: 오프라인, 1: 온라인

OfficeHourAttachment

  • OfficeHourAttachment ID (oaid) — int — Primary key — NOT NULL — 비정기오피스아워첨부파일 ID
  • OfficeHour ID (oid) — int — Foreign key → OfficeHour.OfficeHour ID — NOT NULL — 오피스아워 ID
  • OfficeHourAttachment Link (oalink) — varchar — NOT NULL — 첨부파일 링크
  • OfficeHourAttachment Name (oaname) — varchar — NOT NULL — 첨부파일명

OfficeHourProject

  • Office Hour - Project ID (opid) — int — Primary key — NOT NULL — 오피스아워-사업 ID
  • Office Hour ID (oid) — int — Foreign key → OfficeHour.OfficeHour ID — NOT NULL — 오피스아워 ID
  • Porject ID (pid) — int — Foreign key → Project.Porject ID — NOT NULL — 사업 ID

OfficeHourSchedule

  • OfficeHour Consultant Schedule ID (osid) — int — Primary key — NOT NULL — 오피스아워 컨설턴트 스케줄 ID
  • OfficeHour ID (oid) — int — Foreign key → OfficeHour.OfficeHour ID — NOT NULL — 오피스아워 ID
  • Consultant ID (csid) — int — Foreign key → Consultant.Consultant ID — NOT NULL — 컨설턴트 ID
  • User ID (uid) — int — Foreign key → Consultant.User ID — NOT NULL — 유저 ID
  • OfficeHour Open Schedule (osschedule) — datetime — NOT NULL — 오피스아워 스케줄

Project

  • Porject ID (pid) — int — Primary key — NOT NULL — 사업 ID
  • Admin User ID (uid) — int — Foreign key → User.User ID — NOT NULL — 관리자 유저 ID
  • Project Name (pname) — varchar(128) — NOT NULL — 사업 명
  • Project Start Date (pstartdate) — datetime — NOT NULL — 사업 시작 시점
  • Project End Date (penddate) — dateitme — NOT NULL — 사업 종료 시점
  • Project Normal Ticket (pnormalticket) — int — NOT NULL — 일반 티켓 수
  • Project Special Ticket (pspecialticket) — int — NOT NULL — 특수 티켓 수
  • Project Google Drive (pgoogledrive) — varchar — 구글 드라이브
  • Project Delete (pdelete) — int — NOT NULL — 0: 삭제되지 않음, 1: 삭제
  • Project Days Week (pdaysweek) — varchar — 가능 요일

ProjectPage

  • Project Page ID (ppid) — int — Primary key — NOT NULL — 사업 페이지 ID
  • Project Page Name (ppname) — varchar(128) — NOT NULL — 사업 페이지 이름
  • Project Page Description (ppdescription) — text — NOT NULL — 사업 페이지 설명
  • Project Page URL (ppurl) — varchar(512) — NOT NULL — 사업 페이지 링크
  • Project Page Thumbnail (ppthumbnail) — varchar(128) — NOT NULL — 사업 페이지 썸네일 링크

Topic

  • Topic ID (tid) — int — Primary key — NOT NULL — 토픽 ID
  • Topic Name (tname) — varchar(128) — NOT NULL — 토픽명
  • Topic Description (tdescription) — text — NOT NULL — 토픽 설명
  • Topic Active (tactive) — int — NOT NULL — 0: 비활성, 1: 활성
  • Topic Type (ttype) — int — NOT NULL — 0: 내부, 1: 외부

TopicProject

  • TopicProject ID (tpid) — int — Primary key — NOT NULL — 아젠다-사업 ID
  • Porject ID (pid) — int — Foreign key → Project.Porject ID — NOT NULL — 사업 ID
  • Topic ID (tid) — int — Foreign key → Topic.Topic ID — NOT NULL — 토픽 ID
  • TopicProject Active (tpactive) — int — NOT NULL — 토픽 활성화

User

  • User ID (uid) — int — Primary key — NOT NULL — 유저 ID
  • User Email (uemail) — varchar(128) — NOT NULL — 유저 이메일
  • User Password (upassword) — varchar — NOT NULL — 유저 비밀번호
  • User Phone Number (uphone) — varchar — 유저 연락처
  • User Type (utype) — int — NOT NULL — 0: 슈퍼유저 계정, 1: 관리자 계정, 2:컨설턴트 계정, 3:기업계정
  • User Active (uactive) — int — NOT NULL — 0: 비활성유저, 1활성유저
  • User Delete (udelete) — int — NOT NULL — 0: 삭제되지 않음, 1: 삭제