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