Learning Project Mariadb Tutorial (3): Creación del resto de tablas en SQL
Continuación de Learning Project Mariadb Tutorial (2): Creación de las tablas en SQL , antes de seguir con en este ejemplo te recomendamos que accedas al apartado anterior.
A continuación, vamos a crear el resto de las tablas que desarrollamos en el proyecto para Customerdb para MariaDB.
Tabla cb_currency
Empecemos con cb_currency, nuestra tabla de monedas para el proyecto, la utilizaremos para definir la moneda del país (cb_country y cb_enterprise por ejemplo), este el código:
CREATE TABLE cb_currency( idcurrency INT NOT NULL AUTO_INCREMENT, currency VARCHAR(60) NOT NULL, description VARCHAR(255) NOT NULL , isactive VARCHAR(1) NOT NULL DEFAULT 'N', isocode VARCHAR(3) NOT NULL, cursymbol VARCHAR(10), precisionstd DECIMAL(10,0) NOT NULL, precisioncost DECIMAL(10,0) NOT NULL, precisionprize DECIMAL(10,0) NOT NULL DEFAULT 0, CONSTRAINT pk_cb_currency PRIMARY KEY (idcurrency), CONSTRAINT u_cb_currency_currency UNIQUE (currency), CONSTRAINT u_cb_currency_isocode UNIQUE (isocode))ENGINE = InnoDBCOMMENT='Tabla donde se definen las monedas disponibles y sus relaciones a partir de las monedas bases.';GRANT ALL ON TABLE cb_currency TO xulescode;Usamos idcurrency como clave primaria un tipo integer con AUTO_INCREMENT (generación automática de un contador único):
Algunas consideraciones a tener en cuenta al definir los valores AUTO_INCREMENT:
- Puede utilizarse para generar una identidad única para nuevas filas. Al insertar un nuevo registro a la tabla el valor automáticamente será incrementado.
- Las columnas AUTO_INCREMENT comienzan a partir del 1 de forma predeterminada. El valor generado automáticamente nunca puede ser inferior a 0.
- Cada tabla puede tener sólo una columna AUTO_INCREMENT. Debe definirse como una clave (no necesariamente la clave principal o clave UNIQUE). Si la clave consiste en varias columnas, la columna AUTO_INCREMENT tiene que ser el primero, a menos que el motor de almacenamiento es Aria o MyISAM.
Para la definición de clave primaria que explicamos en la anterior publicación, usamos el siguiente código SQL:
CONSTRAINT pk_cb_currency PRIMARY [Nombre de la clave] KEY (idcurrency) [Indicamos el campo que forma la clave primaria]
En este caso también definimos como clave única currency, es decir, que este valor no se puede repetir, para ellos utilizamos: CONSTRAINT u_cb_currency_currency UNIQUE (currency), lo mismo hacemos con isocode: CONSTRAINT u_cb_currency_isocode UNIQUE (isocode) recordemos la explicaciòn de su uso:
CONSTRAINT u_cb_currency_currency [Nombre para la clave única] UNIQUE (currency) [Indicamos el campo que queremos definir como único]
Para consultar los tipos de datos que podemos usar en MariaDB aquí tienes toda la información: Data Types, veamos los tipos básicos utilizados hasta ahora:
- INT
- Integer desde -2147483648 hasta 2147483648
- INTEGER
- Sinónimo para INT
- DECIMAL
- A packed "exact" fixed-point number
- DEC, NUMERIC, FIXED
- Sinónimo para DECIMAL
- VARCHAR
- Cadena con tamaño variable, como puedes ver en las definiciones de las tablas utilizamos diferentes tamaños de cadenas para limitar los tamaños de las variables utilizadas según nos interese.
Tabla cb_country
La tabla cb_country es la tabla de países para el proyecto, en este caso para cada país indicaremos cual es el idioma y su moneda, este el código:
CREATE TABLE cb_country( idcountry INT NOT NULL AUTO_INCREMENT, country VARCHAR(100) NOT NULL, description VARCHAR(255), countrycode VARCHAR(2) NOT NULL, hasregion VARCHAR(1) NOT NULL DEFAULT 'N', regionname VARCHAR(60), expressionphone VARCHAR(20), displaysequence VARCHAR(20) NOT NULL, isdefault VARCHAR(1) NOT NULL DEFAULT 'N', ibannodigits NUMERIC, ibancountry VARCHAR(2), isactive BOOLEAN NOT NULL DEFAULT true, idlanguage VARCHAR(6), idcurrency INT, CONSTRAINT pk_cb_country PRIMARY KEY (idcountry), CONSTRAINT fk_cb_country_idcurrency FOREIGN KEY (idcurrency) REFERENCES cb_currency (idcurrency) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT fk_cb_country_idlanguage FOREIGN KEY (idlanguage) REFERENCES cb_language (idlanguage) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT un_cb_country_countrycode UNIQUE (countrycode), CONSTRAINT ch_cb_country_hasregion_check CHECK (hasregion IN ('Y', 'N')), CONSTRAINT ch_cb_country_isdefault_check CHECK (isdefault IN ('Y', 'N')))ENGINE=InnoDBCOMMENT='Tabla
donde se definen todos los países con sus características principales:
idioma, nombre, ..., y diferentes datos íntrinsecos a cada país.';GRANT ALL ON TABLE cb_country TO xulescode;Usamos idcountry como clave primaria: CONSTRAINT pk_cb_country PRIMARY KEY (idcountry), en este caso también definimos como clave única countrycode, es decir, que este valor no se puede repetir, para ellos utilizamos:
CONSTRAINT un_cb_country_countrycode UNIQUE, como ya se explicó anteriormente.En este caso para los países definimos el idioma y la moneda por lo que vamos a relacionar cb_country con las tablas correspondientes de cb_language y cb_currency, para esto vamos a definir las claves foráneas (foreign key) correspondientes:
- Definimos la clave foránea para cb_currency, que consiste en relacionar el valor de una tabla con otra estableciendo la relación, en este caso por el id de la tabla:
CONSTRAINT fk_cb_country_idcurrency [Nombre de la tabla] FOREIGN KEY (idcurrency) [Campo en cb_country]
REFERENCES cb_currency (idcurrency) [Tabla relacionada y el campo] MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION [Definición de las acciones]
REFERENCES cb_currency (idcurrency) [Tabla relacionada y el campo] MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION [Definición de las acciones]
- Definimos la clave foránea para cb_language, que consiste en relacionar el valor de una tabla con otra estableciendo la relación, en este caso por el id de la tabla:
CONSTRAINT fk_cb_country_idlanguage [Nombre de la tabla] FOREIGN KEY (idlanguage) [Campo en cb_country]
REFERENCES cb_language (idlanguage) [Tabla relacionada y el campo] MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION [Definición de las acciones]
REFERENCES cb_language (idlanguage) [Tabla relacionada y el campo] MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION [Definición de las acciones]
Tabla cb_paymentmethod
La tabla cb_paymentmethod es la tabla de métodos de pago que vamos a definir para los clientes:
CREATE TABLE cb_paymentmethod( idpaymentmethod INT NOT NULL AUTO_INCREMENT, paymentmethod VARCHAR(100) NOT NULL, description VARCHAR(150), paymentterms VARCHAR(250), paymententity VARCHAR(50), CONSTRAINT pk_cb_paymentmethod PRIMARY KEY (idpaymentmethod), CONSTRAINT un_cb_paymentmethod_paymentmethod UNIQUE (paymentmethod))ENGINE=InnoDBCOMMENT='Métodos de pago definidos para el cliente u otras entidades';GRANT ALL ON TABLE cb_paymentmethod TO xulescode;Esta tabla no tiene nada de particular definimos idpaymentmethod como clave primaria con un entero y AUTO_INCREMENT :
idpaymentmethod INT NOT NULL AUTO_INCREMENT
CONSTRAINT pk_cb_paymentmethod [Nombre para la clave primaria] PRIMARY KEY (idpaymentmethod) [Campo que forma la clave primaria]
Definimos paymentmethod como clave única como se indico anteriormente:
CONSTRAINT un_cb_paymentmethod_paymentmethod [Nombre para la clave única] UNIQUE (paymentmethod)[Campo que forma la clave primaria]
Tabla cb_addresses
La tabla cb_addresses es la tabla donde se asocian las direcciones que tiene un cliente , cada cliente tendrá un número ilimitado de direcciones definidas en cb_address, que se asociarán mediante el id generado en esta tabla y que se relacionará directamente con el cliente.
Está es la definición de la tabla, que definimos como una agrupación de las direcciones asignadas a una entidad:
CREATE TABLE cb_addresses( idaddresses INT NOT NULL AUTO_INCREMENT, addressesentity VARCHAR(100) NOT NULL, CONSTRAINT pk_cb_addresses PRIMARY KEY (idaddresses))ENGINE=InnoDBCOMMENT='Agrupación de las direcciones asignadas a una entidad.';GRANT ALL ON TABLE cb_addresses TO xulescode;Tabla cb_address
La tabla cb_address es la tabla de direcciones que vamos a definir para los clientes u otras entidades que lo necesiten, a través de la relación que establecemos mediante cb_addresses para cada entidad, este es el código SQL de la tabla:CREATE TABLE cb_address( idaddress INT NOT NULL AUTO_INCREMENT, idaddresses INT, address VARCHAR(500), postalnumber VARCHAR(20), mainphone VARCHAR(100), movilephone VARCHAR(100), phone2 VARCHAR(100), phone3 VARCHAR(100), carrier VARCHAR(200), addresstype VARCHAR(100), locality VARCHAR(250), estate VARCHAR(250), idcountry INT, notes1 VARCHAR(500), CONSTRAINT pk_cb_address PRIMARY KEY (idaddress), CONSTRAINT fk_cb_address_idaddresses FOREIGN KEY (idaddresses) REFERENCES cb_addresses (idaddresses) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT fk_cb_address_idcountry FOREIGN KEY (idcountry) REFERENCES cb_country (idcountry) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION)ENGINE=InnoDBCOMMENT='Direcciones para: Clientes, Empresas,... ';GRANT ALL ON TABLE cb_address TO xulescode;Usamos idaddress como clave primaria: CONSTRAINT pk_cb_address PRIMARY KEY (idaddress), definimos los campos habituales para la dirección entre ellos los teléfonos y el país, para este usamos una clave foránea para relacionar el país con la dirección por medio de idcountry.
CONSTRAINT fk_cb_address_idcountry FOREIGN KEY (idcountry)
REFERENCES cb_country (idcountry) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
REFERENCES cb_country (idcountry) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
Para almacenar todas las direcciones de una entidad las relacionamos por e campo idaddresses para el que podemos tener una o varias direcciones, este campo se genera en la tabla cb_addresses y para ello tenemos que relacinarlo con una clave foránea:
CONSTRAINT fk_cb_address_idcountry FOREIGN KEY (idcountry)REFERENCES cb_country (idcountry) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
Tabla cb_enterprise
La tabla cb_enterprise es la tabla de empresas de la base de datos Customerdb, con lo que cada empresa tendrá sus clientes, es el SQL con el que implementamos está tabla:CREATE TABLE cb_enterprise( identerprise INT NOT NULL, enterprise VARCHAR(150), description VARCHAR(250), enterprisealias VARCHAR(100), contact VARCHAR(250), estate VARCHAR(30), balance DECIMAL(10,3), ei VARCHAR(100), enterprisepayer VARCHAR(20), idcountry INT, idcurrency INT, idlanguage VARCHAR(6), CONSTRAINT pk_enterprise PRIMARY KEY (identerprise), CONSTRAINT cb_enterprise_idlanguage FOREIGN KEY (idlanguage) REFERENCES cb_language (idlanguage) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT fk_cb_enterprise_idcountry FOREIGN KEY (idcountry) REFERENCES cb_country (idcountry) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT fk_cb_enterprise_idcurrency FOREIGN KEY (idcurrency) REFERENCES cb_currency (idcurrency) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION)ENGINE=InnoDBCOMMENT='Tabla
para controlar las empresas que se usan en la aplicación, la aplicación
se desarrolla en función de estos parámetros ya que habrá algunas
tablas que serán comunes a las empresa y otras que no, por ejemplo, cada
empresa tendrá sus propios clientes, pero tendrá los mismos idiomas, monedas y países de trabajo.';GRANT ALL ON TABLE cb_enterprise TO xulescode;Usamos identerprise como clave primaria: CONSTRAINT pk_enterprise PRIMARY KEY (identerprise), además también definimos las relacionales para indicar el idioma (cb_language), la moneda (cb_currency) y el país (cb_country), de la misma forma que hicimos en las otra tablas.
En
este caso es un decisión de diseño para este base de datos definir el
idioma y la moneda en particular para la empresa ya que como vimos antes
los valores los tenemos relacionados por país, en este caso, defino el
idioma y la moneda con la que trabaja la empresa que no tiene porque
coincidir con la del país.
Tabla cb_customer
La tabla cb_customer es la tabla donde se almacenarán los clientes de las diferentes empresas, se entiende cliente como aquel que compra a una empresa, veamos el código SQL para nuestra tabla de cliente.CREATE TABLE cb_customer( idcustomer INT NOT NULL AUTO_INCREMENT, identerprise INT, customer VARCHAR(15) NOT NULL, customername VARCHAR(150), customeralias VARCHAR(100), contact VARCHAR(250), customerstate VARCHAR(30), sale DECIMAL(10,3), identitynumber VARCHAR(100), customerpayer VARCHAR(20), idpaymentmethod INT, idcountry INT, idcurrency INT, idlanguage VARCHAR(6), idaddresses INT, CONSTRAINT pk_cb_customer PRIMARY KEY (idcustomer), CONSTRAINT fk_cb_customer_idaddresses FOREIGN KEY (idaddresses) REFERENCES cb_addresses (idaddresses) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT fk_cb_customer_idcountry FOREIGN KEY (idcountry) REFERENCES cb_country (idcountry) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT fk_cb_customer_idcurrency FOREIGN KEY (idcurrency) REFERENCES cb_currency (idcurrency) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT fk_cb_customer_identerprise FOREIGN KEY (identerprise) REFERENCES cb_enterprise (identerprise) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT fk_cb_customer_idlanguage FOREIGN KEY (idlanguage) REFERENCES cb_language (idlanguage) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT fk_cb_customer_idpaymentmethod FOREIGN KEY (idpaymentmethod) REFERENCES cb_paymentmethod (idpaymentmethod) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT un_cb_customer_cb_enterprise UNIQUE (identerprise, customer))ENGINE=InnoDBCOMMENT='Tabla
donde se almacenarán los clientes de las diferentes empresas, se
entiende cliente como aquel que compra a una empresa.';GRANT ALL ON TABLE cb_customer TO xulescode;Usamos idcustormer como clave primaria: CONSTRAINT pk_cb_customer PRIMARY KEY (idcustomer), como queremos separar los clientes por empresa necesitamos indicar a que empresa pertenece cada cliente, para ello usamos identerprise para relacionarlos con la empresa de la tabla cb_enterprise , como se puede ver en la tabla usamos la clave foránea: fk_cb_customer_identerprise para establecer la relación.
Además también definimos las relacionales para indicar el idioma (cb_language), la moneda (cb_currency) , el país (cb_country) y las formas de pago (cb_paymentmethod).
También, definimos para los clientes, lo hacemos a través de la tabla cb_addresses, tabla que utilizaremos para almacenar varias direcciones de cliente en la tabla cb_address. En la tabla de cb_customer simplemente establecemos la relación con cb_addresses con la columna idaddresses como hicimos en otros casos:
CONSTRAINT fk_cb_customer_idaddresses FOREIGN KEY (idaddresses)
REFERENCES cb_addresses (idaddresses) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
REFERENCES cb_addresses (idaddresses) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
Por si no quieres hacerlo tabla a tabla te facilito un script para hacer la creación de las tablas y puedes hacer las pruebas que quieras: Script creación de tablas Customerdb..
Borrado de tablas
Por si fuera necesario durante la programación el orden del borrado de tablas sería el siguiente:
DROP TABLE cb_customer;
DROP TABLE cb_enterprise;
DROP TABLE cb_paymentmethod;
DROP TABLE cb_address;
DROP TABLE cb_addresses;
DROP TABLE cb_country;
DROP TABLE cb_currency;
DROP TABLE cb_language;
La sintaxis es sencilla simplemente se utiliza la sintaxis DROP TABLE, y a continuación, el nombre de la tabla.
En el siguiente capítulo facilitaré un script para cargar con datos la base de datos y empezar con las consultas básicas.
Espero que te haya sido útil.
