feat: Create de ERD for data metrics for host information [Data Monitoring]

[JuanSolisCTJ13]

Summary 💡

Create an ERD model to define a DB client that persists information from the other DB from the Booking System.

Acceptance Criteria ✅

• [ ] All input elements are considered.
• [ ] All keys are considered.
• [ ] All tables are connected

[nazlonga]

@JuanSolisCTJ13 remember to keep the structure of issues, the first word is feat/chore/fix/enhacement For now I edited to feat

[neanlos]

This is the ERD for User and the script for postgreSQL @nazlonga @larispardo

Script:

`CREATE TABLE "Genders" ( "id" int, "name" string, "data_created" timestamp, PRIMARY KEY ("id") );

CREATE TABLE "User Types" ( "id" int, "name" string, "date_created" timestamp, PRIMARY KEY ("id") );

CREATE TABLE "Types of identification" ( "id" int, "name" string, "identification" string, "date_created" timestamp, PRIMARY KEY ("id") );

CREATE TABLE "User" ( "id" int, "id_type_of_identification" int, "dni" String, "first_name" string, "second_name" string, "first_surname" string, "second_surname" string, "birthdate" timestamp, "gender" string, "email" string, "phone_number" int, "id_address" int, "id_emergency_contact" int, "status" int, "idiomas" String, "intro" String, "url_image" string, "id_user_type" int, "date_created" timestamp, PRIMARY KEY ("id"), CONSTRAINT "FK_User.gender" FOREIGN KEY ("gender") REFERENCES "Genders"("id"), CONSTRAINT "FK_User.id_user_type" FOREIGN KEY ("id_user_type") REFERENCES "User Types"("id"), CONSTRAINT "FK_User.id_type_of_identification" FOREIGN KEY ("id_type_of_identification") REFERENCES "Types of identification"("id") );

CREATE TABLE "Status" ( "id" int, "id_user" int, "status_name" String, "data_created" timestamp, PRIMARY KEY ("id"), CONSTRAINT "FK_Status.id_user" FOREIGN KEY ("id_user") REFERENCES "User"("id") );

CREATE TABLE "Contries" ( "id" int, "name" string, "data_created" timestamp, PRIMARY KEY ("id") );

CREATE TABLE "Cities" ( "id" int, "name" string, "id_country" int, "data_created" timestamp, PRIMARY KEY ("id"), CONSTRAINT "FK_Cities.id_country" FOREIGN KEY ("id_country") REFERENCES "Contries"("id") );

CREATE TABLE "<> status_name " ( "verified" , "active" , "deleted" , "banned" );

CREATE TABLE "Favorite Places List" ( "id" int, "list_name" string, "id_user" int, "data_created" timestamp, PRIMARY KEY ("id"), CONSTRAINT "FK_Favorite Places List.id_user" FOREIGN KEY ("id_user") REFERENCES "User"("id") );

CREATE TABLE "Places" ( "id" int, "name" string, PRIMARY KEY ("id") );

CREATE TABLE "Favorite Place" ( "id" int, "id_places" int, "id_favorite_places_list" Type, PRIMARY KEY ("id"), CONSTRAINT "FK_Favorite Place.id_favorite_places_list" FOREIGN KEY ("id_favorite_places_list") REFERENCES "Favorite Places List"("id"), CONSTRAINT "FK_Favorite Place.id_places" FOREIGN KEY ("id_places") REFERENCES "Places"("id") );

CREATE TABLE "Address" ( "id" int, "address" string, "state" string, "zip" string, "id_city" int, "date_created" timestamp, PRIMARY KEY ("id"), CONSTRAINT "FK_Address.id" FOREIGN KEY ("id") REFERENCES "User"("id_address"), CONSTRAINT "FK_Address.id_city" FOREIGN KEY ("id_city") REFERENCES "Cities"("id") );

CREATE TABLE "<> user_type_name " ( "registered" , "host" , "admin" );

CREATE TABLE "USER_ACCOUNTLESS" ( "uid" int, "start_timestamp" timestamp, "end_timestamp" timestamp, PRIMARY KEY ("uid") );

CREATE TABLE "Emergency Contact" ( "uid" int, "name" string, "telephone_number" int, "data_created" timestamp, PRIMARY KEY ("uid") );

CREATE TABLE "<> types_of_DNI_name " ( "citizenship id" , "foreigner id" , "passport" , "..." );

`

[neanlos]

This is the ERD for Place and Booking and the script for MySQL @nazlonga @larispardo

SQL Script

-- MySQL Script generated by MySQL Workbench -- 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='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

---

-- Schema place_booking

---

---

-- Schema place_booking

---

CREATE SCHEMA IF NOT EXISTS place_booking DEFAULT CHARACTER SET utf8 ; USE place_booking ;

---

-- Table place_booking.address

---

CREATE TABLE IF NOT EXISTS place_booking.address ( id INT NOT NULL, address VARCHAR(45) NULL, id_city VARCHAR(45) NULL, zip INT NULL, latitude DOUBLE NULL, longitud DOUBLE NULL, created_date TIMESTAMP NULL, PRIMARY KEY (id)) ENGINE = InnoDB;

