CODEWITHSUNDEEP
database-design
user24790
user24790

Reputation: 41

Database design for different kinds of userprofiles in SQL Server

I have a web application where I have 2 kinds of users: teachers and others. I have created a usertable where the accountinfo resides. with accountinfo, I mean emailaddress, username, password, lastactivitydate etc. Each user has a profile and a profile contains data like age, aboutme, address etc, so this info is in nature different from what is in the user table, that's why I have a separate UserProfile table with a FK of UserId pointing back to the user table.

I was quite happy with this design till I realized that the profile for a teacher has more fields then for others (the others may get additional fields that differ from those of teachers also in the future). For a teacher, I need for example his school etc. So then I would have an additional TeacherProfile table containing the extra data. So that would mean that I would do a join between 3 tables to get all the data for a teacher: User, BaseUserProfile, TeacherProfile. So I am wondering if it was a good idea to separate the base userprofile data to a separate table, of should I merge that table with the User table? What other options are there for this problem?

Upvotes: 0

Views: 152

Answers (1)

oleksii
oleksii

Reputation: 35905

2 kinds of users

These are usually called roles. So you would normally have these tables:

  • tblUser
    • UserId (PK)
    • RoleId (FK)
    • Email
    • LastLoggedIn
    • PasswordHash
    • Salt
  • tblUserProfile
    • UserProfileId (PK)
    • UserId (FK)
    • Contains columns for both teachers and students, some values will be NULL for students
  • tblUserRole
    • RoleId (PK)
    • RoleName

I understand that this is likely to be a learning task, but even there start using good security practices - do not store passwords (encrypted or not) at all. Instead store secure hash of the password and salt used to generate the hash.

Upvotes: 1

Related Questions