Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need to implement basic personalized user functionality for my website. Is there a standard structure for databases of this type? Like is it common practice to have all user info and data in a single table with each user having his own row, or should this info be split among different tables and linked together (maybe for efficiency?) I'm not tremendously concerned with security at this point, but I'll obviously want to have password encryption before too long.

I tried to find what I was looking for on google, but to no avail. Just let me know if the question needs further clarification or anything.

share|improve this question

3 Answers 3

up vote 8 down vote accepted

I suggest that you use typical normalization standards. One user per row.

  • User ID (incrementing bigint)
  • User Common Name (to be displayed on the site)
  • User Email Address
  • Password Salt (Unique for every user, inserted when the account is created)
  • Password (Hashed with the salt - MD5 or SHA1, your preference)
  • Date Account Was Created

The rest is up to you given your business rules.

share|improve this answer
5  
bigint? I understand building to scale but is this website going to have over 2.1 million users? –  SqlSandwiches Jun 28 '11 at 20:25
    
With respect to hashing MD5 and SHA1 are pretty worthless given the amount of processing power available now, I would recommend using bcrypt instead. –  BenM Aug 8 '11 at 21:01
    
BenM - That's why I recommend a unique salt for each user. Someone who wants the password would need to built a rainbow table in both MD5 and SHA1 for each salt and crack one user at a time. Sure, it can be done, but it will be very time consuming. –  Josh Bond Aug 10 '11 at 17:00

Going to have to go for a big old fashion "it depends".

Of course you are going to need some sort of key on this table. You could start off with a UserID. This could just be a incrementing INT (or bigint if you are going to have over 2.1 million users).

I've seen lots of databases also use GUIDs as the main UserID. But that's opening a whole different can of worms using GUIDs for PKs.

You then need to decide how normalized you want your database. Are you going to allow your user to have multiple emails? multiple phone numbers? If so, they should be in a different table.

I'd keep the main user table to:

  • some sort of ID or PK you can use
  • First Name/Last Name or just a username
  • some sort of status of the user (active, disabled, etc) - (tinyint linking to a status table)
  • created date

That should be your starting point.

From there you could add other columns based on what you want to store. Email could link to an email table, address to an address table, etc. Password could use hash+salt but have you considered openids?

I HIGHLY recommend you read this article though - http://www.sqlservercentral.com/articles/data-modeling/71725/

share|improve this answer

Check out this answer where the poster goes through each field and discusses what you might want to do with the field. This can help you work out if you need to have tables linked for flexibility for business requirements, or reporting.

Best praticies on common person fields (Name, email, address, geneder etc...)?

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.