Database Schema - Cinefinder

Overview

Cinefinder uses a MySQL database named CINES with a relational schema designed to manage movies, theatrical works, urban events, artists, categories, and their relationships.

Database Structure

The database consists of 6 main tables:

categorias - Content categories
artistas - Artist information
peliculas - Movie details
obras - Theatrical works
eventos_urbanos - Urban cultural events
participaciones - Artist participation in events

Entity Relationship Diagram

Table Schemas

1. categorias

Stores content categories for movies, works, and events.

CREATE TABLE IF NOT EXISTS CINES.categorias (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    nombre VARCHAR(100) NOT NULL UNIQUE
);

Columns:

Column	Type	Constraints	Description
`id`	BIGINT	PRIMARY KEY, AUTO_INCREMENT	Unique category identifier
`nombre`	VARCHAR(100)	NOT NULL, UNIQUE	Category name

Example Data:

Acción (Action)
Comedia (Comedy)
Drama
Terror (Horror)
Ciencia Ficción (Science Fiction)
Romance
Documental (Documentary)
Animación (Animation)
Musical
Thriller
Fantasía (Fantasy)
Aventura (Adventure)

JPA Entity: Categorias - src/main/java/com/trainee/Cinefinder/model/Categorias.java:12

2. artistas

Stores artist information including actors, singers, dancers, magicians, and other performers.

CREATE TABLE IF NOT EXISTS CINES.artistas (
    dni BIGINT PRIMARY KEY,
    nombre VARCHAR(70) NOT NULL,
    apellido VARCHAR(70) NULL,
    tipo VARCHAR(50),
    descripcion TEXT
);

Columns:

Column	Type	Constraints	Description
`dni`	BIGINT	PRIMARY KEY	National ID number (unique identifier)
`nombre`	VARCHAR(70)	NOT NULL	Artist first name
`apellido`	VARCHAR(70)	NULL	Artist last name (optional)
`tipo`	VARCHAR(50)	-	Artist type (Actor, Cantante, Bailarina, etc.)
`descripcion`	TEXT	-	Artist biography/description

Artist Types:

Actor / Actriz (Actor/Actress)
Cantante (Singer)
Bailarina (Dancer)
Mago (Magician)
Rapero (Rapper)
Pintora (Painter)
Músico (Musician)
Performista (Performance Artist)

JPA Entity: Artistas - src/main/java/com/trainee/Cinefinder/model/Artistas.java:19

3. peliculas

Stores movie information.

