Código Xules: Learning Project Postgresql (2) : Creación de tablas e introducción de datos

Learning Project Postgresql (2): Creación de las tablas en SQL

Continuación de Learning Project Postgresql (1): Explicación, antes de seguir con en este ejemplo te recomendamos que accedas al apartado anterior.

A continuación creamos el resto de las tablas

Tabla cb_currency

Empecemos con cb_currency , nuestra tabla de monedas para el proyecto, este el código:

CREATE TABLE cb_currency
(
idcurrency serial NOT NULL,
currency character varying(60) NOT NULL,
description character varying(255) NOT NULL,
isactive character(1) NOT NULL DEFAULT 'Y'::bpchar,
isocode character(3) NOT NULL,
cursymbol character varying(10),
precisionstd numeric(10,0) NOT NULL,
precisioncost numeric(10,0) NOT NULL,
precisionprize numeric(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)
)
WITH (
OIDS=TRUE
);
ALTER TABLE cb_currency OWNER TO xulescode;
COMMENT ON TABLE cb_currency
IS 'Tabla donde se definen las monedas disponibles y sus relaciones a partir de las monedas bases.';

Usamos idcurrency como clave primaria: CONSTRAINT pk_cb_currency PRIMARY KEY (idcurrency) , 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 , lo mismo paso con isocode.

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 serial NOT NULL,
country character varying(100) NOT NULL,
description character varying(255),
countrycode character(2) NOT NULL,
hasregion character(1) NOT NULL DEFAULT 'N'::bpchar,
regionname character varying(60),
expressionphone character varying(20),
displaysequence character varying(20) NOT NULL,
isdefault character(1) DEFAULT 'N'::bpchar,
ibannodigits numeric,
ibancountry character varying(2),
isactive boolean NOT NULL DEFAULT true,
idlanguage character varying(6),
idcurrency integer,
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 = ANY (ARRAY['Y'::bpchar, 'N'::bpchar])),
CONSTRAINT ch_cb_country_isdefault_check CHECK (isdefault = ANY (ARRAY['Y'::bpchar, 'N'::bpchar]))
)
WITH (
OIDS=TRUE
);
ALTER TABLE cb_country OWNER TO xulescode;

COMMENT ON TABLE cb_country
IS
 'Tabla donde se definen todos los países con sus características 
principales: idioma, nombre, ..., y diferentes datos íntrinsecos a cada 
país.';

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. En este caso para los países definimos el idioma y la moneda para eso lo vamos a relacionar 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]

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]

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 serial NOT NULL,
paymentmethod character varying(100) NOT NULL,
description character varying(150),
paymentterms character varying(250),
paymententity character varying(50),
CONSTRAINT pk_cb_paymentmethod PRIMARY KEY (idpaymentmethod),
CONSTRAINT un_cb_paymentmethod_paymentmethod UNIQUE (paymentmethod)
)
WITH (
OIDS=TRUE
);

ALTER TABLE cb_paymentmethod  OWNER TO xulescode;
COMMENT ON TABLE cb_paymentmethod
  IS 'Métodos de pago definidos para el cliente u otras entidades.';

Esta tabla no tiene nada de particular definimos idpaymentmethod como clave primaria, y paymentmethod como clave única como se indico anteriormente.

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.

CREATE TABLE cb_addresses
(
idaddresses serial NOT NULL,
addressesentity character varying(100) NOT NULL,
CONSTRAINT pk_cb_addresses PRIMARY KEY (idaddresses)
)
WITH (
OIDS=TRUE
);
ALTER TABLE cb_addresses OWNER TO xulescode;
COMMENT ON TABLE cb_addresses
IS 'Agrupación de las direcciones asignadas a una entidad.';

Tabla cb_address

La tabla cb_address es la tabla de métodos de pago que vamos a definir para los clientes:

CREATE TABLE cb_address
(
idaddress serial NOT NULL,
idaddresses integer,
address character varying,
postalnumber character varying(20),
mainphone character varying(100),
movilephone character varying(100),
phone2 character varying(100),
phone3 character varying(100),
carrier character varying(200),
addresstype character varying(100),
locality character varying(250),
state character varying(250),
idcountry integer,
notes1 character varying(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
)
WITH (
OIDS=TRUE
);
ALTER TABLE cb_address
OWNER TO xulescode;
COMMENT ON TABLE cb_address
IS 'Direcciones para: Clientes, Empresas,... ';

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

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

De momento vamos a dejar las acciones sin hacer nada, tanto aquí como en cb_addresses y cb_address, para más adelante ver la conveniencia de realizar acciones de actualización y borrado en cadena.

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 integer NOT NULL,
enterprise character varying(150),
description character varying(250),
enterprisealias character varying(100),
contact character varying(250),
state character varying(30),
balance numeric(10,3),
ei character varying(100),
enterprisepayer character varying(20),
idcountry integer,
idcurrency integer,
idlanguage character varying(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
)
WITH (
OIDS=FALSE
);
ALTER TABLE cb_enterprise OWNER TO xulescode;
GRANT ALL ON TABLE cb_enterprise TO xulescode;
COMMENT ON TABLE cb_enterprise

 
 IS '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.';

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 serial NOT NULL,
identerprise integer,
customer character varying(15) NOT NULL,
customername character varying(150),
customeralias character varying(100),
contact character varying(250),
customerstate character varying(30),
sale numeric(10,3),
identitynumber character varying(100),
customerpayer character varying(20),
idpaymentmethod integer,
idcountry integer,
idcurrency integer,
idlanguage character varying(6),
idaddresses integer,
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)
)
WITH (
OIDS=FALSE
);
ALTER TABLE cb_customer OWNER TO xulescode;
COMMENT ON TABLE cb_customer

 
 IS 'Tabla donde se almacenarán los clientes de las dif

erentes empresas, se entiende cliente como aquel que compra a una empresa.';

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

Será a través de esta tabla como obtendremos las direcciones reales del cliente almacenadas en cb_address.

Por si no quieres hacerlo tabla a tabla te facilito un script para hacer la creación de las tablas: Script creación de tablas Customerdb.

Código Xules

Páginas

viernes, 4 de septiembre de 2015

Learning Project Postgresql (2) : Creación de tablas e introducción de datos