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

Question

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

Navigation

#1 by (3 votes)
#2 by (2 votes)

2

I am modeling a database that services belong to a category (used in most cases). Currently, the categories are defined in the service table itself with a 'level' flag where I say if that record is 'parent' or 'child'.
If so, would that be the correct way to model 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

asked by anonymous 17.11.2014 / 15:16

2 answers

Lock / fade screen during postback Line break in email message

score 3 · Answer 1

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

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

Your model would look like this:

score 2 · Answer 2

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

The easiest way is to concatenate id of the parent record with 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

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

PS: Note that a sequential Id was not created since the goal is not a client-only view and a simple logical organization of the