You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
97 lines
2.8 KiB
SQL
97 lines
2.8 KiB
SQL
-- Schéma de base de données BricoLoc Legacy
|
|
-- SQLite 3
|
|
|
|
-- Table des utilisateurs
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
email TEXT NOT NULL UNIQUE,
|
|
password TEXT NOT NULL,
|
|
nom TEXT NOT NULL,
|
|
prenom TEXT NOT NULL,
|
|
telephone TEXT,
|
|
adresse TEXT,
|
|
code_postal TEXT,
|
|
ville TEXT,
|
|
is_admin INTEGER DEFAULT 0,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Table des catégories d'outils
|
|
CREATE TABLE IF NOT EXISTS categories (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
nom TEXT NOT NULL,
|
|
description TEXT,
|
|
slug TEXT UNIQUE NOT NULL
|
|
);
|
|
|
|
-- Table des entrepôts
|
|
CREATE TABLE IF NOT EXISTS entrepots (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
nom TEXT NOT NULL,
|
|
ville TEXT NOT NULL,
|
|
code_postal TEXT,
|
|
adresse TEXT,
|
|
telephone TEXT,
|
|
email TEXT
|
|
);
|
|
|
|
-- Table des outils
|
|
CREATE TABLE IF NOT EXISTS outils (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
nom TEXT NOT NULL,
|
|
description TEXT,
|
|
categorie_id INTEGER,
|
|
prix_jour REAL NOT NULL,
|
|
caution REAL,
|
|
marque TEXT,
|
|
modele TEXT,
|
|
annee INTEGER,
|
|
poids REAL,
|
|
dimensions TEXT,
|
|
puissance TEXT,
|
|
image_url TEXT,
|
|
restrictions_pro INTEGER DEFAULT 0,
|
|
livraison_possible INTEGER DEFAULT 1,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (categorie_id) REFERENCES categories(id)
|
|
);
|
|
|
|
-- Table des stocks (anti-pattern : données dénormalisées)
|
|
CREATE TABLE IF NOT EXISTS stocks (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
outil_id INTEGER NOT NULL,
|
|
entrepot_id INTEGER NOT NULL,
|
|
quantite_totale INTEGER NOT NULL,
|
|
quantite_disponible INTEGER NOT NULL,
|
|
last_updated DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (outil_id) REFERENCES outils(id),
|
|
FOREIGN KEY (entrepot_id) REFERENCES entrepots(id)
|
|
);
|
|
|
|
-- Table des réservations
|
|
CREATE TABLE IF NOT EXISTS reservations (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
outil_id INTEGER NOT NULL,
|
|
entrepot_id INTEGER NOT NULL,
|
|
date_debut DATE NOT NULL,
|
|
date_fin DATE NOT NULL,
|
|
prix_total REAL NOT NULL,
|
|
statut TEXT DEFAULT 'en_attente',
|
|
mode_paiement TEXT,
|
|
paiement_effectue INTEGER DEFAULT 0,
|
|
notes TEXT,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id),
|
|
FOREIGN KEY (outil_id) REFERENCES outils(id),
|
|
FOREIGN KEY (entrepot_id) REFERENCES entrepots(id)
|
|
);
|
|
|
|
-- Index pour améliorer les performances (même si on a des anti-patterns ailleurs)
|
|
CREATE INDEX IF NOT EXISTS idx_outils_categorie ON outils(categorie_id);
|
|
CREATE INDEX IF NOT EXISTS idx_stocks_outil ON stocks(outil_id);
|
|
CREATE INDEX IF NOT EXISTS idx_stocks_entrepot ON stocks(entrepot_id);
|
|
CREATE INDEX IF NOT EXISTS idx_reservations_user ON reservations(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_reservations_outil ON reservations(outil_id);
|
|
CREATE INDEX IF NOT EXISTS idx_reservations_dates ON reservations(date_debut, date_fin);
|