Different types of users in one table, How to add data off different users

Question

I have 5 type of users:

• Super Admin
• Coordinator
• Supervisor
• Student
• External Examiner

All of them have some common attributes like ID, name, email etc. I have inserted them in one table user differentiating them with user_type attribute.

I have also made different tables for each user like Coordinator, Student because each user have different attributes.

My Question is:

if I want to add a coordinator user into table, I can add it into user table with user_type=Coordinator but how will I link this coordinator from user table into 'coordinator' table?

Can anyone provide me with PHP-MySQL code for it?

Answer 1

Here is the table structure

CREATE TABLE `users`
( `userid` INT NOT NULL AUTO_INCREMENT, 
  `user_name` VARCHAR(50) NOT NULL,
   user_type int 1,
  `email` VARCHAR(100) NOT NULL, 
   PRIMARY KEY (`userid`) 
); 

CREATE TABLE `user_type`
( `user_typeid` INT NOT NULL AUTO_INCREMENT,
  `user_type` VARCHAR(50),
   PRIMARY KEY (`user_typeid`) 
); 

CREATE TABLE `tbl_corordinator`
( `coordinator_id` INT NOT NULL AUTO_INCREMENT,
   user_id int 1,
  `field1` VARCHAR(100) NOT NULL, 
   PRIMARY KEY (`coordinator_id`)
);

When you will add 'Coordinator' users in users table, you need to add information in tbl_corordinator & you will add userid (which is the primary key of users table.)

Thus using this foreign key concept, you can achieve this relation.