Create in MySQL Database a Table State_Vehicle

Question

Create in MySQL Database a Table State_Vehicle

Navigation

#1 by (0 votes)
#2 by (0 votes)
#3 by (0 votes)

0

I am doing a program for the administration of services of an auto mechanic workshop, in the there are tables like: Users, Clients, Staff, Services, Appointments, Vehicles and VEHICLE STATUS and a ternary table that could be called ServantService_RepVehi (do not pay attention to the names is just an example, the important thing is to get the idea)

The question here is in ESTADOdeVehiculo this table stores the data of the status of each of the vehicles when arriving at the workshop as if it has gasoline, it has all its rubbers, it is missing a rear-view mirror, it is used for air conditioning, it has all the lights, etc etc etc ... In this case, what is the right thing or more feasible? do the table with each of those fields or make a field something like TEXT so that everything is brought in a text format ??

In the design window in java I have the window with a table and several checkboxes to confirm whether or not each of the fields .. but I do not know how to store them correctly in the database ... if individually as varchar or boolean or how to text all together in a kind of report: S is something that I do not think the end use the people who manage a workshop but I think it would be a good idea to place those options: S

I hope you understand the question and excuse me if it is something confusing ..

Here is an image of how I have the database: v I have a question with the use of FOREIGN KEY Do you see it well? I will also place the code n.n

SQL Code:

     -- MySQL Workbench Forward Engineering Lo hice con ayuda de Workbench pero cambie la manera de las foraneas ya que como las coloca workbench no conozco muy bien el uso de los CONSTRAINS y ese poco de puntitos...

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

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE DATABASE IF NOT EXISTS 'tallermecanico_hd' DEFAULT CHARACTER SET utf8 ;
USE 'tallermecanico_hd' ;