---

-- Table place_booking.user

---

CREATE TABLE IF NOT EXISTS place_booking.user ( id INT NOT NULL, PRIMARY KEY (id)) ENGINE = InnoDB;

---

-- Table place_booking.place

---

CREATE TABLE IF NOT EXISTS place_booking.place ( id INT NOT NULL, user_id INT NOT NULL, address_id INT NOT NULL, type VARCHAR(45) NULL, price_per_night DOUBLE NULL, name VARCHAR(45) NULL, is_active TINYINT NULL, created_date TIMESTAMP NULL, PRIMARY KEY (id, user_id, address_id), INDEX fk_place_address1_idx (address_id ASC) VISIBLE, INDEX fk_place_user1_idx (user_id ASC) VISIBLE, CONSTRAINT fk_place_address1 FOREIGN KEY (address_id) REFERENCES place_booking.address (id) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT fk_place_user1 FOREIGN KEY (user_id) REFERENCES place_booking.user (id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

---

-- Table place_booking.booking

---

CREATE TABLE IF NOT EXISTS place_booking.booking ( id INT NOT NULL, place_id INT NOT NULL, user_id INT NOT NULL, number_of_guests INT NULL, check_in_date TIMESTAMP NULL, check_out_date TIMESTAMP NULL, charged_comission DOUBLE NULL, is_canceled TINYINT NULL, created_date TIMESTAMP NULL, PRIMARY KEY (id, place_id, user_id), INDEX fk_booking_place1_idx (place_id ASC) VISIBLE, INDEX fk_booking_user1_idx (user_id ASC) VISIBLE, CONSTRAINT fk_booking_place1 FOREIGN KEY (place_id) REFERENCES place_booking.place (id) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT fk_booking_user1 FOREIGN KEY (user_id) REFERENCES place_booking.user (id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

---

-- Table place_booking.review

---

CREATE TABLE IF NOT EXISTS place_booking.review ( id INT NOT NULL, user_id INT NOT NULL, place_id INT NOT NULL, review TEXT NULL, rating INT NULL, created_date TIMESTAMP NULL, PRIMARY KEY (id, user_id, place_id), INDEX fk_review_place1_idx (place_id ASC) VISIBLE, INDEX fk_review_user1_idx (user_id ASC) VISIBLE, CONSTRAINT fk_review_place1 FOREIGN KEY (place_id) REFERENCES place_booking.place (id) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT fk_review_user1 FOREIGN KEY (user_id) REFERENCES place_booking.user (id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

---

-- Table place_booking.image

---

CREATE TABLE IF NOT EXISTS place_booking.image ( id INT NOT NULL, place_id INT NOT NULL, url VARCHAR(45) NULL, is_active TINYINT NULL, PRIMARY KEY (id, place_id), INDEX fk_images_place_idx (place_id ASC) VISIBLE, CONSTRAINT fk_images_place FOREIGN KEY (place_id) REFERENCES place_booking.place (id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

---

-- Table place_booking.perk

---

CREATE TABLE IF NOT EXISTS place_booking.perk ( id INT NOT NULL, place_id INT NOT NULL, name VARCHAR(45) NULL, description TEXT NULL, is_active TINYINT NULL, PRIMARY KEY (id, place_id), INDEX fk_perks_place1_idx (place_id ASC) VISIBLE, CONSTRAINT fk_perks_place1 FOREIGN KEY (place_id) REFERENCES place_booking.place (id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

---

-- Table place_booking.space

---

CREATE TABLE IF NOT EXISTS place_booking.space ( id INT NOT NULL, name VARCHAR(45) NULL, is_active TINYINT NULL, PRIMARY KEY (id)) ENGINE = InnoDB;

---

-- Table place_booking.rule

---

CREATE TABLE IF NOT EXISTS place_booking.rule ( id INT NOT NULL, place_id INT NOT NULL, category VARCHAR(45) NULL, rule TEXT NULL, is_active TINYINT NULL, PRIMARY KEY (id, place_id), INDEX fk_rules_place1_idx (place_id ASC) VISIBLE, CONSTRAINT fk_rules_place1 FOREIGN KEY (place_id) REFERENCES place_booking.place (id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

---

-- Table place_booking.place_space

---

CREATE TABLE IF NOT EXISTS place_booking.place_space ( id VARCHAR(45) NOT NULL, place_id INT NOT NULL, space_id INT NOT NULL, quantity INT NULL, PRIMARY KEY (id, place_id, space_id), INDEX fk_place_space_place1_idx (place_id ASC) VISIBLE, INDEX fk_place_space_space1_idx (space_id ASC) VISIBLE, CONSTRAINT fk_place_space_place1 FOREIGN KEY (place_id) REFERENCES place_booking.place (id) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT fk_place_space_space1 FOREIGN KEY (space_id) REFERENCES place_booking.space (id) 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;