db_project

Tables (10)

diary

  • diary_id — INT — Primary key — NOT NULL
  • title — VARCHAR(200)
  • content — TEXT
  • visibility — ENUM('private', 'members')
  • written_at — DATETIME
  • study_member_id — INT — Foreign key → studyCP.member_id
  • INDEX — `study_member_id`

message

  • message_id — INT — Primary key — NOT NULL
  • content — TEXT
  • sent_at — DATETIME
  • study_member_id — INT — Foreign key → studyCP.member_id
  • INDEX — `study_member_id`

notice

  • notice_id — INT — Primary key — NOT NULL
  • title — VARCHAR(200)
  • content — TEXT
  • posted_at — DATETIME
  • study_id — INT — Foreign key → study.study_id
  • INDEX — `study_id`

review

  • review_id — INT — Primary key — NOT NULL
  • rating — INT
  • comment — TEXT
  • study_member_id — INT — Foreign key → studyCP.member_id
  • INDEX — `study_member_id`

study

  • study_id — INT — Primary key — NOT NULL
  • title — VARCHAR(200)
  • topic — VARCHAR(100)
  • region — VARCHAR(100)
  • max_members — INT
  • deadline — DATE
  • created_at — DATETIME
  • created_by — INT — Foreign key → user.user_id
  • INDEX — `created_by`

studyCP

  • member_id — INT — Primary key — NOT NULL
  • user_id — INT — Foreign key → user.user_id
  • study_id — INT — Foreign key → study.study_id
  • role — VARCHAR(50)
  • status — VARCHAR(20)
  • joined_at — DATETIME
  • INDEX — `user_id`
  • INDEX — `study_id`

studytag

  • study_id — INT(11) — Foreign key → study.study_id — NOT NULL
  • tag_id — INT(11) — Foreign key → tag.tag_id — NOT NULL
  • INDEX — `tag_id`

tag

  • tag_id — INT — Primary key — NOT NULL
  • name — VARCHAR(50)

user

  • user_id — INT — Primary key — NOT NULL
  • name — VARCHAR(100)
  • email — VARCHAR(100)
  • password — VARCHAR(100)
  • topic — VARCHAR(100)
  • region — VARCHAR(100)
  • time_slot — VARCHAR(50)
  • created_at — DATETIME
  • is_admin — INT

usertag

  • user_id — INT — Foreign key → user.user_id — NOT NULL
  • tag_id — INT — Foreign key → tag.tag_id — NOT NULL
  • INDEX — `tag_id`