CODEWITHSUNDEEP
mysqldatabase
сами J.D.
сами J.D.

Reputation: 513

ID or varchar for primary key + multiple primary key

The database below works but I wonder if its design can be improved (I'm no expert). For instance:

1) Should DATOSREFERENCIAS table have an ID INT NOT NULL AUTO_INCREMENT instead of REFERENCIA VARCHAR(60) NOT NULL as primary key or it's irrelevant?

2) In FASESREFERENCIAS table, I can't have two entries with same REFERENCIA and NUM_FASE. Would you create a compound primary key here? I've read that compound primary keys should be avoided if possible.

The application running on top of this database won't allow to input incorrect data but I'm interested in learning how to design a good database.

DROP TABLE IF EXISTS `gestiontransportes`.`CLIENTES`;
DROP TABLE IF EXISTS `gestiontransportes`.`FASESREFERENCIAS`;
DROP TABLE IF EXISTS `gestiontransportes`.`DATOSREFERENCIAS`;
DROP TABLE IF EXISTS `gestiontransportes`.`PROVEEDORFASE`;


CREATE TABLE `gestiontransportes`.`CLIENTES` ( 
    `ID` INT NOT NULL,          -- THIS ID IS HARDCODED, COPIED FROM THE ERP
    `CLIENTES` VARCHAR(60) NOT NULL, 
    `DIAS_TRANSITO` INT NOT NULL, 
    PRIMARY KEY (`ID`),         -- INT = DÍAS DE TRÁNSITO, 100 = LUNES, 200 = MARTES, 300 = MIÉRCOLES, 400 = JUEVES, 500 = VIERNES
    UNIQUE (`CLIENTES`)
) ENGINE = InnoDB COMMENT = 'Tabla para listar todos los clientes existentes';

CREATE TABLE `gestiontransportes`.`PROVEEDORFASE` ( 
    `ID` INT NOT NULL,          -- THIS ID IS HARDCODED, COPIED FROM THE ERP
    `PROVEEDORFASE` VARCHAR(100) NOT NULL, 
    `DIAS_PROCESAR` INT NOT NULL, 
    PRIMARY KEY (`ID`), 
    UNIQUE (`PROVEEDORFASE`)
) ENGINE = InnoDB COMMENT = 'Tabla para los datos maestros de las fases';

CREATE TABLE `gestiontransportes`.`DATOSREFERENCIAS` ( 
    `REFERENCIAS` VARCHAR(60) NOT NULL,  
    `IDCLIENTE` INT NOT NULL,
    `PESO_NETO` FLOAT NOT NULL,
    `CICLO_ESPERADO` FLOAT NOT NULL,
    `RENDIMIENTO` INT NOT NULL,
    PRIMARY KEY (`REFERENCIAS`),
    FOREIGN KEY (IDCLIENTE)
        REFERENCES PROVEEDORFASE(ID)
        ON DELETE CASCADE
) ENGINE = InnoDB COMMENT = 'Tabla para los datos maestros de las refs';

CREATE TABLE `gestiontransportes`.`FASESREFERENCIAS` (
    `ID` INT NOT NULL AUTO_INCREMENT,
    `REFERENCIA` VARCHAR(60) NOT NULL,
    `NUM_FASE` INT NOT NULL,    -- IDENTIFICA EL NÚMERO DE FASE COMO EN GESIN
    `FASE` INT NOT NULL,        -- IDENTIFICA PROVEEDORFASE
    `KG_MIN_ENVIAR` INT NOT NULL,
    `KG_MAX_ENVIAR` INT NOT NULL,
    PRIMARY KEY (`ID`), 
    FOREIGN KEY (REFERENCIA)
        REFERENCES DATOSREFERENCIAS(REFERENCIAS),
    FOREIGN KEY (FASE)
        REFERENCES PROVEEDORFASE(ID)
        ON DELETE CASCADE
) ENGINE = InnoDB COMMENT = 'Tabla para los datos maestros de las fases de las refs';

Thanks in advance,

Upvotes: 0

Views: 193

Answers (1)

user2576266
user2576266

Reputation: 2683

normally, ID feilds with auto increment is the primary key standard. to force your condition in the second table use unique index, and keep using an id feild as the primary key. it is much easier when joining tables later.

Upvotes: 1

Related Questions