+- Twitist Forums (http://twitist.com)
+-- Forum: Twitter forums (/forum-1.html)
+--- Forum: Twitter General help (/forum-6.html)
+--- Thread: Which is better for storing favorites, multiple tables or one big table in MySQL? (/thread-57641.html)
Which is better for storing favorites, multiple tables or one big table in MySQL? - Pistol - 01-26-2013 12:36 AM
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 - 01-26-2013 12:44 AM
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 - 01-26-2013 12:44 AM
Option 2. Make sure you index the user and/or the favoriteditem columns to help improve searches