link user table with other three tables

10y938SQL

I need to find the way of how to make relationship between user table which uses for saving user name and password for login and (student, staff and admin) tables which are driven from as the login correct the related page will shown for user type as student, staff or admin

please help of how to build database tables correctly

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Manas Mohapatra10y ago
Accepted answer
Create a table called tblUsers having following columns:

UserID - integer(auto increment)
Username - varchar(10)
Password -varchar(10)
FirstName -varchar(10)
LastName -varchar(10)
Email -varchar(10)
UserType - tinyint

UserID is auto increment and primary key too. UserType is type of tinyint which holds user type(Student, Staff, Admin or Others). You need to store 1 for Admin, 2 for Staff, 3 for Student, 4 for others..In your code behind you can declare an enum to check the values for UserType.
+1
kalu singh rao10y ago
Dear Joma Alrzini

It is very easy to create this relationship between all tables.

First you create UserTable where you have following columns.
User Table schema
UserID Primary Key int not null Auto-Generate
UserName nvarchar(50)
Password nvarchar(50)
Student Table schema
StudentID Primary Key int not null Auto Generate
UserID Foreign Key int not null
StudentName nvarchar(50)
Staff Table Schema
StaffID Primary Key int not null Auto Generate
UserID Foreign Key int not null
StaffName nvarchar(50)
Admin Table Staff
AdminD Primary Key int not null Auto Generate
UserID Foreign Key int not null
StudentName nvarchar(50)
I hope you would like you may be understand it.
Any query free to ask me, I am here for help.
+1
gaurav pal10y ago
Steps to handle the secenario.
1- Make one column in User Table as UserType.
2- Insert UserType as (student,staff and admin) when you register a user or we can say when you insert data in user table.
Or
you can make a master table for UserType and using Joins get the data(Optional)
3-Next step when you authenticate user or check user for login that time, according to UserType redirect related page.
Please let me know if you face issues.
+1