How to store multiple data for one object in another table?

Question

MySQL

There is a table with recorded tickets. For each ticket, it is possible to reassign the assigned moderator. I don't know how to properly store such data. In theory, making a request by ticket id, I should also receive the history of the moderators who worked on it.
As far as I understand, you need to somehow link the tables, but how and what to do then?

Answer 1

Write the id of the appointed moderator to the table with tickets
. Write the date of the change of the appointed moderator and related information to the *_history table (well, or * _log) - what moderator was, what became, what comments can be. This can be done both at the application level and at the database level. In the case of a database, you can use triggers.
Something like this structure

CREATE TABLE `ticket` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `assignee_id` int DEFAULT NULL,
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  `subject` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `body` text COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `ticket_assignment_log` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `ticket_id` int unsigned NOT NULL,
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `old_assignee_id` int DEFAULT NULL,
  `new_assignee_id` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `i_ticket` (`ticket_id`),
  CONSTRAINT `fk_ticket` FOREIGN KEY (`ticket_id`) REFERENCES `ticket` (`id`) ON UPDATE CASCADE
)