-- -----------------------------------------------------
-- Table 'mydb'.'a_usuarios'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'tallermecanico_hd'.'a_usuarios' (
  'nombre_u' INT NOT NULL,
  'contra_u' VARCHAR(10) NOT NULL,
  'permiso' VARCHAR(15) NOT NULL,
  PRIMARY KEY ('nombre_u'))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'mydb'.'b_personal'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'tallermecanico_hd'.'b_personal' (
  'cedula_p' INT NOT NULL,
  'nombre_p' VARCHAR(25) NOT NULL,
  'apellido_p' VARCHAR(25) NULL,
  'telefono_p' INT NULL,
  'email_p' VARCHAR(45) NULL,
  'dir_p' VARCHAR(255) NULL,
  'cargo_p' VARCHAR(45) NULL,
  'f_contrato' DATE NULL,
  'status_p' VARCHAR(45) NULL,
  PRIMARY KEY ('cedula_p'))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'mydb'.'c_clientes'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'tallermecanico_hd'.'c_clientes' (
  'cedula_c' INT NOT NULL,
  'nombre_c' VARCHAR(25) NOT NULL,
  'apellido_c' VARCHAR(25) NULL,
  'telefono_c' INT NOT NULL,
  'dir_c' VARCHAR(255) NULL,
  'email_c' VARCHAR(45) NULL,
  'empresa_c' VARCHAR(45) NULL,
  PRIMARY KEY ('cedula_c'))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'mydb'.'d_servicios'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'tallermecanico_hd'.'d_servicios' (
  'cod_serv' INT NOT NULL,
  'nomb_serv' VARCHAR(45) NOT NULL,
  'descrip_serv' VARCHAR(255) NULL,
  PRIMARY KEY ('cod_serv'))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'mydb'.'e_vehiculos'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'tallermecanico_hd'.'e_vehiculos' (
  'placa' INT NOT NULL,
  'marca' VARCHAR(25) NULL,
  'modelo' VARCHAR(25) NULL,
  'anio' VARCHAR(4) NULL,
  'observ' VARCHAR(255) NULL,
  'serial' VARCHAR(25) NULL,
  'color' VARCHAR(25) NULL,
  'puertas' INT,
  'motor' VARCHAR(25) NULL,
  'transmision' VARCHAR(25) NULL,
  'cedula_c' INT NOT NULL,
  PRIMARY KEY ('placa'),
    FOREIGN KEY ('cedula_c')REFERENCES 'c_clientes' (cedula_c)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'mydb'.'f_citas'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'tallermecanico_hd'.'f_citas' (
  'id_cita' INT NOT NULL,
  'f_solicitud' DATETIME NULL,
  'f_pautada' DATETIME NULL,
  'falla_idk' VARCHAR(255) NULL,
  'observ_cita' VARCHAR(255) NULL,
  'placa' INT NOT NULL,
  PRIMARY KEY ('id_cita'),
    FOREIGN KEY ('placa') REFERENCES 'e_vehiculos'(placa)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'mydb'.'g_est_vehiculo'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'tallermecanico_hd'.'g_est_vehiculo' (
  'cod_estv' INT NOT NULL,
  'alarma' VARCHAR(5) NULL,
  'espejo_retrov' VARCHAR(5) NULL,
  'luces_int' VARCHAR(5) NULL,
  'cauchos' VARCHAR(5) NULL,
  'cauchorep' VARCHAR(5) NULL,
  'faros' VARCHAR(5) NULL,
  'obv_estv' VARCHAR(1000) NULL,
  'placa' INT NOT NULL,
  PRIMARY KEY ('cod_estv'),
    FOREIGN KEY ('placa')REFERENCES 'e_vehiculos' (placa)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'mydb'.'x_est_servicios'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'tallermecanico_hd'.'x_est_servicios' (
  'f_inicio' DATETIME NOT NULL,
  'f_fin' DATETIME NULL,
  'resultado_s' VARCHAR(1000) NULL,
  'est_result_s' VARCHAR(1000) NULL,
-- fk
  'placa' INT NOT NULL,
  'cod_serv' INT NOT NULL,
  'cedula_p' INT NOT NULL,

  PRIMARY KEY ('f_inicio','placa','cod_serv','cedula_p'),
    FOREIGN KEY ('placa')REFERENCES 'e_vehiculos' (placa)
    ON DELETE CASCADE
    ON UPDATE CASCADE,

    FOREIGN KEY ('cedula_p')REFERENCES 'b_personal' (cedula_p)
    ON DELETE CASCADE
    ON UPDATE CASCADE,

    FOREIGN KEY ('cod_serv')REFERENCES 'd_servicios'(cod_serv)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


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

sql java netbeans phpmyadmin

asked by HeckDan 18.05.2017 в 04:25

source

3 answers

Activity closes when selecting listview item Problem with carbon date

score 0 · Answer 1

My recommendation is that if you are going to need some statistical operation or you need to know something particular with the data that you propose of "gasoline", "rubbers", "mirrors", etc., then it is good to make a field for each data . If all you want to do is read the vehicle and deliver that data exactly as you wrote it, then a TEXT or a VARCHAR (x-bytes) will work for you .

If you plan to create the fields you have to think about which domain these data act on, for example, if you create the gas field and you have a way to measure how much gas the car is coming from, then it is a numerical domain.

Suppose you can measure gasoline in milliliters, then you could use a INT , and so for 10 liters, you can save 10,000. As another example, the field you propose to store if the air conditioner was functional when the car arrived, as you are only going to check if it is useful or not, can be a BOOLEAN or a TINYINT .

A final recommendation is to create a prose text on your own, where you write the requirements of this workshop. Right there you can be very aware of what entities (tables) you will need and also give you a good idea of the attributes (fields) of the entities and their relationships.

Greetings and I hope it serves you.

score 0 · Answer 2

My recommendation is as follows: How much knowledge do you have in handling text files or text fields? Relational databases were created to improve the management of data that was previously tedious with text files.

You should think about statistical calculations, relationships with other tables, groupings, summaries, etc.

Text-type fields are designed for long text, for example, emails, customer reviews, etc.

If your window has checkboxes, the most recommended is a table dedicated to the state of the vehicle with each attribute as a field and of Boolean type. If for example, you need to store the amount of gasoline that is numerical.

At the end the table makes things easier for you and the statistical calculations. Can you imagine how complicated it is to relate your table stateVehicle in text format with other tables?

I would recommend text files for data analysis but in Python, not Java.

In addition, Java does not have many functions that facilitate the manipulation of fields and text-type files.

score 0 · Answer 3

I understand that the table Appointments is the table where the visits of the vehicles to the workshop will be recorded (It would not make sense if you add it in the table Vechiculos ).

Looking at it that way, I would consider putting the states in which the cars arrive to the workshop in the Citas table, since we need to know the status of the cars in each of the appointments (This would be fine if the states already they are defined, and if there are not many). ( See first image )

If you create a separate table for the state I think it would not be very good, because the relationship will be one to one , since all the vehicles do not arrive in the same state, excluding some cases, unless the defined states are many (although for that you also have the third option) ( See Second Image ).

And if the states are defined dynamically it would be better to have a table to define the states, as required, and a pivot table to relate it to the appointment.

I would choose the first option, but it depends on what you need.