Definir les claus primàries per vincular tasques

1. Conceptes Teòrics i Casos d’Ús

Perquè la nostra base de dades funcioni sense errors, hem de seguir uns criteris clau a l’hora de triar les nostres claus:

• Clau Primària (PK): Un codi únic per a cada registre (fila). No es pot repetir mai i no pot estar buit (NOT NULL). Utilitzarem números que s’autoincrementen sols (AUTO_INCREMENT).
• Clau Forana (FK): Un camp que connecta una taula amb una altra. Serveix per enllaçar les dades i el sistema comprova que la dada connectada existeixi de veritat.

Relacions del Nostre Sistema

Usuari → Ticket (Relació 1:N)

• Explicació: Un usuari de la LAN Party pot obrir molts tickets d’incidència (per exemple: «No em funciona Internet», «Falta una cadira»).
• Com es connecten: La taula tickets té el camp user_id que apunta a la PK de users.
• Regla d’esborrat (ON DELETE RESTRICT): No podem esborrar un usuari de la base de dades si encara té tickets oberts. Primer s’haurien de tancar o gestionar els seus tickets.

Tècnic → Ticket (Relació 1:N)

• Explicació: Un tècnic de l’organització pot tenir assignats molts tickets de programari o xarxes, pero cada ticket només el repara un tècnic alhora.
• Com es connecten: La taula tickets té un camp technician_id que apunta a la taula technicians.
• Regla d’esborrat (ON DELETE SET NULL): Si un tècnic plega i l’esborrem del sistema, el ticket no s’esborra; simplement es queda en blanc (NULL) esperant que s’assigni a un altre tècnic.

Usuari → Tècnic (Relació 1:1)

• Explicació: Un tècnic és, primer de tot, un usuari registrat a la LAN Party, però té un perfil especial amb eines de reparació.
• Com es connecten: El camp user_id de la taula technicians és una FK que apunta a users, però a més és UNIQUE (únic), de manera que un usuari no pot ser dos tècnics alhora.
• Regla d’esborrat (ON DELETE CASCADE): Si esborrem l’usuari del sistema, automàticament s’esborra la seva fitxa de tècnic.

2. Disseny de les Taules (Model Relacional)

A sota pots veure com es connecten les 4 taules principals del sistema LAN Party per garantir que cap dada es quedi perduda o incorrecta:

3. Codi SQL per a la creació (Implementació)

Aquest és el codi en llenguatge SQL adaptat per introduir-lo directament a phpMyAdmin o MySQL Workbench a les pràctiques de classe:

-- 1. CREACIÓ DE LA TAULA USUARIS
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    role VARCHAR(20) DEFAULT 'assistant'
);

-- 2. CREACIÓ DE LA TAULA TÈCNICS (Depèn de users)
CREATE TABLE technicians (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNIQUE NOT NULL, -- UNIQUE fa que la relació sigui 1:1
    specialization VARCHAR(50) DEFAULT 'Xarxes',
    is_available BOOLEAN DEFAULT TRUE,
    
    -- Unió amb la taula users
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- 3. CREACIÓ DE LA TAULA ZONES (Ubicacions de la LAN Party)
CREATE TABLE zones (
    id INT PRIMARY KEY AUTO_INCREMENT,
    zone_name VARCHAR(50) NOT NULL UNIQUE,
    capacity INT
);

-- 4. CREACIÓ DE LA TAULA TICKETS (Depèn de users, technicians i zones)
CREATE TABLE tickets (
    id INT PRIMARY KEY AUTO_INCREMENT,
    ticket_number VARCHAR(20) NOT NULL UNIQUE,
    title VARCHAR(150) NOT NULL,
    description TEXT NOT NULL,
    priority VARCHAR(10) DEFAULT 'Media', 
    status VARCHAR(15) DEFAULT 'Obert',   
    
    user_id INT NOT NULL,          
    technician_id INT,             
    zone_id INT NOT NULL,          
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    -- Definició de Claus Foranes i normes de seguretat d'esborrat
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT,
    FOREIGN KEY (technician_id) REFERENCES technicians(id) ON DELETE SET NULL,
    FOREIGN KEY (zone_id) REFERENCES zones(id) ON DELETE RESTRICT
);

-- 5. CREACIÓ DE LA TAULA COMENTARIS (Depèn de tickets i users)
CREATE TABLE ticket_comments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    ticket_id INT NOT NULL,
    user_id INT NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    -- Si s'esborra el ticket, s'esborren els comentaris automàticament
    FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT
);

4. Taula Resum d’Integritat Referencial

Per comprovar ràpidament el funcionament de la nostra base de dades i repassar abans de l’examen de l’mòdul:

5. Proves de Funcionament (Validació a l’aula)

Com podem comprovar a classe de SMX que el nostre disseny funciona correctament i no admet errors?

1. Prova de la Clau Primària: Si intentes inserir manualment dos usuaris amb el mateix id o el mateix username, la base de dades llançarà un error de dades duplicades (Duplicate entry).
2. Prova del RESTRICT: Crea un usuari, afegeix un ticket assignat a aquest usuari i després intenta esborrar l’usuari de la taula users. El MySQL ho bloquejarà per evitar que el ticket es quedi «orfe» (sense saber de qui és).
3. Prova del CASCADE: Crea un usuari i fes-lo tècnic. Elimina l’usuari a la taula users i veuràs com automàticament ha desaparegut el seu registre vinculat a la taula technicians.