0

I am new to the database design so I have to check my data model is correct way to handle the problem or not.

Problem Statement: I am designing the database for student portal which have following user roles

  1. Administrator
  2. School(Principal)
  3. Teacher
  4. Student

The functionalities is for phase I is just maintain all users data and provide the login system.

I am thinking about the database table are

-------------   -------------   -----------
Schools            Teachers       Students
-------------   -------------   -----------
id                id              id
name              name            name
other info        other info      other info

And I am decided to create the another table which store the login creadintilas and role with id as below

Users Table

--------------------------------------------
username    password      role      user_id
--------------------------------------------

but I am not sure about how to applying the PK/FK relations on the table. I want create the same login system for all system because all the users shares some features i.e. teachers,student,schools can update the student info. Questions

  1. Is this right way of thinking?

  2. Can i do it in another better way?

1

Here are two different possible solutions.

Solution 1: enter image description here

Solution 2: enter image description here

For both models, Users is a parent table, with different tables subclassing it. This is achieved by the primary key of the child tables also being a foreign key to the Users' primary key.

Solution 1 involves having a list of possible user roles in the Roles table (Administrator, School, Teacher, Student) with the User having a reference to a role.

Solution 2 involves deriving the idea of the User's role from which child table the User belongs to. This would mean that a row belonging to Users must also belong to one of the child tables.

I personally like the first solution better, since it separates the idea of a Users' role in the portal from the User's position within the school environment.

As an additional note, I would look into "salted password encryption" for storing passwords, especially if this database is for a real application.

  • edit removed the UserRoles table from Solution 1, it wasn't necessary. – Joe Borysko May 1 '15 at 17:58
  • if we remove the userRoles then how can we defined which type of user is loggedIn – siddhesh May 2 '15 at 5:30
  • Sir, I would like to tell you what i concluded from your data model. After checking the user creadintials I have to also check the 4 tables for matching the id itself. I think first solution is best if there is userRoles column. why are you saying me to remove the userRoles table – siddhesh May 2 '15 at 5:34
  • When I first posted the answer, I had a UsersRole associative table between Users and Roles in solution 1. I quickly realized that didn't make sense, because it's indicating that many Users have many Roles, when really Users only have one Role. That's why I made that comment. – Joe Borysko May 2 '15 at 17:50
  • To answer your question of how you would check which type of user is logged in, it would simply be "Select idRole FROM Role Users;" – Joe Borysko May 2 '15 at 17:52
0

Another alternative is to combine the teachers and students in a single table.

Add a flag or attribute to identify who is a teacher or a student.

This has the advantage of allowing a single user to change from one to the other or even be both at the same time.

  • If you do it thi way, you can't have FKs that target only Students or only Teachers - which you probably need for a school database. – ypercubeᵀᴹ May 1 '15 at 9:01
  • Student/Teacher relationship is almost always many:many so that could be held in a mapping table. To simplify things you could create views for students and teachers if it helps conceptually. – Sir Swears-a-lot May 1 '15 at 9:06
  • That's what I was commenting about. If you have students and teachers in the same table, how will you define the foreign keys from the mapping table (and from any other table that wants to reference Students or Teachers)? I don't know of any DBMS that supports FKs that reference a view. – ypercubeᵀᴹ May 1 '15 at 9:08
  • Yeah, fair call. I guess youd have to rely on application logic. – Sir Swears-a-lot May 1 '15 at 9:10
  • I guess you could still have one login but map thier details to 2 tables if they are both a student and teacher. Thinking in terms of an ER diagram. I was thinking of a person as a single entity with different attributes rather 1 person being 2 entities. not sure if i explained that very well. – Sir Swears-a-lot May 1 '15 at 9:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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