Jump to content

Database Diagram


gmc1103
Go to solution Solved by Barand,

Recommended Posts

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

post-174418-0-95680200-1484843646_thumb.jpg

Edited by gmc1103
Link to comment
Share on other sites

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 

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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 by gmc1103
Link to comment
Share on other sites

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

post-174418-0-15282100-1484861435_thumb.png

Link to comment
Share on other sites

 

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 by Barand
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.