0

I have the following table data:

SELECT * from Autenticacao_Permissoes;

PermissionId    PermissionName                     RoleId   
------------    --------------                     ---------
1               Pages.Clientes.Editar.Test         3
2               Pages.Sinistros.Editar             3
3               Pages.Hack.Ver.Editar.Comer.Maca   3
4               Pages.Sinistros.VerFolder3         3
5               Pages.Hack.Ver                     NULL

There is no limit to the permissions' depth and I would like to have a temporary table with the following format:

SiteMapId    Title                     RoleId    ParentId
---------    ------------------------  ------    --------
1            Pages                     NULL      NULL       
2            Clientes                  NULL      1
3            Sinistros                 NULL      1
4            Hack                      NULL      1
5            Editar                    NULL      2
6            Test                      3         5
7            Editar                    3         3
...

The idea is to use it according to the ASP.NET sitemap data structure.

jiten
  • 11
  • 2
  • 1
    Never ever store data as dot separated items. It will only cause you lots of trouble. – jarlh Jan 05 '17 at 09:11
  • ... or any kind of delimited data... Read [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad), where you will see a lot of reasons why the answer to this question is **Absolutly yes!** – Zohar Peled Jan 05 '17 at 09:27
  • What version of sql server are you working with? – Zohar Peled Jan 05 '17 at 09:34
  • So what would you recommend? I'm using SQL Server 2008 R2. – jiten Jan 05 '17 at 09:37
  • Store it in the hierarchical format you are asking us to generate in the first place. That format is not uncommon, particularly when holding data about employees with a key value that references their manager, from the same employee table. – iamdave Jan 05 '17 at 09:39
  • I was trying to replicate ASP.NET Zero permission management module and make it compatible to the hierarchical Sitemap structure that I'm using at the moment. – jiten Jan 05 '17 at 09:43

0 Answers0