I need to store multiple id's in either a field in the table or add another table to store the id's in.
Each member will basically have favourite articles. Each article has an id which is stored when the user clicks on a Add to favourites button.
My question is:
Do I create a field and in this field add the multiple id's or do I create a table to add those id's?
What is the best way to do this?
This is a many-to-many relationship, you need an additional table storing pairs of user_id and article_id (primary keys of user and article tables, respectively).
You should create a new table instead of having comma seperated values in a single column.
Keep your database normalized.
You create a separate table, this is how things work in a relational database. The other solution (comma separated list of ids in one column) will lead to an unmaintainable database. For example, what if you want to know how many times an article was favorited? You cannot write queries on a column like this.
Your table will need to store the user's id and the article's id - these refer to the primary keys of the corresponding tables. For querying, you can either use JOINs or nested SELECT queries.
As lafor already pointed out this is a many-to-many relationship and you'll end up with three tables: user, article, and favorite:
CREATE TABLE user(
id INT NOT NULL,
...
PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE article (
id INT NOT NULL,
...
PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE favorite (
userID INT NOT NULL,
articleID INT NOT NULL,
FOREIGN KEY (userID) REFERENCES user(id) ON DELETE CASCADE,
FOREIGN KEY (articleID) REFERENCES article(id) ON DELETE CASCADE,
PRIMARY KEY (userID, articleID)
) ENGINE=INNODB;
If you then want to select all user's favorite articles you use a JOIN:
SELECT * FROM favorite f JOIN article a ON f.articleID = a.id WHERE f.userID = ?
If you want to know why you should use this schema, I recommend reading about database normilization. With multiple IDs in a single field you would even violate the first normal form and thus land in a world of pain...
