2. PHPRunner General questions
  3. Tags in PHPRunner with a master detail relationships
This topic is locked

Tags in PHPRunner with a master detail relationships

 		8/29/2022 10:59:52 AM
PHPRunner General questions
Davor Geci authorDevClub member

Hello,
I see that there is a custom control in Marketplace for implementing Tags to our applications. But is uses 1 field with comaseparated values.
Is there a way or a control to implement Tags but where Tags are stored in a details table.
For Example if we have a Products table and Colors table where we have a master detail relationship:

tblProducts
ProductID (PK)
ProductName

tblColors
ColorID (PK)
Color

tblProductColor
ProductColorID (PK)
ProductID
ColorID
fhumanes 8/29/2022

Hello,

Normally, these denormalizations of the relational model are easily resolved with the "find_in_set" function.

Look at these examples: https://www.mysqltutorial.org/mysql-find_in_set/

Cheers,
fernando
Davor Geci authorDevClub member 8/29/2022

Thanks Fernando for your reply and for the suggestion.
But I would like to use the tags also in other scenarios and be able to show what tags are available, defind the colors of the tags,.... And for this I think that the separete table is the way to go.
fhumanes 8/30/2022

Hello,

I just gave this respect to another user that I have asked the same question.

It is quite easy to solve.

I will take an example that I have to explain how it is resolved.

  • We have this data model.

img alt

With the ball (1), you see that we have a standardized relationship of many to much, in this case a film can have many issues.

With ball (2), we have a relationship of many to many, but we store the ID's of the interpreters in a field of the "film" record, which is what you have.

In both cases, information (normalized and grouped) can be available, regardless of how it is stored.

It is resolved very well with database views, in this case:

(1) Change to Item grouped:

CREATE VIEW `v_vide_temas` AS
select `vide_rel_pelicula_tema`.`vide_pelicula_idvide_pelicula` AS `vide_pelicula_idvide_pelicula`,
cast(group_concat(`vide_rel_pelicula_tema`.`vide_cata_tema_idvide_cata_tema`
order by `vide_rel_pelicula_tema`.`vide_cata_tema_idvide_cata_tema` ASC separator ',')
as char charset utf8) AS `vide_cata_tema_idvide_cata_tema`
from `vide_rel_pelicula_tema`
group by `vide_rel_pelicula_tema`.`vide_pelicula_idvide_pelicula`

Use of the Group_Concat function

(2) Your case, for a standardized model:

CREATE VIEW `v_vide_peli_interprete` AS
select `p`.`idvide_pelicula` AS `idvide_pelicula`,
`i`.`NombreyApellidos` AS `NombreyApellidos_Interprete`
from
(`vide_pelicula` `p`
join `vide_cata_interprete` `i`)
where (find_in_set(`i`.`idvide_cata_interprete`,`p`.`vide_cata_interprete_idvide_cata_interprete`) > 0)

Use of the Find_in_set function

Cheers,
fernando
Davor Geci authorDevClub member 8/31/2022

Thank you Fernando,
this is an interesting approach.
I will try it out.

Davor