OMP ERD

Office Hour Management Platform

Tables (13)

ArrangedSchedule

  • ArrangedSchedule ID (sid) — int — Primary key — NOT NULL
  • RegularOfficeHour Consultant Schedule ID (cid) — int — Foreign key → RegularOfficeHourSchedule.RegularOfficeHour Consultant Schedule ID — NOT NULL
  • Company User ID (uid) — int — Foreign key → User.User ID — NOT NULL
  • User ID (uid3) — int — Foreign key → RegularOfficeHourSchedule.User ID — NOT NULL
  • Consultant Pre-comment (precomment) — text
  • Consultant Report (report) — text
  • Attachment (attachment) — varchar(128)
  • Arranged Schedule (aschedule) — datetime — NOT NULL
  • Arranged Schedule Status (astatus) — int — NOT NULL — 0: Arranged, 1: Met, 2: Finished, 3 ;Canceled
  • Billing Date (bilingdate) — datetime — 세금계산서 발행일자
  • Rejection Message (rejectmessage) — text

Banner

    Project

    • Porject ID (pid) — int — Primary key — NOT NULL
    • User ID (uid) — int — Foreign key → User.User ID — NOT NULL — 담당 관리자
    • 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

    ProjectPage

    • Project Page ID (ppid) — int — Primary key — NOT NULL
    • Project Page Name (ppname) — varchar(128) — NOT NULL
    • Project Page URL (ppurl) — varchar(512) — NOT NULL
    • Project Page Thumbnail (ppthumbnail) — varchar(128)

    RegularOfficeHour

    • Regular Office Hour ID (rid) — int — Primary key — NOT NULL
    • Regular Office Hour Name (rname) — varchar(128) — NOT NULL
    • Regular Office Hour Start Date (rstartdate) — datetime — NOT NULL
    • Regular Office Hour End Date (renddate) — datetime — NOT NULL
    • Regular Office Hour Status (rstatus) — int — NOT NULL — 0: Pending, 1: On-going, 2: Done
    • Regular Office Hour Type (rtype) — int — NOT NULL — 0: Regular, 1: Irregular

    RegularOfficeHourConsultant

    • Regular Office Hour-User ID (ruid) — int — Primary key — NOT NULL
    • Regular Office Hour ID (rid) — int — Foreign key → RegularOfficeHour.Regular Office Hour ID — NOT NULL
    • Porject ID (pid) — int — Foreign key → Project.Porject ID — NOT NULL
    • User ID (uid2) — int — Foreign key → User.User ID — NOT NULL
    • User ID (uid3) — int — Foreign key → Project.User ID — NOT NULL

    RegularOfficeHourProject

    • Regular Office Hour - Project ID (rpid) — int — Primary key — NOT NULL
    • Regular Office Hour ID (rid) — int — Foreign key → RegularOfficeHour.Regular Office Hour ID — NOT NULL
    • Porject ID (pid) — int — Foreign key → Project.Porject ID — NOT NULL
    • User ID (uid) — int — Foreign key → Project.User ID — NOT NULL

    RegularOfficeHourSchedule

    • RegularOfficeHour Consultant Schedule ID (cid) — int — Primary key — NOT NULL
    • Regular Office Hour ID (rid) — int — Foreign key → RegularOfficeHour.Regular Office Hour ID — NOT NULL
    • Porject ID (pid) — int — Foreign key → RegularOfficeHourConsultant.Porject ID — NOT NULL
    • User ID (uid) — int — Foreign key → RegularOfficeHourConsultant.User ID — NOT NULL
    • Topic ID (tid) — int — Foreign key → Topic.Topic ID — NOT NULL
    • User ID (uid3) — int — Foreign key → RegularOfficeHourConsultant.User ID — NOT NULL
    • Open Schedule (cschedule) — datetime — NOT NULL

    RegularOfficeHourTopic

    • RegularOfficeHourTopic ID (rtid) — int — Primary key — NOT NULL
    • Regular Office Hour ID (rid) — int — Foreign key → RegularOfficeHourProject.Regular Office Hour ID — NOT NULL
    • Porject ID (pid) — int — Foreign key → RegularOfficeHourProject.Porject ID — NOT NULL
    • Topic ID (tid) — int — Foreign key → Topic.Topic ID — NOT NULL
    • User ID (uid) — int — Foreign key → RegularOfficeHourProject.User ID — NOT NULL

    Topic

    • Topic ID (tid) — int — Primary key — NOT NULL
    • Topic Name (tname) — varchar(128)

    User

    • User ID (uid) — int — Primary key — NOT NULL
    • User Email (uemail) — varchar(128) — NOT NULL
    • User Name (uname) — varchar(128) — NOT NULL
    • User Phone Number (uphone) — varchar(11)
    • User Type (utype) — int — NOT NULL — 0: 슈퍼유저 계정, 1: 관리자 계정, 2:컨설턴트 계정, 3:기업계정

    UserDetail

    • User ID (uid) — int — Foreign key → User.User ID — NOT NULL

    UserProject

    • User-Project ID (upid) — int — Primary key — NOT NULL
    • User ID (uid) — int — Foreign key → User.User ID — NOT NULL
    • Porject ID (pid) — int — Foreign key → Project.Porject ID — NOT NULL
    • User ID (uid2) — int — Foreign key → Project.User ID — NOT NULL
    • Normal Tickets (nticket) — int — NOT NULL
    • Special Tickets (sticket) — int — NOT NULL