Database normalization with 'parent services' and 'child services'

Question

I am modeling a database which services belong to a category (used in most cases). Currently, categories are defined in the service table itself with a 'level' flag where I say whether that record is 'parent' or 'child'.
In this case, this would be the correct form of modeling of service categories?

Current example:

codigo | cod_pai | nivel  | nome  
 0001  |  0001   | pai    | Acessórios  
 0002  |  0001   | filho  | Item de Acessórios  
 0003  |  0001   | filho  | Item de Acessórios  
 0004  |  0001   | filho  | Item de Acessórios
 0005  |  0005   | pai    | Mesa e Banho
 0006  |  0005   | filho  | Item de Mesa e Banho
 0007  |  0005   | filho  | Item de Mesa e Banho

Answer 1

The third normal form (3FN) seeks to eliminate reductive values in the data of an application. In your case, the column information nivel is reductive, since by the column itself cod_pai you know if that column has a parent category or not.

In fact, the right one would be that the column cod_pai were NULLABLE - Thus, a tuple that does not have this value is the parent category, and tuples that have this value are the daughter categories (and potential parent categories).

Your model would look that way:

Answer 2

In addition to following @Rodrigo Rigotti’s recommendation, to leave the field cod_pai as Nullable, would also place a column Id if searching the categories via SQL is a common operation. This id would be filled via Trigger to facilitate queries and mounting of paths / trees.

The easiest way is to concatenate the id of the father record with the codigo as follows

codigo | cod_pai | id     | nome  
 0001  |  null   | 1.     | Acessórios  
 0002  |  0001   | 1.2.   | Item de Acessórios  
 0003  |  0001   | 1.3.   | Item de Acessórios  
 0004  |  0001   | 1.4.   | Item de Acessórios
 0005  |  null   | 5.     | Mesa e Banho
 0006  |  0005   | 5.6.   | Toalhas de Banho
 0007  |  0005   | 5.7.   | Toalhas de Mesa
 0008  |  0007   | 5.7.8  | Toalhas de mesa redondas

Thus, when searching for all daughter categories of Table and bath, could use WHERE cod_pai = 0005 if only the categories or WHERE id LIKE '5.%' if you wanted to search in subcategories.

PS: Note that a sequential Id has not been created, since the goal is not customer-view and only a simple, logical organization of the categories