Table relationships in MySQL

If by necessary you mean mandatory , the answer is no , that is, you can create two tables and that, although they share information, they have no relationship with each other.

However, it is extremely advisable that you DO establish a relationship between the two and take care of the Referential Integrity of your data, since otherwise it would be technically possible to insert data from subcategories that do not have an existing category .

I'll pass you the MySQL documentation on Foreign Keys .

EDITED : I give you an example that will prevent you from having a subcategory without a parent category .

CREATE TABLE categoria (
    id INT NOT NULL,
    nom_categoria VARCHAR(15) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE sub_categoria(
    id INT,
    nom_subcategoria VARCHAR(15) NOT NULL,
    categoria_id INT,
    INDEX par_ind (categoria_id),
    FOREIGN KEY (categoria_id)
        REFERENCES categoria(id)
        ON DELETE CASCADE
) ENGINE=INNODB;

NEW ANSWER: A MUCH SIMPLER SOLUTION

The truth is that I was surprised by @PatricioMoracho's answer, which helped me to reconsider a much simpler solution to the problem to understand and maintain . His answer opened my eyes to an important limitation of the previous model: the possibility of nested categories and sub-categories!

Interestingly, 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 way .

Let's see:

Indeed, you can have a single tablecategorias to which we are going to add a new column called padre_id. In that column we will put the id of the parent of each category. If it's a parent category, we'll set that value to zero ( 0). That will help us to recover the main categories only, if we wish so...

The code presents a simple query that will display each category and its children next to it.

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 grandparent category through its parent category.

Código: Ver Demo

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 the foreign key, referential integrity, indexes, depending on the type of database (InnoDB, MyISAM) in the table creation.

Since you're just starting out, and some have questioned that I haven't put anything about referential integrity in my answer , I want to tell you that before going any further, it's important to decide on the type of engine your tables will have.

In MySQL there are two: MyISAMe InnoDB. Both have their advantages and disadvantages. For example this article (and others) can guide you: MyISAM or InnoDB? Choose your MySQL storage engine

You can also consult the official documentation of MySQL (in English):

• MyISAM

• InnoDB

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 to do so , I InnoDBrecommend that you read the documentation of said engine, and how the table creation syntax works, how the primary keys, the references, the on delete, the on update... etc. are indicated. It is a much broader topic than establishing a simple relationship. Depending on how you configure them on delete , for example, the child data will be deleted when the parent is deleted, so as not to leave orphaned data. You need to learn how it works before implementing it...

Código: Ver Demo

/*
-- 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

Juance, your question has already been answered, both A. Cedano's and David Isla's answers are valid. The issue is that I noticed a limitation in the model that you yourself were proposing, which is the category/subcategory model, and I asked you about it. Basically you can only manage two levels, you could not "hang" a subcategory from another subcategory, and in general only two levels of cataloging is usually not enough, just look at sites like MercadoLibre or others. My proposal is to handle a single table categoriaswithout distinction, which in a certain way is reasonable since they are all categories and what changes is the hierarchy of how we want to see them, which will be handled by an attributenodo. If at any time we need to move something or directly create a new level for all categories, this model is more flexible. It has an important complication : you have to take care of updating the nodo, it is not complex but it has its logic.

I extend A.Cedano's example a bit so you can see it:

DROP TABLE IF EXISTS categorias;

CREATE TABLE  IF NOT EXISTS categorias 
    (
      categoria_id          INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
      nodo                  VARCHAR(40),
      categoria_nombre      VARCHAR(40) NOT NULL
    );


INSERT INTO categorias (categoria_nombre, nodo) 
    VALUES  ('Categorias de Productos', '0001'), 
            ('Accesorios para celulares', '00010001'), 
            ('Celulares', '00010002'),
            ('Accesorios PC', '00010003'),
            ('Baterías', '000100010001'),
            ('Protectores', '000100010002'),
            ('Plásticos', '0001000100020001'),
            ('Vidrio', '0001000100020001'),
            ('Manos libres', '000100010003'),
            ('Teclado', '000100020001'),
            ('Monitor', '000100020001'),
            ('Mouse', '000100020001'),                
            ('Gama Alta', '000100020001'),
            ('Iphone 6', '0001000200010001'),
            ('Samsung S8', '0001000200010002'),
            ('Gama Media', '000100020002'),
            ('Gama Baja', '000100020002')
            ;   


SELECT  c1.nodo, 
        CONCAT(SPACE(LENGTH(c1.nodo)-LENGTH(c.nodo)),  c1.categoria_nombre) AS 'categoria_nombre'
    FROM categorias c
    INNER JOIN categorias c1
        ON LEFT(c1.nodo, LENGTH(c.nodo)) = c.nodo
    WHERE c.categoria_id = 1
    ORDER BY c1.nodo

The output would be something like this:

nodo             categoria_nombre
================ =====================================
0001             Categorias de Productos
00010001             Accesorios para celulares
000100010001             Baterías
000100010002             Protectores
0001000100020001             Plásticos
0001000100020001             Vidrio
000100010003             Manos libres
00010002             Celulares
000100020001             Gama Alta
0001000200010001             Iphone 6
0001000200010002             Samsung S8
000100020002             Gama Media
000100020002             Gama Baja
00010003             Accesorios PC
000100030001             Teclado
000100030001             Monitor
000100030001             Mouse

The indentation is just to visualize the hierarchy. Each product would have a categoria_idrelated table categoriasthat would point to the predefined level within the tree. Searching for products below a certain category is very easy to implement, for example if you want all Cell Phone Accessories, you just have to indicate the categoria_id = 2. The maintenance of the nodoif you are eventually going to create an ABM for it is not complicated but you have to be careful, such a structure is easier if the maintenance is done by script.

In the example that I am proposing, each branch or node could hold up to 9999 categories, and up to 10 levels, but this is easily extendable by modifying the field nodoand the update logic. A somewhat more advanced model would be to manage some additional tables jerarquiaand categoria_jerarquiato eventually manage different hierarchical models that depend on some criteria, for example "simple categories" or "extended categories" but I don't want to complicate your life, maybe this model will help you.

Additional information: How do we add more categories?

First of all, I recommend keeping a first main category base on the first level, in the "Product Categories" example, any new item must always go below it, simply because later the logic to handle insertions is easier . Let's see an example: suppose we want to add a new mobile accessory "Chargers", how do we do it?

• In the first place, you have to establish what the parent category is going to be, in this example it will be "Accessories for cell phones", in the query that I put together, I am not showing the ID but all the categories have an id, the one for Accessories is 2 , doing the following we will obtain the "parent" node and its length

  SELECT nodo AS NodoPadre,
          LENGTH(NODO) As LongitudNodoPadre
          FROM categorias
          WHERE categoria_id = 2 ;
  

• The "parent" node is 00010001the new item, we are going to insert it below this and at the end of the categories of that branch, so we need to calculate the final node, which would be something like this 00010001????, to calculate exactly the part of, ????we simply have to see what it is the last node of that branch and add one to it, like so:

  SELECT  CONCAT('00010001',RIGHT(CAST((10000 + CAST(MAX(right(nodo,4)) AS UNSIGNED ) + 1) AS CHAR(5)),4))
     FROM categorias
      WHERE nodo LIKE '00010001%'
            AND LENGTH(nodo) = 12;
  

• We are seeing the branch below the parent node, this because of it LIKE '00010001%'and also because we verify that the length of the node is 4 + the length of the parent node using LENGTH(nodo) = 12. The result would be: 000100010004, if you see the branch:

  nodo            categoria_nombre
  =============== ===================
  000100010001    Baterías
  000100010002    Protectores
  000100010003    Manos libres
  

  It is clear that we must insert the new category as:

  INSERT INTO categorias (categoria_nombre, nodo) 
          VALUES  ('Cargadores', '000100010004'), 
  

With this model, the insertions are always at the last level of the branch, eventually the logic could be built to reorder the items sequentially, making an "exchange" of the node between items, as for the delete, it is convenient that they be logical , using for example a FlagHabilitado(1/0) but eventually they could also be physical.

In some way, with the structure that you are proposing, you already have a relationship between categories and subcategories , but this is a mere reference, it is not physically in the database.

I consider it dangerous to use it without a physical relationship because the database will not manage the integridad referencial, also an index to that field concepto_idyou declared will not be created and in the future it can create performance problems in query and update operations if you use that column as a criterion .

In short, it is not necessary, but you assume unnecessary risks that you must cover from the programming.

I recommend reading about the llaves primariasand llaves foráneasbefore everything to understand better.