I am creating a new website. I have made some other sites in the past, and I have come to realize that my database creating skills suck..Horribly..and I need help.. Are there any tips that you can give me that will help me in creating a database that wont lag horribly after a lot of data is in it, and a lot of users are accessing the site? Would it be better to have as many tables as possible for different data things? Or would it be better to have as few tables as possible? Thanks for your help!
It is best to normalize the data and to make use of indexes to join tables together. For example: table users user_id username ... table messages message_id user_id message You then index user_id so that if you're doing searches on it that it's much faster. It's obviously slightly slower than putting the user info right into the messages and not doing any join at all for example. But it's a heck of a lot easier to maintain as an application grows.
Thats what I usually do, but I am not sure if that is the most efficient way. My partner and I did that with advercash.net, and now with over 100k users, it gets kinda slow at times.
Well at some point the application may need it's own mysql server or better hardware. At a certain point you cannot make things more efficient. The most common ways besides mysql optimization is to start caching pages that don't change often into static content. This way you avoid the mysql server all together.