any of you have ever used Closure Table to represent the hierarchy users groups ? In all the examples I found online the id of the node is always unique within the structure.
Try to imagine this issue : In a classic security management in my software I have a list of users and a list of groups, I'd like to make sure that a user can belong to several groups and each group can in turn contain other groups .
Users table :
Id | UserType | Name
( UserType is 0 if the record represents a user , if group 1 )
table UserHC Id | ParentId | Level
Analyzing the classic command to insert already known that if " MyID " is already present (since the primary key is Id, ParentId ) would not be good .
INSERT INTO UserHC ( [Id], [ParentId], [Level] ) select {MyID} as [Id], [ParentId], [Level] +1 from UserHC where [Id] = {myid} UNION ALL SELECT {MyID}, {myId} , 0
*Where MyId is new Users.Id
Before i make a complex analysis I was wondering if any of you have already had a similar requirement and how you resolved it .
thanks
