Chatting-Server-DB

Tables (3)

chat_participant

  • user_id — VARCHAR(15), NOT NULL — Primary key — NOT NULL
  • room_id — VARCHAR(36), PRIMARY KEY — Foreign key → chat_room.room_id — NOT NULL
  • joined_at — DATETIME DEFAULT CURRENT_TIMESTAMP
  • exited_at — DATETIME, NULL

chat_room

  • room_id — VARCHAR(36), PRIMARY KEY — Primary key — NOT NULL
  • created_at — DATETIME DEFAULT CURRENT_TIMESTAMP
  • deleted_at — DATETIME, NULL

message

  • message_id — BIGINT, AUTO_INCREMENT, PRIMARY KEY — Primary key — NOT NULL
  • sender_id — VARCHAR(15), NOT NULL, FOREIGN KEY (room_id, sender_id) REFERENCES chat_participant(room_id, user_id), INDEX idx_room_created (room_id, created_at DESC) — Foreign key → chat_participant.user_id — NOT NULL
  • room_id — VARCHAR(36), NOT NULL, FOREIGN KEY (room_id, sender_id) REFERENCES chat_participant(room_id, user_id), INDEX idx_room_created (room_id, created_at DESC) — Foreign key → chat_participant.room_id — NOT NULL
  • content — TEXT
  • created_at — DATETIME DEFAULT CURRENT_TIMESTAMP