Which is better for storing favorites, multiple tables or one big table in MySQL?

[Pistol]

Which is better for giving the website visitor the ability to add favorites like in Youtube or Twitter (follows)

Option 1: Making a table for each user with the column:
rowID
favoriteditem

Option 2: Have one big table with these columns
rowID
user
favoriteditem

rowID is the primary key column

[Steven V]

On a Small scale (10,000 users or less), having multiple rows Option 2 works. Option 1 would not be advisable ever because it would be really difficult to maintain (backup, restore, tune)

On a large scale(100,000+ users), you are really started to talk about semi-structured data and a NoSQL database is best. However, there is a workaround in MySQL which would be to use the XML field type.

You could create a table that looked like:
ID (this is the primary key of the table)
USER_ID (this is an integer that references a USER table which also as ID as it's Primary Key)
favorite_xml (this is the full list of favorite_xml)

The favorite_xml could be something like this:
<favorites>
<favorite url="<url1>" />
<favorite url="<url2>" />
<favorite url="<url3>" />
<favorite url="<url4>" />
</favorites>

However, if you want to have folders:

<favorites>
<folder name="Folder1" >
<favorite url="<url1>" description="<description1>" />
<favorite url="<url2>" />
</folder>
<folder name="Folder2" >
<favorite url="<url3>" />
<favorite url="<url4>" />
</folder>
<!-- uncategorized -->
<favorite url="<url5>" />
</favorites>

You may also want to read about JSON, which is superseding XML, but MySQL doesn't support natively.

[Naturally Sleep]

Option 2. Make sure you index the user and/or the favoriteditem columns to help improve searches