I am developing a web application using mysql. I want to make sure I go down the right track here.
My web-app revolves around people creating lists and then working with the data in those lists. Each list will typically be between 200 and 2000 rows, and each user will typically create 1-50 lists. All of the lists will share the same structure, something like: [int | varchar(50) | varchar(50) | text]
I'm wandering if it is better to
1) create a new table for each list. OR
2) create a very long table for all of the lists' data, and add a listID field to each row specifying which list the entry belongs to.
What are the pros and cons of having lots of tables, versus having one very long table.
One table will be far better. It doesn't matter much how much data is in it as proper indexes will ensure it's fast to access. It will be much easier to write code using a single table and much easier to manage. I can't think of any advantages of having just one table - perhaps it's easier to set user permissions but in reality this will be handled by the web front end.
Are you sure a user will create up to 50 lists containing 2000 records? that's 100,000 records - even my wife doesn't give me lists like that. What does the system do?
Thanks for the replies. By long I meant vertically long, the table would only need 4 or 5 columns. My list sizes are certainly worst-cases, but I'm guessing that doesn't really matter here. (The application is for an online learning tool so the lists might be vocabulary lists for example.)
Yes, always use fewer tables where possible. You should never have two tables storing similar data, it's much more difficult to work with, and an indexed table will provide fast access to millions of rows. One suggestion would be...if you have two fields 'listid' and 'itemid' representing which list it is and which item in it, make that your primary key ('listid','itemid') and set the table to innodb, so it keeps the data stored together on disk in that order. I do not believe MyISAM has this feature.
And when the new MySQL is finally stable, you can use table partitioning, if you have a lot of disk access due to lack of memory.
Thanks for the advice. I've implemented the db using a single table and I'll look into coupling the listId and itemID as you suggest. But in the mean time all is working and my site is up and running at www.vocaber.com.