Skip to main content
The color table stores available color options for clothing items. Colors are referenced by products through the id_color foreign key, providing consistent color naming across the inventory.

​
Table Structure

​
id_color
int(11)
required
Primary key - Auto-incrementing unique identifier for each color
​
nombre
varchar(30)
required
Color name (e.g., “Negro Nocturno”, “Azul Marino”, “Rojo Pasión”)

​
SQL Definition

TiendaRopa.sql
CREATE TABLE `color` (
  `id_color` int(11) NOT NULL,
  `nombre` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

ALTER TABLE `color`
  ADD PRIMARY KEY (`id_color`);

ALTER TABLE `color`
  MODIFY `id_color` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

​
Sample Data

The database includes these default color options: 
INSERT INTO `color` (`id_color`, `nombre`) VALUES
(1, 'Negro Nocturno'),
(2, 'Blanco Pureza'),
(3, 'Azul Marino'),
(4, 'Rojo Pasión'),
(5, 'Gris Oxford');

Negro Nocturno

ID: 1 - Deep black color

Blanco Pureza

ID: 2 - Pure white color

Azul Marino

ID: 3 - Navy blue color

Rojo Pasión

ID: 4 - Passionate red color

Gris Oxford

ID: 5 - Oxford gray color

​
Relationships

​
Referenced By

prenda Table

Products reference colors through id_color foreign key
Foreign Key Constraint: 
ALTER TABLE `prenda`
  ADD CONSTRAINT `fk_prenda_color` 
  FOREIGN KEY (`id_color`) REFERENCES `color` (`id_color`);

​
Common Queries

​
List All Colors

SELECT * FROM color ORDER BY id_color ASC;

​
Products by Color

SELECT p.*, col.nombre as color_nombre
FROM prenda p
JOIN color col ON p.id_color = col.id_color
WHERE col.nombre = 'Azul Marino';

​
Color Popularity

SELECT col.nombre, COUNT(*) as productos
FROM prenda p
JOIN color col ON p.id_color = col.id_color
GROUP BY col.id_color
ORDER BY productos DESC;

​
Usage in Application

Colors are managed through colores.php:
  • Create: Add new colors with custom names
  • Update: Edit existing color names via modal dialog
  • View: Display all colors in a sortable table
Colors cannot be deleted if they are referenced by existing products due to foreign key constraints. This prevents data integrity issues.

​
Business Rules

  1. Unique Names: While not enforced by database constraint, avoid duplicate color names for clarity
  2. Descriptive Names: Use evocative color names (e.g., “Negro Nocturno” instead of just “Negro”)
  3. Consistency: Maintain consistent naming conventions across all colors
  4. Language: Color names are stored in Spanish to match the application locale

​
Extension Possibilities

Add a codigo_hex column to store hexadecimal color codes for visual representation in the UI:
ALTER TABLE color ADD COLUMN codigo_hex VARCHAR(7);
Group colors by category (warm, cool, neutral) for filtering:
ALTER TABLE color ADD COLUMN categoria ENUM('calido', 'frio', 'neutral');
Track which colors are available in specific seasons:
ALTER TABLE color ADD COLUMN temporada SET('primavera', 'verano', 'otono', 'invierno');

prenda Table

Product catalog that references colors

Catalog Management

Using the color management interface

Database Relationships

Foreign key relationships overview

Managing Categories

User guide for catalog management

Build docs developers (and LLMs) love

Get started for freeTalk to us