connecting multiple profile tables to user table

Question

I've 3 profile tables:

-student -teacher -staff

All profile tables have different columns(few common columns). These tables must be connected user table, each staff, teacher, student must be having account in user table:

user table
-id
-login
-password
-etc.

What is the best ways of connecting profiles tables to user table.

I've found one solution:

Storing User Profile as properties and values in tables

*i.e. Table to store possible options, table to store user_id, option_id and value*

-No redundant data stored, all data is relevant
-Most normalised method
-Slower to retrieve and update data

updated

Below, I've created db as @user1032531 said.

Answer 1

Create a super table called "people". Put all attributes in this table that are common to students, teachers, and staff.

Then create a table for students, teachers, and staff where each table's primary key is a 1-to-1 to the people table, and put unique attributes in these tables.

-- MySQL Script generated by MySQL Workbench
-- 07/30/14 05:51:12
-- Model: New Model    Version: 1.0
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`people`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`people` (
  `idpeople` INT UNSIGNED NOT NULL,
  `name` VARCHAR(45) NULL,
  `birthday` DATE NULL,
  `sex` CHAR(1) NULL,
  PRIMARY KEY (`idpeople`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`teachers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`teachers` (
  `people_idpeople` INT UNSIGNED NOT NULL,
  `unique_column` VARCHAR(45) NULL,
  PRIMARY KEY (`people_idpeople`),
  CONSTRAINT `fk_teachers_people`
    FOREIGN KEY (`people_idpeople`)
    REFERENCES `mydb`.`people` (`idpeople`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`students`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`students` (
  `people_idpeople` INT UNSIGNED NOT NULL,
  `unique_column` VARCHAR(45) NULL,
  PRIMARY KEY (`people_idpeople`),
  CONSTRAINT `fk_students_people1`
    FOREIGN KEY (`people_idpeople`)
    REFERENCES `mydb`.`people` (`idpeople`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`staff`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`staff` (
  `people_idpeople` INT UNSIGNED NOT NULL,
  `unique_column` VARCHAR(45) NULL,
  PRIMARY KEY (`people_idpeople`),
  CONSTRAINT `fk_staff_people1`
    FOREIGN KEY (`people_idpeople`)
    REFERENCES `mydb`.`people` (`idpeople`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;