Newsletter Categories in one row like 1,2 - Mysql Simple Database Design

Question

Hi, I'am using a simple newsletter-script where different categories for one user are possible. But I want to get the different categories in one row like 1,2,3

The tables:

newsletter_emails

id  email          category
1   [email protected]    1
2   [email protected]    2

newsletter_categories

id  name
1   firstcategory
2   secondcategory

But what Iam looking for is like this:

newsletter_emails

user_id  email                category
1        [email protected]        1,2
2        [email protected]    1

what's the best solution for this?

PS: The User can select his own Categorys at the profile page. (maybe with Mysql Update?)

Answer 1

Try this (completely untested):

SELECT id AS user_id, email, GROUP_CONCAT(category) AS category FROM newsletter_emails GROUP BY email ORDER BY user_id ASC;

Answer 2

This is a many-to-many relationship case. Instead of having comma separated category ids make an associative table between newsletter_emails and newsletter_categories like user_category having the following schema:

user_id    category
1          1
1          2
2          1

This way you won't have to do string processing if a user unsubscribes from a category. You will just have to remove the row from the user_category table.

Answer 3

SQL and the relational data model aren't exactly made for this kind of thing. You can do either of the following:

• use a simple SELECT query on the first table, then in your consuming code, iterate over the result, fetching the corresponding rows from the second table and combining them into a string (how you'd do this exactly depends on the language you're using)

• use a JOIN on both tables, iterate over the result set and accumulate values from table 2 as long as the ID from table 1 remains the same. This is harder to code than the first solution, and the result set you're pulling from the DB is larger, but you'll get away with just one query.

• use DBMS-specific extensions to the SQL standard (e.g. GROUP_CONCAT) to achieve this. You'll get exactly what you asked for, but your SQL queries won't be as portable.