Which is better for storing favorites, multiple tables or one big table in MySQL?
|
01-26-2013, 12:36 AM
Post: #1
|
|||
|
|||
Which is better for storing favorites, multiple tables or one big table in MySQL?
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 Ads |
|||
01-26-2013, 12:44 AM
Post: #2
|
|||
|
|||
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. Ads |
|||
01-26-2013, 12:44 AM
Post: #3
|
|||
|
|||
Option 2. Make sure you index the user and/or the favoriteditem columns to help improve searches
|
|||
« Next Oldest | Next Newest »
|
User(s) browsing this thread: 1 Guest(s)