teachers students database design

Question

Let's say you have students

and you have teachers

Students log in to the site using a different interface than teachers

Students can do the following on the site - Look up grades - Email teachers

Teachers can do the following on the site - Lookup up students - Input grades

How would you design the database table to allow teachers and students to log in? This question confuses me because I was thinking of doing the following

of having a teacher table
of having a student table

two separate tables

so

create table teacher ( name varchar(255), email varchar(100), password varchar(100) )

create table student ( name varchar(255), email varchar(100), password varchar(100) )

Is that how it is usually done? When you have two different entities logging in to two different login interfaces?

--- EDIT ---

also what if the two entities have different fields? Would you still create one Users table with a "Role" in that case.

For example, what if you have the following:

(notice how teacher and student have a few fields different. Would you still create one table to put them both??)

create table teacher ( name varchar(255), email varchar(100), password varchar(100) num_of_students int,

)

create table student ( name varchar(255), email varchar(100), password varchar(100), gpa decimal(10,2) )

Picking up on @Fatal's answer: create table user ( name varchar(255), email varchar(100), password varchar(100), role varchar(45)) — Jorge Guberte
@Jorge I would not save the role by its name inside the User table. I would have a User, Role and User_Role table. There is a N:M relationship between User and Role entity. This is what you basically have in every app with role-based security. — Fabian Barney
@Fatal Good to know. I have to study database schemas more in depth. — Jorge Guberte

JonH JonH · Accepted Answer · 2011-10-26T17:31:03

I would consider just making it one table and add a TypeID, where TypeID would classify the type of person be it a student or a teacher.

Take a look at the design of both of those tables, they are exactly the same, this should be a signal to you that you may be overdoing things.

By keeping it in 2 separate tables, it is more UI work for you and more administrative work for the person entering the data.

By combining it into one table and introducing a classification or type field you eliminate all that extra work.

CREATE TABLE Users ( ID int, Type int, name varchar(255), email varchar(100))

I also would not store the password as plain text, as it appears you may be hinting at with password varchar(100). Type in this case could be an int or even a bit datatype if the only two types of personnel will be Students and Teachers. The field could be IsTeacher with 1 being a teacher and 0 being a student. But in most cases, you end up realizing you needed a different type of User, hence the int datatype.

teachers students database design

5 Answers

Entities and Relationship

Solution