I need some advice on storing data in mysql, where one needs to store more than one, let say userids for a single post?

Question

In cases when some one needs to store more than one value in a in a cell, what approach is more desirable and advisable, storing it with delimiters or glue and exploding it into an array later for processing in the server side language of choice, for example.

$returnedFromDB = "159|160|161|162|163|164|165"; $myIdArray = explode("|",$returnedFromDB);

or as a JSON or PHP serialized array, like this.

:6:{i:0;i:1;i:1;i:2;i:2;i:3;i:3;i:4;i:4;i:5;i:5;i:6;}

then later unserialize it into an array and work with it,

OR

have a new row for every new entry like this

postid 12 | showto 2
postid 12 | showto 3
postid 12 | showto 5
postid 12 | showto 6
postid 12 | showto 8

instead of postid 12 | showto "2|3|4|6|8|5|".
OR postid 12 | showto ":6:{i:0;i:2;i:1;i:3;i:2;i:3;i:3;i:4;i:4;i:5;i:5;i:6;}".

Thanks, looking forward to your opinions :D

Answer 1

Most of the time the recommendation is that many-to-many relationships (such as posts to users) should have a mapping table with 1 row for each post-user combination (in other words, your "new row for every new entry" version).

It's more optimal for things like join queries, and lets you retrieve only the data you need.

Answer 2

In cases when some one needs to store more than one value in a in a cell, what approach is more desirable and advisable, storing it with delimiters or glue and exploding it into an array later for processing in the server side language of choice, for example.

Neither. Oh goodness, neither! Edgar F. Codd is rolling in his grave right now.

Storing delimited data in a text field is no better than storing it in a flat file. The data becomes unqueryable. Storing PHP serialized data in a text field is even worse because then only PHP can parse the data.

You want a nice, happy, normalized database.

The thing you're trying to describe is a many-to-many relationship. Each user can maintain one or more posts. Likewise, each post can be maintained by one or more user. Right? Then something like this will work.

CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    ...
);

CREATE TABLE posts (
    post_id INTEGER PRIMARY KEY,
    ...
);
CREATE TABLE user_posts (
    user_id INTEGER REFERENCES users(user_id),
    post_id INTEGER REFERENCES posts(post_id),
    UNIQUE KEY(user_id, post_id)
);

-- All posts made by user 22.
SELECT posts.*
  FROM posts, user_posts
 WHERE user_posts.user_id = 22
   AND posts.post_id = user_posts.post_id

-- All users that worked on post 47
SELECT users.*
  FROM users, user_posts
 WHERE user_posts.post_id = 47
   AND users.user_id = user_posts.user_id

Answer 3

You should only serialize data in the DB if the data is never needed to be processed by the DB. For example, you could serialize user ID in the user_id field if you never need to do a query with the user_id field; e.g. never selecting anything based on user.

If these are posts (blog/news/etc. posts?) then I'm pretty confident you'll need to be able to query them by user. Normalizing the user into another table would serve you:

CREATE TABLE posts (post_id, ....);
CREATE TABLE post_users (post_id, user_id, ...);

You can then get the users in a different query, or use group_concat: SELECT post_id, GROUP_CONCAT(user_id) FROM posts JOIN post_users USING (post_id) GROUP BY post_id. When you need to show user name, just join to the users table to get their name in the group concat.

Answer 4

From RDBMS point of view i would 'have a new row for every new entry' Thats called m:n relationship table. You can then query the data however you like.

If you need postid 12 | showto ":6:{i:0;i:2;i:1;i:3;i:2;i:3;i:3;i:4;i:4;i:5;i:5;i:6;}". you can do

SELECT postid, CONCAT(':',count(showto),':{i:',GROUP_CONCAT(showto SEPARATOR ';i:'),';}') AS showto 
FROM tablename
GROUP BY postid

However if you only need the data in 1 form and not do any other kind of queries on that data then you may aswell store the string.