NEW ANSWER: A MUCH SIMPLE SOLUTION
The truth is that I was surprised by the response of @PatricioMoracho which helped me reconsider a much simpler solution to the problem of understanding and maintaining . His answer opened my eyes to a major limitation of the previous model: the possibility of nested categories and sub-categories!
Curiously, considering such complexity can lead us to solve the problem in a very simple way. It often happens that in life, what is complicated helps us to see that there is a simpler path .
Let's see:
Indeed, you can have a single table categorias to which we are going to add a new column called padre_id . In that column we will put the id of the father of each category. If it is a main category, we will set that value to zero ( 0 ). That will help us to recover the main categories only, if we wish ...
The code presents a simple query that will show each category and next to your children.
Obviously, more complex queries could be made, such as one that shows each category and its sub-categories by levels, or code that reaches the category grandpa through its parent category.
CREATE TABLE IF NOT EXISTS categorias
(
categoria_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
categoria_nombre VARCHAR(50) NOT NULL,
padre_id INT NOT NULL
);
INSERT INTO categorias (categoria_nombre, padre_id)
VALUES
('Teléfonos',0),
('Celulares',1),
('Accesorios para celulares',2),
('Baterías',3),
('Protectores',3),
('Manos libres',3),
('Computadoras',0),
('Computadoras de Escritorio',7),
('Computadoras Portátiles',7),
('Teclado',8),
('Monitor',8),
('Mouse',8),
('Funda de portátil',9);
SELECT c.categoria_nombre as padre,
GROUP_CONCAT(hijos.categoria_nombre SEPARATOR ",") AS hijos
FROM categorias c
INNER JOIN categorias hijos
ON c.categoria_id=hijos.padre_id
GROUP BY hijos.padre_id;
Resultado:
padre hijos
1 Teléfonos Celulares
2 Celulares Accesorios para celulares
3 Accesorios para celulares Baterías,Protectores,Manos libres
4 Computadoras Computadoras de Escritorio,Computadoras Portátiles
5 Computadoras de Escritorio Teclado,Monitor,Mouse
6 Computadoras Portátiles Funda de portátil
@deprecated
This is an old answer
What is usually done in these cases is something like this.
Note: This is a basic example . It would be necessary to add in the table creation the foreign key, referential integrity, indexes, according to the type of database (InnoDB, MyISAM).
Given that you are starting, and some have questioned that in my answer I have not put anything on referential integrity , I want to tell you that before moving on it is important to decide the type of engine that will have your tables.
In MySQL there are two: MyISAM e InnoDB . Both have their advantages and their disadvantages. For example, this article (and others) can orient you: MyISAM or InnoDB ? Choose your MySQL storage engine
You can also check the official MySQL documentation (in English):
The code presents a basic example and a query that would work if you want to get the sub_categories of a category x.
If you decide for InnoDB I recommend that you read the documentation of that engine, and how the table creation syntax works, how the primary keys are indicated, the references, the on delete , the on update .. . etc. It is a much broader issue than establishing a simple relationship. As you set the on delete for example, the child data will be deleted when the parent is deleted, so as not to leave orphan data. You need to find out how it works before implementing it ...
/*
-- ATENCIÓN NO USE DROP TABLE CON SUS TABLAS REALES
-- YA QUE DROP TABLE BORRARÁ SUS DATOS
-- DROP TABLE SE USA AQUÍ SÓLO PARA PODER PROBAR LOS DATOS */
DROP TABLE IF EXISTS categorias;
DROP TABLE IF EXISTS sub_categorias;
/* NO COPIE LA SENTENCIA ^ DROP TABLE ^ EN SUS DATOS REALES */
CREATE TABLE IF NOT EXISTS categorias
(
categoria_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
categoria_nombre VARCHAR(50) NOT NULL
);
CREATE TABLE IF NOT EXISTS sub_categorias
(
subcategoria_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
subcategoria_nombre VARCHAR(50) NOT NULL,
categoria_id INT (10)
);
INSERT INTO categorias (categoria_nombre)
VALUES ('Accesorios para celulares'), ('Accesorios PC');
INSERT INTO sub_categorias (subcategoria_nombre,categoria_id)
VALUES
('Baterías', 1),
('Protectores', 1),
('Manos libres', 1),
('Teclado', 2),
('Monitor', 2),
('Mouse', 2)
;
SELECT subcategoria_nombre FROM sub_categorias sc
INNER JOIN categorias c
ON sc.categoria_id=c.categoria_id
WHERE sc.categoria_id=1;
Resultado
subcategoria_nombre
1 Baterías
2 Protectores
3 Manos libres