Jump to content
gmc1103

Database Diagram

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

Share this post


Link to post
Share on other sites

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 by benanamen

Share this post


Link to post
Share on other sites

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"?

Share this post


Link to post
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 

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
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?

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

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

post-3105-0-68624800-1484859892_thumb.png

Share this post


Link to post
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

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.