Ejemplo de creación y diseño de base de datos en PostgreSQL

PostgreSQL SQL

Supongamos que queremos crear una base de datos para una tienda en línea que vende productos. Primero, debemos identificar las entidades relevantes para nuestra base de datos.

Aquí te presento un ejemplo de creación y diseño de una base de datos utilizando las tablas tb_cliente, tb_producto, tb_factura y tb_factura_detalle.

En PostgreSQL se pueden crear bases de datos y cada base de datos puede contener uno o varios esquemas, vamos a crear una base de datos "postgres" y le vamos asignar el usuario "postgres".

CREATE DATABASE postgres OWNER postgres;

Un esquema en PostgreSQL es una colección de objetos de base de datos, como tablas, vistas, funciones, etc. Un esquema puede ser utilizado para organizar y separar los objetos de la base de datos en grupos lógicos.

Cuando se crea una nueva base de datos en PostgreSQL, se crea automáticamente un esquema llamado "public", que es el esquema predeterminado. Todos los objetos de la base de datos que no se especifican en un esquema se almacenan en este esquema "public".

Sin embargo, se pueden crear esquemas adicionales en una base de datos para organizar los objetos de la base de datos de una manera más lógica y eficiente. Para crear un nuevo esquema en PostgreSQL, se puede utilizar la siguiente sintaxis:

Crear esquema "bdcrud"

CREATE SCHEMA bdcrud;

Tabla "tb_cliente"

La tabla "tb_cliente" contiene información sobre los clientes de la tienda, incluyendo su nombre, dirección y fecha de nacimiento.

CREATE TABLE bdcrud.tb_cliente (
    idcliente integer NOT NULL,
    nombres character varying(100) NOT NULL,
    direccion character varying(100) DEFAULT NULL::character varying,
    fecha_nacimiento date NOT NULL,
    fecha_registro timestamp without time zone NOT NULL
);
ALTER TABLE ONLY bdcrud.tb_cliente
    ADD CONSTRAINT tb_cliente_pkey PRIMARY KEY (idcliente);

El usuario postgres tendrá acceso a la tabla "tb_cliente"

ALTER TABLE bdcrud.tb_cliente OWNER TO postgres;

Creamos la secuencia para el campo "idcliente" de la tabla "tb_cliente"

CREATE SEQUENCE bdcrud.sq_tb_cliente
    START WITH 21
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

El usuario postgres tendrá acceso a la secuencia "sq_tb_cliente"

ALTER TABLE bdcrud.sq_tb_cliente OWNER TO postgres;

Script para insertar datos a la tabla "tb_cliente"

