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

Continuación de Tutorial Postgresql (1): Preparando el entorno, antes de seguir con en este ejemplo te recomendamos que accedas al apartado anterior.
 

Postgresql tablas

A continuación, creamos el resto de las tablas explicando la sintaxis SQL utilizada, hacemos la explicación de las tablas de forma ordenada con lo que ejecutando la sintaxis planteada en este orden podrás crear la base de datos sin problema.

 

Implementamos el proyecto Learning Project en Postgresql explicando las tablas y su sintaxis paso a paso.
Código Xules

 

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 hacemos con isocode.

En la documentación de Postgresql tienes más información sobre la claves únicas, la sintaxis básica es la siguiente:

CREATE UNIQUE INDEX name ON table (column [, ...]);

 

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, para ellos definiremos el uso de las claves foráneas (foreign key), 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.

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

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 diferentes 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_identerprisepara 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.

 

¿Qué hacemos ahora?

Continúa con el aprendizaje de PostgreSQL con una explicación sencilla sobre las consultas.

Postgresql consultas básicas

Descarga el script de datos del proyecto Learning Project en Postgresql y realiza las consultas para continuar tu aprendizaje.
Código Xules

 
 
Iniciate en el desarrollo de aplicaciones empresariales web con el proyecto Learning Project con PostgreSQL:

Desarrollo en 6 pasos de una aplicación con Spring Roo con PostgreSQL

En este tutorial aprenderemos a hacer una aplicación Web desde cero usando Spring Roo con PostgreSQL Código Xules

 

Aprende OpenXava con PostgreSQL

OpenXava es un marco de trabajo AJAX para desarrollo rápido de aplicaciones web empresariales.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos necesarios están marcados *