CODEWITHSUNDEEP
mysqlsqldatabase-designdata-modelingone-to-many
Prix
Prix

Reputation: 19528

Multiple data stored into a field for better management?

I have a MySQL table as follow:

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

I was thinking of using the reason as an int field and store just the id of the reason that could be selected by the user and the reason itself could be increased by the admin.

What I meant by increased is that the admin could register new reason, for example currently we have:

Flood, Racism, Hacks, Other

But the admin could add a new reason for instance:

Refund

Now my problem is that I would like to allow my users to select multiple reasons, for example:

The report 01 have the reasons Flood and Hack.

How should I store the reason field so that I could select multiple reasons while maintaining a good table format?

Should I just go ahead and store it as a string and cast it as an INT when I am searching thru it or there are better forms to store it?

UPDATE Based on Jonathan's reply:

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

Upvotes: 0

Views: 281

Answers (2)

Jonathan Leffler
Jonathan Leffler

Reputation: 753525

The normalized solution is to have a second table containing one row for each reason:

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

(Assuming your main table is called MainTable and you have a table defining valid reason codes called Reasons.)

From a comment:

[W]ould you be [so] kind [as] to show me an example of selecting something to retrieve a report's reason? I mean if I simple select it SELECT * FROM MainTABLE I would never get any reasons since MainTable doesnt know it right? Because it is only linked to the MainReasons and Reasons table so I would need to do something like SELECT * FROM MainTable LEFT JOIN MainReasons USING (MainTable_ID) or something alike but how would I go about getting all the reasons if multiples?

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

This will return one row per reason - so it would return multiple rows for a single report (recorded in what I called MainTable). I omitted the reason ID number from the results - you can include it if you wish.

You can add criteria to the query, adding terms to a WHERE clause. If you want to see the reports where a specific reason is specified:

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'

(You don't need the reason in the results - you know what it is.)

If you want to see the reports where 'Floods' and 'Hacks' were the reasons given, then you can write:

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

Upvotes: 2

dpmattingly
dpmattingly

Reputation: 1321

To do a one-to-many relationship, I would spin reason off into it's own table, like so:

id, parent_id, reason

parent_id would refer back into your current table's id.

You could store it as an INT, but it would be a continual pain to have to parse it every time you wanted to read the data. This way would just take one more join.

Upvotes: 1

Related Questions