INSERT INTO bdcrud.tb_cliente VALUES (1, 'Juan Perez', 'Av. Siempreviva 123', '1990-06-15', now());
INSERT INTO bdcrud.tb_cliente VALUES (2, 'Maria Rodriguez', 'Calle Falsa 123', '1985-08-20', now());
INSERT INTO bdcrud.tb_cliente VALUES (3, 'Pedro Gomez', 'Av. Principal 456', '1995-02-10', now());
INSERT INTO bdcrud.tb_cliente VALUES (4, 'Ana Fernandez', 'Calle Real 789', '1982-12-01', now());
INSERT INTO bdcrud.tb_cliente VALUES (5, 'Luisa Martinez', 'Av. America 321', '1993-07-18', now());
INSERT INTO bdcrud.tb_cliente VALUES (6, 'Jorge Ramirez', 'Calle Luna 456', '1978-11-30', now());
INSERT INTO bdcrud.tb_cliente VALUES (7, 'Sofia Sanchez', 'Av. Sol 987', '1998-04-05', now());
INSERT INTO bdcrud.tb_cliente VALUES (8, 'Carlos Torres', 'Calle Estrella 654', '1987-03-22', now());
INSERT INTO bdcrud.tb_cliente VALUES (9, 'Fernando Garcia', 'Av. Universitaria 321', '1991-09-28', now());
INSERT INTO bdcrud.tb_cliente VALUES (10, 'Gloria Castro', 'Calle Alegria 987', '1975-05-12', now());
INSERT INTO bdcrud.tb_cliente VALUES (11, 'Diego Lopez', 'Av. Libertad 456', '1996-08-02', now());
INSERT INTO bdcrud.tb_cliente VALUES (12, 'Valeria Jimenez', 'Calle Flores 789', '1989-01-07', now());
INSERT INTO bdcrud.tb_cliente VALUES (13, 'Ricardo Hernandez', 'Av. Rio 123', '1979-10-25', now());
INSERT INTO bdcrud.tb_cliente VALUES (14, 'Silvia Morales', 'Calle Montaña 456', '1992-03-12', now());
INSERT INTO bdcrud.tb_cliente VALUES (15, 'Gabriel Diaz', 'Av. Peru 987', '1984-06-22', now());
INSERT INTO bdcrud.tb_cliente VALUES (16, 'Lucia Ortiz', 'Calle Paraiso 654', '1976-09-08', now());
INSERT INTO bdcrud.tb_cliente VALUES (17, 'Oscar Nieto', 'Av. España 321', '1990-12-15', now());
INSERT INTO bdcrud.tb_cliente VALUES (18, 'Alicia Rivera', 'Calle Paz 789', '1983-05-28', now());
INSERT INTO bdcrud.tb_cliente VALUES (19, 'Pablo Castro', 'Av. Amazonas 456', '1994-02-03', now());
INSERT INTO bdcrud.tb_cliente VALUES (20, 'Natalia Vega', 'Calle Luna 123', '1977-11-17', now());

Tabla "tb_producto"

La tabla "tb_producto" contiene información sobre los productos que vende la tienda, incluyendo su nombre, precio y stock.

CREATE TABLE bdcrud.tb_producto (
    idproducto integer NOT NULL,
    nombre character varying(100) NOT NULL,
    precio numeric(10,2) NOT NULL,
    stock integer NOT NULL,
    fecha_registro timestamp without time zone NOT NULL
);
ALTER TABLE ONLY bdcrud.tb_producto
    ADD CONSTRAINT tb_producto_pkey PRIMARY KEY (idproducto);

El usuario postgres tendrá acceso a la tabla "tb_producto"

ALTER TABLE bdcrud.tb_producto OWNER TO postgres;

Creamos la secuencia para el campo "idproducto" de la tabla "sq_tb_producto"

CREATE SEQUENCE bdcrud.sq_tb_producto
    START WITH 18
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

El usuario postgres tendrá acceso a la secuencia "sq_tb_producto"

ALTER TABLE bdcrud.sq_tb_producto OWNER TO postgres;

Script para insertar datos a la tabla "tb_producto"

INSERT INTO bdcrud.tb_producto VALUES (1, 'Smartphone Samsung Galaxy S20', 899.99, 10, now());
INSERT INTO bdcrud.tb_producto VALUES (2, 'Laptop Dell Inspiron 15', 1099.99, 5, now());
INSERT INTO bdcrud.tb_producto VALUES (3, 'Smart TV LG 55\"', 1299.99, 3, now());
INSERT INTO bdcrud.tb_producto VALUES (4, 'Aspiradora Dyson V11', 599.99, 2, now());
INSERT INTO bdcrud.tb_producto VALUES (5, 'Camisa Polo Ralph Lauren', 99.99, 20, now());
INSERT INTO bdcrud.tb_producto VALUES (6, 'Tenis Nike Air Max', 149.99, 8, now());
INSERT INTO bdcrud.tb_producto VALUES (7, 'Muñeca Barbie Dreamhouse', 199.99, 4, now());
INSERT INTO bdcrud.tb_producto VALUES (8, 'Camisa', 39.99, 50, now());
INSERT INTO bdcrud.tb_producto VALUES (9, 'Pantalón', 59.99, 30, now());
INSERT INTO bdcrud.tb_producto VALUES (10, 'Zapatos', 89.99, 20, now());
INSERT INTO bdcrud.tb_producto VALUES (11, 'Gorra', 19.99, 100, now());
INSERT INTO bdcrud.tb_producto VALUES (12, 'Bufanda', 29.99, 40, now());
INSERT INTO bdcrud.tb_producto VALUES (13, 'Guantes', 14.99, 50, now());
INSERT INTO bdcrud.tb_producto VALUES (14, 'Chaqueta', 99.99, 10, now());
INSERT INTO bdcrud.tb_producto VALUES (15, 'Calcetines', 9.99, 80, now());
INSERT INTO bdcrud.tb_producto VALUES (16, 'Sombrero', 24.99, 30, now());
INSERT INTO bdcrud.tb_producto VALUES (17, 'Cinturón', 19.99, 60, now());

