I'm developing a Web app. It will have basic registrations procedures: user login and registration as well as managing various statistics for every user (such as number of logins, number of accesses to every sub-module and so on).
At first I decided to use one table to store all information about the user: login, pass, profile info and statistics. Now I'm thinking if it was a good decision. Because there will probably be a lot of failed logins (mistyped passwords and hacker attacks), maybe it's better to have 2 tables:
1) one for user login/pass
2) the other for profile info and statistics.
This way the performance load caused by failed logins will not affect the performance of users who logged in correctly. The two tables will be accessed independently.
Am I write? I'm new to Web App developement, so this may seem a dummy question I use MySQL, but maybe I should go to the DB design forum?
I'm implementing something similar to yours. I've a "login_trans" (for login transactions) and a "profiles" table. The "login_trans" table stores the username and a password hash (using a MD5 hex) and is used for authenticating logging in and related tasks.
Did you do this because of the reasons I mentioned? I just want to know if my reasoning regarding redistribution of computational load between the 2 tables is correct?
I'm just not sure if MySQL (and other DBMS) allows asynchronious access to diffenent tables?
I'm not particularly sure about the load part. But like you said, the possibility of failed logins is very real and I think it would be safer to put the data in different tables. Another reason I'm having the two tables is that I'm trying to group closely related items in a table of their own.
Just from what you've said here, it doesn't sound like you need to use two different tables. When your user tries to login, you are trying to retrieve a recordset (assuming you want the profile information as well). Returning an empty recordset (login failed) will not tie up your database. If you're concerned about the number of hits on the table, while I can't say for certain with MySQL, I would think it would allow asynchronous access for SELECT queries, but would lock for UPDATE and DELETE, which will not be the majority of your queries.