Web Database – How to store user information

database-design

I'm starting to design a database to use in a web solution and would like to understand if there is any benefit to storing passwords (and the rest of the user information for that matter) in a separate table to the table with the user id.

Best Answer

The one benefit for storing passwords in a separate table, if you have a db that supports this, is you can use a security definer function to read the passwords, and this prevents sql injection or the like from reading it in other queries. Think of it as being like the /etc/shadow file on Linux. Of course this is in addition to salted hashes.

This being said I am of the opinion you should really avoid storing passwords in your database. The less of this sort of thing your app does the less you have to worry about security-wise. Outsource it to OpenID providers, etc. or if it is an intranet app, something like LDAP, AD, or the like.

Related Solutions

Questions about Database Design for a Web Application

100k records in a single non-partitioned table is a relatively trivial number. Nothing to worry about assuming you follow normalization guidelines and index appropriately.

Take a look at some example schemas to avoid schoolboy errors. Study sample databases, like the Microsoft examples for SQL Server. When you've created your schema, post a new question for feedback.

User system database design

Having the users and lost passwords in the same table implies a 1-1 (or 1-0/1) relationship; 1 user has 1 lost password (or none, if the column is nullable).

If that's the cardinality of the relationship, having them on the same table is correct. The school of thought that refuses nulls (which depending on the day, I adhere to), says that if the relationship is 1-0/1, you should:

users
  usersID primary key

lostPasswords
  usersID primary key references users(users_id)
  -- lost password columns here, non-nullable

The relationships look fine, however, I would suggest you use a diagramming tool which shows which are the columns referenced. Surprisingly, I've only found SchemaSpy doing this: http://schemaspy.sourceforge.net/sample/diagrams/summary/relationships.real.compact.png

Best Answer

Related Solutions

Questions about Database Design for a Web Application

User system database design

Related Question