Most efficient way to login when multiple tables exist
Hello everyone, sorry for the dull title. Couldn't think of anything better. So this is my problem.
I was using a registration form for teachers and one for students. There are ten fields in each, first name, last name, username, password, etc being similar whereas departments (teachers have 7, students have 3) and email ids (teachers have a different domain extension and students different) being different. I am using two tables, one for teachers and one for student.
Now, I know I can use one login form, query against these two tables using the JOIN property and make them login. But I want to know if this method is efficient because two tables are being used.
Or is this method more efficient: In the reg form, I put a radio button to select teacher or student and depending on what one chooses the remaining fields change, i.e, if I choose teacher, 7 departments show up or if I choose student only 3 of them show up. In this way, I have to use one table only, atleast I guess so.
Or another possibility is I use two tables but two login forms, one for teacher and one for student, I know this is the easy way out but being a Comp Sci student I want to choose the efficient method of all.
P.S: The teacher's role is going to be completely different from the student's in the site. And there will be lot of activities for the teachers and students, they are completely different. Will maintaining one table be a good bet?
I would do it this way:
Use one user table with a column to identify the role (student or teacher).
And eliminate the departments from the users table, and create a new user_departments table (with two columns: userid and departmentid). That way, if in the future the number of departments changes, there will be no need to change the DB structure.
Also, having a different domain extension for the emails is no reason to have separate tables, is it? In the email column you can store any email address.
Having one or two tables has nothing to do with the number of registration forms. You could have one table and two registration forms, or two tables and one registration form.
In this particular case you can use two static registration forms because you ask different things from students and teachers, or one dynamically created form that shows different fields depending on if you are registering a teacher or a student. In the end, all registration data will be saved in the same user table (and the departments in the user_department table).
But the login script is one, and you'll use the role column value to decide if the user is a teacher or a student. Idem for the difference in tasks and logic in the rest of the application. Just use the role column value to decide what to show and what to do.