Database with users design

Question

I am in database design development phase. Application will work with large number of users (LARGE :))

I designed 80% of database but I have one Users table which is connected to everything else:

Users {UserId, FirstName, LastName, Username, Password, PasswordQuestion, PasswordAnswer, Gender, RoleId, LastLoginDate etc etc}

I saw asp.net membership database structure where Users and Membership are two tables. My questions are:

Should I use one users table with all users data in it or more tables?
If answer is 'more tables', what tables to use? Any advice on how to structure relation between those tables?

This is sample relation that I have, and trying to improve. I don't understand why user and userChild are separated tables?

John: Something like that. Mitch: Sorry I didn't read FAQ, now I understand that I need to build reputation. I will fix that. And I updated question with one new data. — 1110
– 1110, Commented Dec 26, 2010 at 11:47

cherouvim · Accepted Answer · 2010-12-25 22:13:22Z

1

How many tables you need depends on your modeling needs.

For example if you include the RoleId in the Users table then you'll only be able to assign one role per user. Is that what you want?

Otherwise, if you have a separate table linking users to roles, you'll be able to assign more than one role per user.

answered Dec 25, 2010 at 22:13

cherouvim

32k15 gold badges107 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

1110 Over a year ago

No. Every user can have only one role. I am creating something where user can register, login, users can browse another users to add them as friends (I have Friend table with UserId and FriendId columns and both columns are connected to UserId from Users table). A also think to create separate table for secure questions because there will be list of questions. But I don't know do I, and should I have Users table that will store user personal data like FirsName, LastName etc. and another table that will store data like email, username, password, avatarUrl etc.

cherouvim Over a year ago

If a user has a single firstname and a single email then these two bits of data should usually go in the same table: Users.

Collectives™ on Stack Overflow

Database with users design

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related