Database Diagram

[gmc1103]

Hi

 

I'm starting to make a new database wand i would like to have opinions about this implementation

This is a reservation system for a school

Admin can manage the database (add users, equipment, time, classroom)

Teacher can make a reservation of an equipment(datashow)

Non teacher, deliver the equipment in a classroom and in the end collect him 

Check the image to follow

 

So i have 8 tables

Tipo is (user type (admin, teacher, non teacher)

Utilzador is (Users)

Utilizador_Tipo (where i put all the user and type)

 

Equipamento (equipment(datashow, pc,tv, etc)

Sala (classroom)

Horas(Time)

Reserva(Reservation, needs , a date, the user_id, the classroom:id, the time_id, the equip_id)

Reserva_Detail(when the non teacher deliver and collect the equipment)

 

So this how i did it, 1º approach of course

 

This is my sql

-- MySQL Script generated by MySQL Workbench
-- 01/19/17 16:43:39
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

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 requisicao
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema requisicao
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `requisicao` DEFAULT CHARACTER SET utf8 ;
USE `requisicao` ;

-- -----------------------------------------------------
-- Table `requisicao`.`Utilizadores`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `requisicao`.`Utilizadores` (
  `id_utilizador` INT NOT NULL AUTO_INCREMENT,
  `nome` VARCHAR(100) NOT NULL,
  `email` VARCHAR(45) NOT NULL,
  `telefone` INT(9) NOT NULL,
  `pass` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id_utilizador`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `requisicao`.`Tipo`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `requisicao`.`Tipo` (
  `id_tipo` INT NOT NULL AUTO_INCREMENT,
  `tipo` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id_tipo`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `requisicao`.`Sala`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `requisicao`.`Sala` (
  `id_sala` INT NOT NULL AUTO_INCREMENT,
  `designacao` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id_sala`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `requisicao`.`Equipamento`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `requisicao`.`Equipamento` (
  `id_equipamento` INT NOT NULL AUTO_INCREMENT,
  `equipamento` VARCHAR(45) NOT NULL,
  `referencia` VARCHAR(45) NOT NULL,
  `estado` TINYINT(1) NOT NULL,
  PRIMARY KEY (`id_equipamento`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `requisicao`.`Horas`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `requisicao`.`Horas` (
  `id_horas` INT NOT NULL AUTO_INCREMENT,
  `inicio` TIME(5) NOT NULL,
  `fim` TIME(5) NOT NULL,
  PRIMARY KEY (`id_horas`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `requisicao`.`Reserva`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `requisicao`.`Reserva` (
  `id_reserva` INT NOT NULL AUTO_INCREMENT,
  `data` DATE NOT NULL,
  `id_user` INT(11) NOT NULL,
  `id_sala` INT(11) NOT NULL,
  `id_equip` INT(11) NOT NULL,
  `id_horas` INT(11) NOT NULL,
  PRIMARY KEY (`id_reserva`),
  INDEX `fk_user_idx` (`id_user` ASC),
  INDEX `fk_sala_idx` (`id_sala` ASC),
  INDEX `fk_equip_idx` (`id_equip` ASC),
  INDEX `fk_horas_idx` (`id_horas` ASC),
  CONSTRAINT `fk_user`
    FOREIGN KEY (`id_user`)
    REFERENCES `requisicao`.`Utilizadores` (`id_utilizador`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_sala`
    FOREIGN KEY (`id_sala`)
    REFERENCES `requisicao`.`Sala` (`id_sala`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_equip`
    FOREIGN KEY (`id_equip`)
    REFERENCES `requisicao`.`Equipamento` (`id_equipamento`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_horas`
    FOREIGN KEY (`id_horas`)
    REFERENCES `requisicao`.`Horas` (`id_horas`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `requisicao`.`Reserva_detail`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `requisicao`.`Reserva_detail` (
  `id_reserva_detail` INT NOT NULL AUTO_INCREMENT,
  `id_reserva` INT(11) NOT NULL,
  `entregue` DATETIME NULL,
  `devolvido` DATETIME NULL,
  PRIMARY KEY (`id_reserva_detail`),
  INDEX `fk_reserva_idx` (`id_reserva` ASC),
  CONSTRAINT `fk_reserva`
    FOREIGN KEY (`id_reserva`)
    REFERENCES `requisicao`.`Reserva` (`id_reserva`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `requisicao`.`Utilizador_Tipo`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `requisicao`.`Utilizador_Tipo` (
  `id_utilizador` INT NOT NULL,
  `id_tipo` INT NULL,
  INDEX `fk_utilizador_idx` (`id_utilizador` ASC),
  INDEX `fk_tipo_idx` (`id_tipo` ASC),
  CONSTRAINT `fk_utilizador`
    FOREIGN KEY (`id_utilizador`)
    REFERENCES `requisicao`.`Utilizadores` (`id_utilizador`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_tipo`
    FOREIGN KEY (`id_tipo`)
    REFERENCES `requisicao`.`Tipo` (`id_tipo`)
    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;

Any improvment?

 

Thanks

Edited January 19, 2017 by gmc1103

[benanamen]

On a quick review it looks like you did well. I pretty much never see that on a forum. I did notice you changed the id naming format in Reserva for the id_equip. I would add in the full name id_equipamento. it is the only column you didn't do it on.

Edited January 19, 2017 by benanamen

[Barand]

My only question is why are the times that the equipment is required stored in a separate "horas" table and not in the "reserva" table? And same question regarding "reserva_detail"?

[gmc1103]

Hi

 

Well, Horas table is because i nedd to keep the format and to use only times stored in the DB

id_horas  inicio    fim       
--------  --------  ----------
       1  08:15:00  09:00:00  
       2  09:00:00  09:45:00  
       3  10:00:00  10:45:00  
       4  10:45:00  11:30:00  
       5  11:40:00  12:25:00  
       6  12:25:00  13:10:00  
       7  13:20:00  14:05:00  
       8  14:05:00  14:50:00  
       9  15:00:00  15:45:00  
      10  15:45:00  16:30:00  
      11  16:45:00  17:30:00  
      12  17:30:00  18:15:00  
      13  18:45:00  19:30:00  
      14  19:30:00  20:15:00  
      15  20:15:00  21:00:00  
      16  21:00:00  21:45:00  
      17  22:00:00  22:45:00  
      18  22:45:00  23:30:00  

This is Horas table, with this table i can define id_horas in the reservas table and i can avoid someone insert wrong time like 08:10 instead of 08:15

Then i can use id to make my select

Example

Select COUNT(*) as total from reserva where id_horas = 1

About "reserva_detail" i want to:

after a reservation, the id is created in "reservas" table and in "reserva_detail" table,

• When the non teacher deliver the equipment the datetime is inserted into `entregue` field "reserva_detail" table
• When the non teacher collect the equipment after the class the datetime is inserted into `devolvido` field  "reserva_detail" table

 

I don't know if it is the best approach for doing this 

[Barand]

I did wonder if the times were predefined, as you have now confirmed. What if the equipment is required for two or more time periods?

[gmc1103]

Hi

 

What if the equipment is required for two or more time periods?

I understand that, i mean, for every time period i must have a new record for the same id like this

 

Id_reserva        data                  id_equip    id_horas   id_sala

1                       2017-01-18            1               1              1

1                       2017-01-18            1               2              1

1                       2017-01-18            1               3              1

 

To do this i need another table right?

[Barand]

right.

[gmc1103]

Ok

 

So i keep one table to be filled when the non teacher do his job(deliver and collect) and another one to keep all time period from one reservation

 

So can i do like 

 

Table Reserva_Status (id_reserva, delivered(datetime), collected(datetime))

Table Reserva_Details (id_reserva, id_horas, id_sala, id_equip)

 

Using this, i can remove from Reserva table (id_horas, id_sala, id_equip) and keep only (id_reserva, data)

 

correct?

Thanks for your help

Edited January 19, 2017 by gmc1103

[Barand]

The table you needed to add is the "reserva_hora" to give a many-to-many relationship between reserva and hora.

[gmc1103]

Thank you Barand

I didn't think this approach, i would like to know like you do, i still have a lot to learn

I have a stupid question, this is the first time i work with  MySQL Workbench

Why the table reserva_hora is green??

And my relations between table are different from yours..

 

I use usually sqlLyog and this one is new for me

 

The script is this one

-- MySQL Script generated by MySQL Workbench
-- 01/19/17 21:25:05
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

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 requisicao
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema requisicao
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `requisicao` DEFAULT CHARACTER SET utf8 ;
USE `requisicao` ;

-- -----------------------------------------------------
-- Table `requisicao`.`Utilizadores`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `requisicao`.`Utilizadores` (
  `id_utilizador` INT NOT NULL AUTO_INCREMENT,
  `nome` VARCHAR(100) NOT NULL,
  `email` VARCHAR(45) NOT NULL,
  `telefone` INT(9) NOT NULL,
  `pass` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id_utilizador`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `requisicao`.`Tipo`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `requisicao`.`Tipo` (
  `id_tipo` INT NOT NULL AUTO_INCREMENT,
  `tipo` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id_tipo`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `requisicao`.`Sala`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `requisicao`.`Sala` (
  `id_sala` INT NOT NULL AUTO_INCREMENT,
  `designacao` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id_sala`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `requisicao`.`Equipamento`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `requisicao`.`Equipamento` (
  `id_equipamento` INT NOT NULL AUTO_INCREMENT,
  `equipamento` VARCHAR(45) NOT NULL,
  `referencia` VARCHAR(45) NOT NULL,
  `estado` TINYINT(1) NOT NULL,
  PRIMARY KEY (`id_equipamento`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `requisicao`.`Horas`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `requisicao`.`Horas` (
  `id_horas` INT NOT NULL AUTO_INCREMENT,
  `inicio` TIME(5) NOT NULL,
  PRIMARY KEY (`id_horas`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `requisicao`.`Reserva`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `requisicao`.`Reserva` (
  `id_reserva` INT NOT NULL AUTO_INCREMENT,
  `data` DATE NOT NULL,
  `id_user` INT(11) NOT NULL,
  `id_sala` INT(11) NOT NULL,
  `id_equip` INT(11) NOT NULL,
  PRIMARY KEY (`id_reserva`, `id_user`),
  INDEX `fk_user_idx` (`id_user` ASC),
  INDEX `fk_sala_idx` (`id_sala` ASC),
  INDEX `fk_equip_idx` (`id_equip` ASC),
  CONSTRAINT `fk_user`
    FOREIGN KEY (`id_user`)
    REFERENCES `requisicao`.`Utilizadores` (`id_utilizador`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_sala`
    FOREIGN KEY (`id_sala`)
    REFERENCES `requisicao`.`Sala` (`id_sala`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_equip`
    FOREIGN KEY (`id_equip`)
    REFERENCES `requisicao`.`Equipamento` (`id_equipamento`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `requisicao`.`Reserva_detail`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `requisicao`.`Reserva_detail` (
  `id_reserva_detail` INT NOT NULL AUTO_INCREMENT,
  `id_reserva` INT(11) NOT NULL,
  `entregue` DATETIME NULL,
  `devolvido` DATETIME NULL,
  PRIMARY KEY (`id_reserva_detail`),
  INDEX `fk_reserva_idx` (`id_reserva` ASC),
  CONSTRAINT `fk_reserva`
    FOREIGN KEY (`id_reserva`)
    REFERENCES `requisicao`.`Reserva` (`id_reserva`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `requisicao`.`Utilizador_Tipo`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `requisicao`.`Utilizador_Tipo` (
  `id_utilizador` INT NOT NULL,
  `id_tipo` INT NULL,
  INDEX `fk_utilizador_idx` (`id_utilizador` ASC),
  INDEX `fk_tipo_idx` (`id_tipo` ASC),
  CONSTRAINT `fk_utilizador`
    FOREIGN KEY (`id_utilizador`)
    REFERENCES `requisicao`.`Utilizadores` (`id_utilizador`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_tipo`
    FOREIGN KEY (`id_tipo`)
    REFERENCES `requisicao`.`Tipo` (`id_tipo`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `requisicao`.`Reserva_Hora`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `requisicao`.`Reserva_Hora` (
  `id_reserva_hora` INT NOT NULL AUTO_INCREMENT,
  `id_reserva` INT NOT NULL,
  `id_horas` INT NOT NULL,
  PRIMARY KEY (`id_reserva_hora`),
  INDEX `fk_id_reserva_idx` (`id_reserva` ASC),
  INDEX `fk_id_horas_idx` (`id_horas` ASC),
  CONSTRAINT `fk_id_reserva`
    FOREIGN KEY (`id_reserva`)
    REFERENCES `requisicao`.`Reserva` (`id_reserva`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_id_horas`
    FOREIGN KEY (`id_horas`)
    REFERENCES `requisicao`.`Horas` (`id_horas`)
    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;

Sorry for any inconvinience

[Barand]

 

Why the table reserva_hora is green??

I altered its color property just to hilight it.

 

 

And my relations between table are different from* yours

You can change the style in the "model" menu

 

EDIT: * how refreshing to see someone correctly use "different from" instead "different than".

Edited January 19, 2017 by Barand

[gmc1103]

Ok.

 

Thank you again Barand for everything.

I'm gonna open another post in php help regarding a problem i have in another work, if you can read and help me, i would appreciate,

Again, many thanks

Best regards