Table design for user's information as well as login credentials?

Question

Initially I would like to ask you to forget about hashing passwords or w/e related to passwords, this question is not related to securing passwords, etc and I do know/understand how this should be done.

What is the best approach to store the data in question, considering performance on read/write - to build one or more tables?

Single table, for example:

Table users: id, username, password, hash, email, group, access, address, phone, parents, ts_created, ts_update

Multiple tables, for example:

Table users: id, username, password, hash, email, group, access, ts_created, ts_update

Table user's information: id, user_id, address, phone, parents, ts_created, ts_update

What if your user's information fields may grow along the time - how should you deal with it ?

For example new fields: birthday_date, comments, situation

Will having 2 tables be slower on queries than having a single table ?

If having multiple tables in this case is only for maintaining a good design with separated data, does that mean it is not useful at all for performance reasons ?

If you want real sql examples let me know and I will scrap something to update this.

Answer 1

You may need even more tables depending on the data your going to store:

1. What if you use a password policy in the future where a user cannot re-use a previously used password?
2. Can a user have more than one email?
3. Can a user belong to more than one group?
4. Can a user have more than one phone number?
5. Only one parent? Or two? Is the parent in the system? What information about the parent do you store?

Storing thinks like this may be worth to store in its own individual table, which means that in the future it should be a lot easier to maintain. You need to think of how the system will change. As for performance, as already outlined it shouldn't be a problem as long as you create the correct indexes, and use the database correctly.

Answer 2

Your multiple-table design looks sensible - one table contains data about the user, the other about the person; if you only need user data (e.g. for checking access rights), person data are irrelevant.

The new fields you propose would probably go into the person table as new columns.

Using 2 (or more) tables and joining them together won't slow you down significantly - it may even improve performance (with good indexing - a unique index on user_id would be a good start here):

• on SELECT, the speed difference will be negligible
• on INSERT/UPDATE, this will be better than a single table in most situations (e.g. if the "users" table has many reads, writes on persons won't block them - whereas with one table, it might happen)

Also, personally I find it much easier (both in code and in db administration) to work with two narrower tables than with a single wide table.