How to store an array into mysql?

Solution 1:

You may want to tackle this as follows:

CREATE TABLE comments (
    comment_id int, 
    body varchar(100), 
    PRIMARY KEY (comment_id)
);

CREATE TABLE users (
    user_id int, 
    username varchar(20), 
    PRIMARY KEY (user_id)
);

CREATE TABLE comments_votes (
    comment_id int, 
    user_id int, 
    vote_type int, 
    PRIMARY KEY (comment_id, user_id)
);

The composite primary key (comment_id, user_id) on the intersection table comments_votes will prevent users from voting multiple times on the same comments.

Let's insert some data in the above schema:

INSERT INTO comments VALUES (1, 'first comment');
INSERT INTO comments VALUES (2, 'second comment');
INSERT INTO comments VALUES (3, 'third comment');

INSERT INTO users VALUES (1, 'user_a');
INSERT INTO users VALUES (2, 'user_b');
INSERT INTO users VALUES (3, 'user_c');

Now let's add some votes for user 1:

INSERT INTO comments_votes VALUES (1, 1, 1);
INSERT INTO comments_votes VALUES (2, 1, 1);

The above means that user 1 gave a vote of type 1 on comments 1 and 2.

If the same user tries to vote again on one of those comments, the database will reject it:

INSERT INTO comments_votes VALUES (1, 1, 1);
ERROR 1062 (23000): Duplicate entry '1-1' for key 'PRIMARY'

If you will be using the InnoDB storage engine, it will also be wise to use foreign key constraints on the comment_id and user_id fields of the intersection table. However note that MyISAM, the default storage engine in MySQL, does not enforce foreign key constraints:

CREATE TABLE comments (
    comment_id int, 
    body varchar(100), 
    PRIMARY KEY (comment_id)
) ENGINE=INNODB;

CREATE TABLE users (
    user_id int, 
    username varchar(20), 
    PRIMARY KEY (user_id)
) ENGINE=INNODB;

CREATE TABLE comments_votes (
    comment_id int, 
    user_id int, 
    vote_type int, 
    PRIMARY KEY (comment_id, user_id),
    FOREIGN KEY (comment_id) REFERENCES comments (comment_id),
    FOREIGN KEY (user_id) REFERENCES users (user_id)
) ENGINE=INNODB;

These foreign keys guarantee that a row in comments_votes will never have a comment_id or user_id value that doesn't exist in the comments and users tables, respectively. Foreign keys aren't required to have a working relational database, but they are definitely essential to avoid broken relationships and orphan rows (ie. referential integrity).

In fact, referential integrity is something that would have been very difficult to enforce if you were to store serialized arrays into a single database field.

Solution 2:

You can always serialize the array and store that in the database.
PHP Serialize

You can then unserialize the array when needed.

Solution 3:

Consider normalizing the table structure into a comments, and a separate votes table.

Table "comments":

id
comment
user
...

Table "votes":

user_id  
comment_id
vote (downvote/upvote)

this would allow an unlimited number of votes without having to deal with the limits of a database field.

Also, you may have future needs for operations like "show all votes a user has cast", removing specific votes or limiting the maximum number of votes per day. These operations are dead easy and fast to implement with a normalized structure, and horribly slow and complex in a serialized array.

Solution 4:

you should have three tables: users, comments and comment_users.

comment_users has just two fields: fk_user_id and fk_comment_id

That way you can keep your performance up to a maximum :)

Solution 5:

I'd prefer to normalize your table structure more, something like;

COMMENTS
-------
id (pk)
title
comment
userId


USERS
-----
id (pk)
name
email


COMMENT_VOTE
------------
commentId (pk)
userId (pk)
rating (float)

Now it's easier to maintain! And MySQL only accept one vote per user and comment.