Tabla "tb_factura"

La tabla "tb_factura" relaciona los clientes con las facturas que han realizado y contiene información sobre la fecha y el total(monto) de la factura

CREATE TABLE bdcrud.tb_factura (
    idfactura integer NOT NULL,
    id_cliente integer NOT NULL,
    nro_factura character varying(10) NOT NULL,
    monto numeric(10,2) NOT NULL,
    fecha_registro timestamp without time zone NOT NULL
);
ALTER TABLE ONLY bdcrud.tb_factura
    ADD CONSTRAINT tb_factura_pkey PRIMARY KEY (idfactura);
ALTER TABLE ONLY bdcrud.tb_factura
    ADD CONSTRAINT tb_factura_fk FOREIGN KEY (id_cliente) REFERENCES bdcrud.tb_cliente(idcliente);
ALTER TABLE ONLY bdcrud.tb_factura
    ADD CONSTRAINT tb_factura_un UNIQUE (nro_factura);

El usuario postgres tendrá acceso a la tabla "tb_factura"

ALTER TABLE bdcrud.tb_factura OWNER TO postgres;

Creamos la secuencia para el campo "idfactura" de la tabla "sq_tb_factura"

CREATE SEQUENCE bdcrud.sq_tb_factura
    START WITH 11
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

El usuario postgres tendrá acceso a la secuencia "sq_tb_factura"

ALTER TABLE bdcrud.sq_tb_factura OWNER TO postgres;

Script para insertar datos a la tabla "tb_factura"

INSERT INTO bdcrud.tb_factura VALUES (1, 1, 'FAC001', 100.00, now());
INSERT INTO bdcrud.tb_factura VALUES (2, 2, 'FAC002', 200.00, now());
INSERT INTO bdcrud.tb_factura VALUES (3, 3, 'FAC003', 300.00, now());
INSERT INTO bdcrud.tb_factura VALUES (4, 4, 'FAC004', 400.00, now());
INSERT INTO bdcrud.tb_factura VALUES (5, 5, 'FAC005', 500.00, now());
INSERT INTO bdcrud.tb_factura VALUES (6, 1, 'FAC006', 150.00, now());
INSERT INTO bdcrud.tb_factura VALUES (7, 2, 'FAC007', 250.00, now());
INSERT INTO bdcrud.tb_factura VALUES (8, 3, 'FAC008', 350.00, now());
INSERT INTO bdcrud.tb_factura VALUES (9, 4, 'FAC009', 450.00, now());
INSERT INTO bdcrud.tb_factura VALUES (10, 5, 'FAC010', 550.00, now());

Tabla "tb_factura_detalle"

La tabla "tb_factura_detalle" contiene información sobre los productos que se incluyen en cada factura y la cantidad y precio de cada producto.

CREATE TABLE bdcrud.tb_factura_detalle (
    iddetallefactura integer NOT NULL,
    id_factura integer NOT NULL,
    id_producto integer NOT NULL,
    cantidad integer NOT NULL,
    precio numeric(10,2) NOT NULL,
    subtotal numeric(10,2) NOT NULL,
    fecha_registro timestamp without time zone NOT NULL
);
ALTER TABLE ONLY bdcrud.tb_factura_detalle
    ADD CONSTRAINT tb_factura_detalle_pkey PRIMARY KEY (iddetallefactura);
ALTER TABLE ONLY bdcrud.tb_factura_detalle
    ADD CONSTRAINT tb_factura_detalle_fk FOREIGN KEY (id_producto) REFERENCES bdcrud.tb_producto(idproducto);
