Save a list of user ids to a mysql table

Question

I need to save a list of user ids who viewed a page, streamed a song and / or downloaded it. What I do with the list is add to it and show it. I don't really need to save more info than that, and I came up with two solutions. Which one is better, or is there an even better solution I missed:

1. The KISS solution - 1 table with the primary key the song id and a text field for each of the three interactions above (view, download, stream) in which there will be a comma separated list of user ids. Adding to it will be just a concatenation operation.

2. The "best practice" solution - Have 3 tables with the primary key the song id and a field of user id that did the interaction. Each row has one user id and I could add stuff like date and other stuff.

One thing that makes me lean towards options 2 is that it may be easier to check whether the user has already voted on a song?

---

tl;dr version - Is it better to use a text field to save arrays as comma separated values, or have each item in the array in a separate table row.

Answer 1

Definitely the 2nd:

• You'll be able to scale your application as it grows
• It will be less programming language dependent
• You'll be able to make queries faster and cleaner
• It will be less painful for any other programmer coding / debugging your application later

Additionally, I'd add a new table called "operations" with their ID, so you can add different operations if you need later, storing the operation ID instead of a string on each row ("view", "download", "stream").

Answer 2

It's definitely better to have each item in a separate row. Manipulating text fields has performance disadvantages by itself. But if ever you want to find out which songs user 1234 has viewed/listened to/etc., you'd have to do something like

SELECT * FROM songactions WHERE userlist LIKE '%,1234,%' OR userlist LIKE '1234,%' OR userlist LIKE '%,1234' OR userlist='1234';

It'd be just horribly, horribly painful.