Writlaus

I have been dealing with mysql for a while now, but I still don't really know how exactly it works, or the most efficient way to do certain things. Is there some web page that explains how it works on a basic level?

One example is that I have one table for the users' accounts, with an id number, username, and password, and then I have other tables called "user_1","user_2", etc. that are created dynamically with the user's id number, when the account is created. These other tables hold more information about that user that seem to be more easily organized in their own tables. Does this impact the efficiency of the system? Will mysql be "bogged down" if it has too many tables?

titaniumdecoy

Jimbo

Yes, there is a level at which performance is affected by the number of tables; how much it's affected, I don't know. Your design is very inefficient though. You'd need a table to keep track of what tables you have; why not just keep the data all inside one table (or a small number of tables)?

__________________
<insert disclaimer here>
<insert shameless plug for Visual Studio here>

Writlaus

I forgot to mention that this was done on another project of mine a while ago, and now I have come to the same dilemma. This time I decided to ask if it was a smart thing to do.

Jimbo, the project I did that with was a poll website where it kept track of what specific users voted, so you could see things like how many people voted yes to poll A and poll B. I had the "user_n" tables basically just keep a record of all of their answers. I then had each poll record on the poll table keep a simple tally on the votes, as well as the ID of each user that voted.

And I've actually come up with a better way to do the project I'm currently working on, but I still can't think of a better way I could have done the poll thing. For curiosity's sake, can anyone think of a better way to do that?

titaniumdecoy

As I meant to suggest via the link in my last post, you can use database normalization techniques to brainstorm the most robust design for your database. I suggest you do some research on the subject.

As for your poll example, my solution would be: Create a users table (userid, username, etc.). Create a new table users_to_polls to associate each user with each poll they vote in (u2pid, userid, pollid, response). Create a new table for each poll (pollid, question, etc.).