The "Lots of tables, or lots of rows" dilemma
Ok you experienced database people,
I have emmbarked on an online productivity application using PHP and MySql. I've developed my own lightweight framework, designed a full template for the UI and now I'm just about to jump in and start connectiong the front end with the database.
Basically this site will have users who each have their own tasks,projects,files and notes. I hope we will get in the 1000+ user range, so I'm going to plan for that. My question is whether I should make a seperate table for each user and keep their tasks,notes, and projects in the table pertaining to them OR keep all the items in one big table and just associate each item by user ID. As of right now this is the option I am going with, it makes more sense to me and is going to be easier to implement. But my only worry is when there gets to be 100,000 rows in this table that searches and stuff will get very slow.
Each task,note,file,project has the same columns. Each one just has a different label pretty much and is handled differently within the application.
I will definitely use InnoDB for this one large table, so I don't have table locking slowing it down.
So any input on this matter would be great! I have been developing with php and mysql for about 5 years now, I'm just still not a database design guru.