ALTER TABLE ONLY bdcrud.tb_factura_detalle
    ADD CONSTRAINT tb_factura_detalle_fk_1 FOREIGN KEY (id_factura) REFERENCES bdcrud.tb_factura(idfactura);

El usuario postgres tendrá acceso a la tabla "tb_factura_detalle"

ALTER TABLE bdcrud.tb_factura_detalle OWNER TO postgres;

Creamos la secuencia para el campo "iddetallefactura" de la tabla "sq_tb_factura_detalle"

CREATE SEQUENCE bdcrud.sq_tb_factura_detalle
    START WITH 21
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

El usuario postgres tendrá acceso a la secuencia "sq_tb_factura_detalle"

ALTER TABLE bdcrud.sq_tb_factura_detalle OWNER TO postgres;

Script para insertar datos a la tabla "tb_factura_detalle"

INSERT INTO bdcrud.tb_factura_detalle VALUES (1, 1, 1, 2, 25.00, 50.00, now());
INSERT INTO bdcrud.tb_factura_detalle VALUES (2, 1, 2, 1, 50.00, 50.00, now());
INSERT INTO bdcrud.tb_factura_detalle VALUES (3, 2, 3, 3, 20.00, 60.00, now());
INSERT INTO bdcrud.tb_factura_detalle VALUES (4, 2, 4, 2, 35.00, 70.00, now());
INSERT INTO bdcrud.tb_factura_detalle VALUES (5, 3, 5, 4, 15.00, 60.00, now());
INSERT INTO bdcrud.tb_factura_detalle VALUES (6, 3, 6, 1, 100.00, 100.00, now());
INSERT INTO bdcrud.tb_factura_detalle VALUES (7, 4, 7, 3, 30.00, 90.00, now());
INSERT INTO bdcrud.tb_factura_detalle VALUES (8, 4, 8, 2, 45.00, 90.00, now());
INSERT INTO bdcrud.tb_factura_detalle VALUES (9, 5, 9, 5, 10.00, 50.00, now());
INSERT INTO bdcrud.tb_factura_detalle VALUES (10, 5, 10, 2, 75.00, 150.00, now());
INSERT INTO bdcrud.tb_factura_detalle VALUES (11, 6, 1, 1, 25.00, 25.00, now());
INSERT INTO bdcrud.tb_factura_detalle VALUES (12, 6, 4, 3, 35.00, 105.00, now());
INSERT INTO bdcrud.tb_factura_detalle VALUES (13, 7, 6, 2, 100.00, 200.00, now());
INSERT INTO bdcrud.tb_factura_detalle VALUES (14, 7, 9, 1, 10.00, 10.00, now());
INSERT INTO bdcrud.tb_factura_detalle VALUES (15, 8, 2, 2, 50.00, 100.00, now());
INSERT INTO bdcrud.tb_factura_detalle VALUES (16, 8, 3, 1, 20.00, 20.00, now());
INSERT INTO bdcrud.tb_factura_detalle VALUES (17, 9, 5, 3, 15.00, 45.00, now());
INSERT INTO bdcrud.tb_factura_detalle VALUES (18, 9, 8, 4, 45.00, 180.00, now());
INSERT INTO bdcrud.tb_factura_detalle VALUES (19, 10, 10, 1, 75.00, 75.00, now());
INSERT INTO bdcrud.tb_factura_detalle VALUES (20, 10, 7, 2, 30.00, 60.00, now());

Diseño del esquema "bdcrud"

Con este diseño de base de datos, podemos almacenar y recuperar información de manera eficiente sobre los clientes que realizan compras, los productos que vendemos, las facturas que generamos y los detalles de cada factura. Además, podemos realizar consultas y generar informes útiles para la gestión de la tienda.

Para visualizar el diseño completo de las tablas creadas utilizando el modelo lógico, lo ideal es utilizar una herramienta de modelado de datos que te permita crear y visualizar diagramas ER (Entidad-Relación) que representen la estructura de la base de datos y las relaciones entre las tablas.