Блог

У меня есть таблица MySQL следующим образом:

 id, user, culprit, reason, status, ts_register, ts_update
  

Я думал использовать причину в качестве поля int и хранить только идентификатор причины, который может быть выбран пользователем, а сама причина может быть увеличена администратором.

Что я имел в виду под увеличением, так это то, что администратор мог зарегистрировать новую причину, например, в настоящее время у нас есть:

 Flood, Racism, Hacks, Other
  

Но администратор мог бы добавить новую причину, например:

 Refund
  

Теперь моя проблема в том, что я хотел бы разрешить своим пользователям выбирать несколько причин, например:

В отчете 01 указаны причины флуда и взлома.

Как я должен хранить поле причины, чтобы я мог выбрать несколько причин, сохраняя при этом хороший формат таблицы?

Должен ли я просто сохранить его в виде строки и преобразовать его в качестве INT при поиске по нему или есть лучшие формы для его хранения?

ОБНОВЛЕНИЕ на основе ответа Джонатана:

 SELECT mt.*, group_concat(r.reason separator ', ') AS reason
  FROM MainTable AS mt
  JOIN MainReason AS mr ON mt.id = mr.maintable_ID
  JOIN Reasons AS r ON mr.reason = r.reason_id
  GROUP BY mt.id
  

Нормализованное решение — иметь вторую таблицу, содержащую по одной строке для каждой причины:

 CREATE TABLE MainReasons
(
    MainTable_ID INTEGER NOT NULL REFERENCES MainTable(ID),
    Reason       INTEGER NOT NULL REFERENCES Reasons(ID),
    PRIMARY KEY(MainTable_ID, Reason)
);
  

(Предполагая, что ваша основная таблица называется MainTable и у вас есть таблица, определяющая допустимые коды причин, называемые Reasons.)

Из комментария:

[Не будете ли вы [так] любезны [как] показать мне пример выбора чего-либо для извлечения причины отчета? Я имею в виду, что если я просто выберу его SELECT * ИЗ MainTable, я никогда не получу никаких причин, поскольку MainTable не знает этого правильно? Потому что это связано только с таблицей основных причин и обоснований, поэтому мне нужно было бы сделать что-то вроде SELECT * FROM MainTable LEFT JOIN MainReasons USING (MainTable_ID) или что-то подобное, но как мне получить все причины, если они кратны?

 SELECT mt.*, r.reason
  FROM MainTable AS mt
  JOIN MainReason AS mr ON mt.id = mr.maintable_ID
  JOIN Reasons AS r ON mr.reason = r.reason_id
  

Это вернет одну строку для каждой причины — таким образом, это вернет несколько строк для одного отчета (записанного в том, что я назвал MainTable). Я опустил идентификационный номер причины в результатах — вы можете включить его, если хотите.

Вы можете добавить критерии к запросу, добавив термины в предложение WHERE. Если вы хотите просмотреть отчеты, в которых указана конкретная причина:

 SELECT mt.*
  FROM MainTable AS mt
  JOIN MainReason AS mr ON mt.id = mr.maintable_ID
  JOIN Reasons AS r ON mr.reason = r.reason_id
 WHERE r.reason = 'Flood'
  

(Вам не нужна причина в результатах — вы знаете, что это такое.)

Если вы хотите просмотреть отчеты, в которых были указаны причины «наводнений» и «взломов», тогда вы можете написать:

 SELECT mt.*
  FROM MainTable AS mt
  JOIN (SELECT f.MainTable_ID
          FROM (SELECT MainTable_ID
                  FROM MainReason AS mr1
                  JOIN Reasons    AS r1  ON mr1.reason = r1.reason_ID
                 WHERE r1.reason = 'Floods'
               ) AS f ON f.MainTable_ID = mt.MainTable_ID
  JOIN (SELECT f.MainTable_ID
          FROM (SELECT MainTable_ID
                  FROM MainReason AS mr2
                  JOIN Reasons    AS r2  ON mr2.reason = r2.reason_ID
                 WHERE r1.reason = 'Hacks'
               ) AS h ON h.MainTable_ID = mt.MainTable_ID
  

Чтобы установить связь «один ко многим», я бы выделил reason в его собственную таблицу, вот так:

 id, parent_id, reason
  

parent_id будет ссылаться обратно на идентификатор вашей текущей таблицы.

Вы могли бы сохранить его как INT, но было бы постоянной проблемой разбирать его каждый раз, когда вы хотели прочитать данные. Этот способ просто потребовал бы еще одного объединения.