El Projecte | Sobre nosaltres | Contribuir | Donacions | Llicència

HOME

En aquest tema, explorarem com dissenyar una base de dades per a una aplicació de comerç electrònic utilitzant PostgreSQL. Aquest tipus d'aplicació requereix una estructura de dades robusta i flexible per gestionar productes, usuaris, comandes, pagaments i molt més.

Objectius d'aprenentatge

Comprendre els requisits bàsics d'una base de dades de comerç electrònic.
Aprendre a dissenyar esquemes de bases de dades per a diferents entitats.
Implementar relacions entre taules per assegurar la integritat de les dades.
Optimitzar el disseny per a un rendiment eficient.

Requisits bàsics d'una base de dades de comerç electrònic

Abans de començar a dissenyar, és important identificar les entitats clau i les seves relacions. Les entitats principals en una aplicació de comerç electrònic solen ser:

Usuaris: Clients que compren productes.
Productes: Articles disponibles per a la venda.
Categories: Classificacions dels productes.
Comandes: Registres de les compres realitzades pels usuaris.
Detalls de la comanda: Informació específica sobre els productes en cada comanda.
Pagaments: Informació sobre els pagaments realitzats pels usuaris.

Disseny de l'esquema de la base de dades

Taula d'Usuaris

CREATE TABLE Usuaris (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    contrasenya VARCHAR(100) NOT NULL,
    data_registre TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

id: Identificador únic per a cada usuari.
nom: Nom de l'usuari.
email: Correu electrònic de l'usuari, ha de ser únic.
contrasenya: Contrasenya de l'usuari.
data_registre: Data i hora en què l'usuari es va registrar.

Taula de Productes

CREATE TABLE Productes (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(100) NOT NULL,
    descripcio TEXT,
    preu DECIMAL(10, 2) NOT NULL,
    stock INT NOT NULL,
    categoria_id INT REFERENCES Categories(id)
);

id: Identificador únic per a cada producte.
nom: Nom del producte.
descripcio: Descripció del producte.
preu: Preu del producte.
stock: Quantitat disponible en estoc.
categoria_id: Identificador de la categoria a la qual pertany el producte.

Taula de Categories

CREATE TABLE Categories (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(100) NOT NULL
);

id: Identificador únic per a cada categoria.
nom: Nom de la categoria.

Taula de Comandes

CREATE TABLE Comandes (
    id SERIAL PRIMARY KEY,
    usuari_id INT REFERENCES Usuaris(id),
    data_comanda TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total DECIMAL(10, 2) NOT NULL
);

id: Identificador únic per a cada comanda.
usuari_id: Identificador de l'usuari que ha realitzat la comanda.
data_comanda: Data i hora en què es va realitzar la comanda.
total: Import total de la comanda.

Taula de Detalls de la Comanda

CREATE TABLE DetallsComanda (
    id SERIAL PRIMARY KEY,
    comanda_id INT REFERENCES Comandes(id),
    producte_id INT REFERENCES Productes(id),
    quantitat INT NOT NULL,
    preu DECIMAL(10, 2) NOT NULL
);

id: Identificador únic per a cada detall de la comanda.
comanda_id: Identificador de la comanda a la qual pertany aquest detall.
producte_id: Identificador del producte.
quantitat: Quantitat del producte en la comanda.
preu: Preu del producte en el moment de la compra.

Taula de Pagaments

CREATE TABLE Pagaments (
    id SERIAL PRIMARY KEY,
    comanda_id INT REFERENCES Comandes(id),
    metode VARCHAR(50) NOT NULL,
    estat VARCHAR(50) NOT NULL,
    data_pagament TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

id: Identificador únic per a cada pagament.
comanda_id: Identificador de la comanda associada al pagament.
metode: Mètode de pagament utilitzat (per exemple, targeta de crèdit, PayPal).
estat: Estat del pagament (per exemple, completat, pendent).
data_pagament: Data i hora en què es va realitzar el pagament.

Relacions entre taules

Les relacions entre les taules es defineixen utilitzant claus primàries i estrangeres. Això assegura la integritat referencial i permet realitzar consultes complexes de manera eficient.

Exemple de consulta amb JOIN

Suposem que volem obtenir una llista de totes les comandes amb els detalls dels productes i els usuaris que les han realitzat. Podem utilitzar una consulta SQL amb JOIN:

SELECT 
    Comandes.id AS comanda_id,
    Usuaris.nom AS nom_usuari,
    Productes.nom AS nom_producte,
    DetallsComanda.quantitat,
    DetallsComanda.preu,
    Comandes.total,
    Comandes.data_comanda
FROM 
    Comandes
JOIN 
    Usuaris ON Comandes.usuari_id = Usuaris.id
JOIN 
    DetallsComanda ON Comandes.id = DetallsComanda.comanda_id
JOIN 
    Productes ON DetallsComanda.producte_id = Productes.id;

Exercici pràctic

Exercici

Crea una base de dades de comerç electrònic seguint l'esquema proporcionat. Inserta dades de mostra per a usuaris, productes, categories, comandes, detalls de comandes i pagaments. Realitza una consulta per obtenir totes les comandes realitzades per un usuari específic.

Solució

-- Creació de la base de dades
CREATE DATABASE ecommerce;

-- Connexió a la base de dades
\c ecommerce

-- Creació de les taules
CREATE TABLE Usuaris (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    contrasenya VARCHAR(100) NOT NULL,
    data_registre TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE Productes (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(100) NOT NULL,
    descripcio TEXT,
    preu DECIMAL(10, 2) NOT NULL,
    stock INT NOT NULL,
    categoria_id INT REFERENCES Categories(id)
);

CREATE TABLE Categories (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(100) NOT NULL
);

CREATE TABLE Comandes (
    id SERIAL PRIMARY KEY,
    usuari_id INT REFERENCES Usuaris(id),
    data_comanda TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total DECIMAL(10, 2) NOT NULL
);

CREATE TABLE DetallsComanda (
    id SERIAL PRIMARY KEY,
    comanda_id INT REFERENCES Comandes(id),
    producte_id INT REFERENCES Productes(id),
    quantitat INT NOT NULL,
    preu DECIMAL(10, 2) NOT NULL
);

CREATE TABLE Pagaments (
    id SERIAL PRIMARY KEY,
    comanda_id INT REFERENCES Comandes(id),
    metode VARCHAR(50) NOT NULL,
    estat VARCHAR(50) NOT NULL,
    data_pagament TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Inserció de dades de mostra
INSERT INTO Categories (nom) VALUES ('Electrònica'), ('Roba'), ('Llibres');

INSERT INTO Usuaris (nom, email, contrasenya) VALUES 
('Joan', '[email protected]', 'password123'),
('Maria', '[email protected]', 'password456');

INSERT INTO Productes (nom, descripcio, preu, stock, categoria_id) VALUES 
('Portàtil', 'Portàtil d\'alta gamma', 1200.00, 10, 1),
('Samarreta', 'Samarreta de cotó', 20.00, 50, 2),
('Llibre de PostgreSQL', 'Guia completa de PostgreSQL', 35.00, 30, 3);

INSERT INTO Comandes (usuari_id, total) VALUES 
(1, 1255.00),
(2, 55.00);

INSERT INTO DetallsComanda (comanda_id, producte_id, quantitat, preu) VALUES 
(1, 1, 1, 1200.00),
(1, 3, 1, 35.00),
(2, 2, 2, 20.00);

INSERT INTO Pagaments (comanda_id, metode, estat) VALUES 
(1, 'Targeta de crèdit', 'Completat'),
(2, 'PayPal', 'Completat');

-- Consulta per obtenir totes les comandes realitzades per un usuari específic
SELECT 
    Comandes.id AS comanda_id,
    Usuaris.nom AS nom_usuari,
    Productes.nom AS nom_producte,
    DetallsComanda.quantitat,
    DetallsComanda.preu,
    Comandes.total,
    Comandes.data_comanda
FROM 
    Comandes
JOIN 
    Usuaris ON Comandes.usuari_id = Usuaris.id
JOIN 
    DetallsComanda ON Comandes.id = DetallsComanda.comanda_id
JOIN 
    Productes ON DetallsComanda.producte_id = Productes.id
WHERE 
    Usuaris.id = 1;

Conclusió

En aquest tema, hem après a dissenyar una base de dades per a una aplicació de comerç electrònic, identificant les entitats clau i les seves relacions. Hem creat les taules necessàries, inserit dades de mostra i realitzat consultes per obtenir informació rellevant. Aquest disseny pot ser ampliat i optimitzat segons les necessitats específiques de l'aplicació.

Disseny de bases de dades per a comerç electrònic

Objectius d'aprenentatge

Requisits bàsics d'una base de dades de comerç electrònic

Disseny de l'esquema de la base de dades

Taula d'Usuaris

Taula de Productes

Taula de Categories

Taula de Comandes

Taula de Detalls de la Comanda

Taula de Pagaments

Relacions entre taules

Exemple de consulta amb JOIN

Exercici pràctic

Exercici

Solució

Conclusió

Curs de PostgreSQL

Mòdul 1: Introducció a PostgreSQL

Mòdul 2: Operacions bàsiques de SQL

Mòdul 3: Consultes SQL avançades

Mòdul 4: Disseny de bases de dades i normalització

Mòdul 5: Funcionalitats avançades de PostgreSQL

Mòdul 6: Optimització i millora del rendiment

Mòdul 7: Seguretat i gestió d'usuaris

Mòdul 8: Treballant amb JSON i funcionalitats NoSQL

Mòdul 9: Extensions i eines avançades

Mòdul 10: Estudis de cas i aplicacions del món real