CREATE TABLE IF NOT EXISTS CINES.peliculas (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    titulo VARCHAR(200) UNIQUE NOT NULL,
    sipnosis TEXT,
    duracion INT NOT NULL,
    categoria_id BIGINT,
    CONSTRAINT categorias_fk_peliculas FOREIGN KEY (categoria_id) 
        REFERENCES categorias(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Columns:

Column	Type	Constraints	Description
`id`	BIGINT	PRIMARY KEY, AUTO_INCREMENT	Unique movie identifier
`titulo`	VARCHAR(200)	UNIQUE, NOT NULL	Movie title
`sipnosis`	TEXT	-	Movie synopsis/summary
`duracion`	INT	NOT NULL	Duration in minutes
`categoria_id`	BIGINT	FOREIGN KEY → categorias(id)	Associated category

Foreign Key Constraints:

categorias_fk_peliculas: Links to categorias(id)
- ON DELETE CASCADE: Deleting a category deletes associated movies
- ON UPDATE CASCADE: Updating a category ID updates movie references

Example Movies:

El Último Viaje (120 min, Ciencia Ficción)
Amor de Barrio (95 min, Romance)
Ríe que Ríe (100 min, Comedia)
Bajo Tierra (110 min, Thriller)

JPA Entity: Peliculas - src/main/java/com/trainee/Cinefinder/model/Peliculas.java:13

4. obras

Stores theatrical works and stage performances.

CREATE TABLE IF NOT EXISTS CINES.obras (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    titulo VARCHAR(200) NOT NULL,
    descripcion TEXT,
    duracion INT NOT NULL,
    categoria_id BIGINT,
    CONSTRAINT categorias_fk_obras FOREIGN KEY (categoria_id) 
        REFERENCES categorias(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Columns:

Column	Type	Constraints	Description
`id`	BIGINT	PRIMARY KEY, AUTO_INCREMENT	Unique work identifier
`titulo`	VARCHAR(200)	NOT NULL	Work title
`descripcion`	TEXT	-	Work description
`duracion`	INT	NOT NULL	Duration in minutes
`categoria_id`	BIGINT	FOREIGN KEY → categorias(id)	Associated category

Foreign Key Constraints:

categorias_fk_obras: Links to categorias(id)
- ON DELETE CASCADE
- ON UPDATE CASCADE

Example Works:

Voces Silenciadas (90 min, Drama)
El Puente (95 min, Drama)
Luz Roja (80 min, Musical)
La Última Nota (100 min, Musical)

JPA Entity: Obras - src/main/java/com/trainee/Cinefinder/model/Obras.java:13

5. eventos_urbanos

Stores urban cultural events like street performances, festivals, and public art exhibitions.

CREATE TABLE IF NOT EXISTS CINES.eventos_urbanos (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    titulo VARCHAR(200) NOT NULL,
    descripcion TEXT,
    fecha DATE NOT NULL,
    lugar VARCHAR(200) NOT NULL,
    categoria_id BIGINT,
    CONSTRAINT categoria_fk_eventos_urbanos FOREIGN KEY (categoria_id) 
        REFERENCES categorias(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

Columns:

Column	Type	Constraints	Description
`id`	BIGINT	PRIMARY KEY, AUTO_INCREMENT	Unique event identifier
`titulo`	VARCHAR(200)	NOT NULL	Event title
`descripcion`	TEXT	-	Event description
`fecha`	DATE	NOT NULL	Event date
`lugar`	VARCHAR(200)	NOT NULL	Event location
`categoria_id`	BIGINT	FOREIGN KEY → categorias(id)	Associated category

Foreign Key Constraints:

categoria_fk_eventos_urbanos: Links to categorias(id)
- ON DELETE CASCADE
- ON UPDATE CASCADE

Example Events:

Festival de Callejeros (Plaza Bolívar, 2025-07-10)
Noche de Freestyle (Parque de los Deseos, 2025-08-05)
Arte al Paso (Calle 72, 2025-07-15)
Mural Fest (Ciudad Salitre, 2025-07-12)

JPA Entity: EventosUrbanos - src/main/java/com/trainee/Cinefinder/model/EventosUrbanos.java:15

6. participaciones

Links artists to movies, works, or events they participate in. This is a polymorphic relationship table.

CREATE TABLE IF NOT EXISTS CINES.participaciones (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    artista_dni BIGINT,
    tipo_evento ENUM('pelicula', 'obra', 'evento'),
    evento_id BIGINT,
    CONSTRAINT artista_participaciones FOREIGN KEY (artista_dni) 
        REFERENCES artistas(dni)
        ON DELETE CASCADE
);

Columns:

Column	Type	Constraints	Description
`id`	BIGINT	PRIMARY KEY, AUTO_INCREMENT	Unique participation identifier
`artista_dni`	BIGINT	FOREIGN KEY → artistas(dni)	Artist’s DNI
`tipo_evento`	ENUM	’pelicula’, ‘obra’, ‘evento’	Type of event
`evento_id`	BIGINT	-	ID of the event (references peliculas, obras, or eventos_urbanos)

Foreign Key Constraints:

artista_participaciones: Links to artistas(dni)
- ON DELETE CASCADE: Deleting an artist removes their participations

Note: This table implements a polymorphic relationship where evento_id can reference:

peliculas.id when tipo_evento = 'pelicula'
obras.id when tipo_evento = 'obra'
eventos_urbanos.id when tipo_evento = 'evento'

Example Participations:

Artist DNI 69374820 in pelicula ID 1
Artist DNI 71592047 in pelicula ID 2
Artist DNI 71920384 in evento ID 1
Artist DNI 74718290 in obra ID 3

JPA Entity: Participaciones - src/main/java/com/trainee/Cinefinder/model/Participaciones.java:16

Relationships

One-to-Many Relationships

categorias → peliculas

Type: One-to-Many
Description: One category can have many movies
Foreign Key: peliculas.categoria_id → categorias.id
Cascade: DELETE CASCADE, UPDATE CASCADE

categorias → obras

Type: One-to-Many
Description: One category can have many theatrical works
Foreign Key: obras.categoria_id → categorias.id
Cascade: DELETE CASCADE, UPDATE CASCADE

categorias → eventos_urbanos

Type: One-to-Many
Description: One category can have many urban events
Foreign Key: eventos_urbanos.categoria_id → categorias.id
Cascade: DELETE CASCADE, UPDATE CASCADE

artistas → participaciones

Type: One-to-Many
Description: One artist can participate in many events
Foreign Key: participaciones.artista_dni → artistas.dni
Cascade: DELETE CASCADE

Polymorphic Relationships

participaciones → peliculas/obras/eventos_urbanos

Type: Polymorphic Many-to-One
Description: A participation can reference a movie, work, or urban event
Implementation:
- tipo_evento ENUM determines the target table
- evento_id stores the referenced ID
Target Tables:
- tipo_evento = 'pelicula' → peliculas.id
- tipo_evento = 'obra' → obras.id
- tipo_evento = 'evento' → eventos_urbanos.id

Indexes

Primary Keys (Automatically Indexed)

categorias.id
artistas.dni
peliculas.id
obras.id
eventos_urbanos.id
participaciones.id

Unique Constraints (Automatically Indexed)

categorias.nombre
peliculas.titulo

Foreign Key Indexes

MySQL automatically creates indexes on foreign key columns:

peliculas.categoria_id
obras.categoria_id
eventos_urbanos.categoria_id
participaciones.artista_dni

Constraints Summary

NOT NULL Constraints

All primary keys
categorias.nombre
artistas.nombre
peliculas.titulo, peliculas.duracion
obras.titulo, obras.duracion
eventos_urbanos.titulo, eventos_urbanos.fecha, eventos_urbanos.lugar

UNIQUE Constraints

categorias.nombre - Prevents duplicate category names
peliculas.titulo - Prevents duplicate movie titles
artistas.dni - Each artist has a unique national ID

CASCADE Behaviors

DELETE CASCADE:

Deleting a category deletes all associated movies, works, and events
Deleting an artist deletes all their participations

UPDATE CASCADE:

Updating a category ID updates all references in movies, works, and events

Database Initialization

The database includes sample data for:

12 categories
12 artists
12 movies
12 theatrical works
12 urban events
12 artist participations

See the full SQL script at: /workspace/source/BD_CINES.sql

JPA Mappings

The database schema is mapped to JPA entities using:

@Entity - Marks the class as a JPA entity
@Table - Specifies the table name
@Id - Marks the primary key
@GeneratedValue - Auto-increment strategy
@Column - Maps to database columns
@ManyToOne - Defines many-to-one relationships
@JoinColumn - Specifies foreign key column

Example:

@Entity
@Table(name = "peliculas")
public class Peliculas {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Integer id;
    
    @ManyToOne
    @JoinColumn(name = "categoria_id")
    private Categorias categorias_id;
}

Best Practices

Use Transactions

Always use transactions when modifying multiple related records.

Validate Foreign Keys

Ensure referenced IDs exist before creating relationships.

Handle Cascades

Be aware of cascade delete behaviors when removing categories or artists.

Index Performance

The database is optimized with indexes on primary keys, unique columns, and foreign keys.

Next Steps

Architecture

Learn about the Spring Boot architecture and design patterns

Get Started

Core Concepts

API Reference

Guides

Documentation Index

​Overview

​Database Structure

​Entity Relationship Diagram

​Table Schemas

​1. categorias

​2. artistas

​3. peliculas

​4. obras

​5. eventos_urbanos

​6. participaciones

​Relationships

​One-to-Many Relationships

​categorias → peliculas

​categorias → obras

​categorias → eventos_urbanos

​artistas → participaciones

​Polymorphic Relationships

​participaciones → peliculas/obras/eventos_urbanos

​Indexes

​Primary Keys (Automatically Indexed)

​Unique Constraints (Automatically Indexed)

​Foreign Key Indexes

​Constraints Summary

​NOT NULL Constraints

​UNIQUE Constraints

​CASCADE Behaviors

​Database Initialization

​JPA Mappings

​Best Practices

Use Transactions

Validate Foreign Keys

Handle Cascades

Index Performance

​Next Steps

Architecture

Build docs developers (and LLMs) love

Overview

Database Structure

Entity Relationship Diagram

Table Schemas

1. categorias

2. artistas

3. peliculas

4. obras

5. eventos_urbanos

6. participaciones

Relationships

One-to-Many Relationships

categorias → peliculas

categorias → obras

categorias → eventos_urbanos

artistas → participaciones

Polymorphic Relationships

participaciones → peliculas/obras/eventos_urbanos

Indexes

Primary Keys (Automatically Indexed)

Unique Constraints (Automatically Indexed)

Foreign Key Indexes

Constraints Summary

NOT NULL Constraints

UNIQUE Constraints

CASCADE Behaviors

Database Initialization

JPA Mappings

Best Practices

Next Steps