Storing multiple data in one field (storing data in an array in database)

Question

I have a table called user_thoughts. The table has many columns, one of them being favourited_by.

A thought may be favourited by many different users, but I don't want to create a new row stating that this thought id has been favourited by this user.

I would rather have it that it stores multiple username's in one field. So favourited_by for example can hold data like this:

Alice, Fred, Freddy, Conor ....

All in one single row. I have tried messing around with the data types on phpMyAdmin but cannot figure out how the field can hold multiple data.

Bad idea, you should probably have a separate table with individual rows. But if you are going to do it anyway maybe json_encode(). — AbraCadaver
– AbraCadaver, Commented Mar 7, 2016 at 19:28
You are so going to regret the above decision. Asking the community for help with this equals asking people on the street how to best run blindfolded with scissors in your hand. — PeeHaa
– PeeHaa, Commented Mar 7, 2016 at 19:31
To elaborate on what @PeeHaa said, you really should research database normalization and go that route instead of trying to do all of that into one column. — mituw16
– mituw16, Commented Mar 7, 2016 at 19:32
There are several reasons why this is less than ideal. I mean if you just want to display a list somewhere saying "a, b, c favorited this" then it's probably not that bad. But you will have a hard time using the data for anything else other than this. You can't search the data (you are suck using LIKE causing a scan of all rows). You can't get a list of "thoughts" that a specific user favorited (same as previous point) or even a count of favorites. And you can't easily update the list without modifying the whole string (alphabetical, removing..etc). It just ends up being a headache. — Jonathan Kuhn
– Jonathan Kuhn, Commented Mar 7, 2016 at 19:46

Siphon · Accepted Answer · 2016-03-07 20:06:41Z

What you're asking is the wrong way to do this. You should not serialize the favorites data into a text field for either the user table or the thought table. This destroys the whole purpose of using a relational database like MySQL.

The right way to do this: create a cross-reference table between the user table and the thought table. This utilizes a many-to-many table to store a list of favorites using the primary keys of a thought row and a user row.

Your new favorite table:

CREATE TABLE IF NOT EXISTS `favorite` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `thought_id` int NOT NULL,
  PRIMARY KEY (`id`)
);

What this does is take the id from the user table and store it in favorite.user_id, then stores the id from the thought table in favorite.thought_id.

To add a new favorite for the user with the id of 123, for the thought with id 456:

INSERT INTO favorite (user_id, thought_id) VALUES ('123', '456');

Get the users that have marked the thought with id 456 as their favorite (using a JOIN):

SELECT u.* FROM favorite AS f
  JOIN user AS u ON u.id = f.user_id
  WHERE f.thought_id = 456;

And similar to the last query, get the favorite thoughts for the user with id 123:

SELECT t.* FROM favorite AS f
  JOIN thought AS t ON t.id = f.thought_id
  WHERE f.user_id = 123;

DanielM · Accepted Answer · 2016-03-07 19:31:49Z

0

The ideal way to handle this is to map it to another table, however you can just store it as json.

MySQL 5.7 even includes JSON as a data type allowing easier filtering and manipulation.

https://dev.mysql.com/doc/refman/5.7/en/json.html

You can put json into any text field however if you don't need to search it, etc.

answered Mar 7, 2016 at 19:31

DanielM

6,7262 gold badges42 silver badges61 bronze badges

Collectives™ on Stack Overflow

Storing multiple data in one field (storing data in an array in database)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related