SitePoint Sponsor

User Tag List

Results 1 to 21 of 21
  1. #1
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Exclamation Working with Lists and the IN Operator in SQL 2005

    A short setup on the Table:

    Table : Auth_Groups

    Auth_Group_ID (PK) int, not null
    Auth_Group_Name varchar(256), not null
    FK_NW_Personnel_IDs (varchar(1024), not null
    FK_Template_IDs (varchar(1024), not null
    FK_Auth_Right_IDs text, not null
    Auth_Group_Description text, null
    Auth_Group_Is_Enabled bit, not null

    I'm trying to run the following code:

    Code:
    SELECT
      AG.Auth_Group_ID,
      AG.Auth_Group_Name,
      AG.FK_Auth_Right_IDs
    FROM
      Auth_Groups AS AG
    WHERE
      CONTAINS ( AG.FK_Template_IDs, '#REQUEST.Queries.qryTemplate.Template_ID#' )
    AND
      AG.Auth_Group_Is_Enabled = 1
    Right now it's returning an error back saying that Full Indexing is not enabled, but that's beside the point. What I'm trying to do is the equivalant of a ListFindCase for SQL.

    The REQUEST.Queries.qryTemplate.Template_ID is returning the number 69 (which is the Template ID of the page the user is on)

    What I'm trying to do is tell it to check if that value (69) is in the value that is in the AG.FK_Template_IDs field. (There is 1 record in the Auth_Groups table and in the FK_Template_IDs field, the value in there is:

    69,70,71

    So I basically want SQL to return that record because the 69 (a value we capture from ColdFusion) exists in the list value of the FK_Template_IDs field. How do I do this? I've tried LIKE operators, but they work where you could say something like:

    Code:
    ... WHERE #REQUEST.Queries.qryTemplate.Template_ID# IN ( AG.FK_Template_IDs ) ...
    But this doesn't work either.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    would you mind telling us which database system this is?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    by the way, this is a very bad idea --

    FK_Template_IDs (varchar(1024), not null

    is there any chance you can design this properly?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Oh, I thought I had stated in the title SQL 2005. It's for Windows if that helps.

    I heard that this was a bad way to set things up, but the alternate sounds SO much worse.

    We have over 600 employees. If we have 30 templates, and only reference 1 NW_Personnel_ID to 1 Template per record, is that not 600^30 records?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by aaron.martone View Post
    Oh, I thought I had stated in the title SQL 2005.
    in the immortal words of phil mickelson, i am such an idiot

    Quote Originally Posted by aaron.martone View Post
    ... is that not 600^30 records?
    probably not, but i don't understand what your tables do

    for instance, this table is about auth groups, whatever they are, related to several templates

    what's a template, anyway? and can an auth group have only a few of them, or does it have to have all 30?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    If you're an idiot, I'm a blithering imbecile.

    What we're trying to do is create a window-esque rights system for our Admin Control Panel (ACP). There are 4 tables we're using:

    Auth_Groups ( Groups users can belong to as well as what rights those groups infer )
    Auth_Rights ( The individual rights users can have, such as Access, View, View Own, Edit, etc. )
    Auth_Users ( The users as well as what groups they belong to )
    Auth_User_Rights ( The individual (non-group based) rights that are applied onto a user for any given location in the ACP )

    The way it works is, for a given template location (which has an ID), the system checks which group you belong to, and you gain those rights. It then checks to see if you have specific user rights for that same page, and if you do, those user rights override the group rights.

    For example, if on the News page of the ACP, you are a member of the "News Moderators" group, then you gain the rights of "Add, Edit, Delete, View and Access". But if we wanted to explicity deny your particular editing and deleting, we'd make an entry into the User Rights table for that template and user's NW Personnel ID (Identifies the user) stating -Edit, -Delete, and these rights override the group ones, so in the end you only have Acces, View and Add rights.

    As far as simplifying the database structure, I kind of felt a list would help us here, but I just can't seem to find any list-based functionality in SQL. Made me wish there was such a thing as CF-SQL.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    there is plenty of "list" processing in SQL -- except instead of stringing the values out into a single concatenated long character string per row, you would stack the values in a column, such that there is one row per column

    your method uses a very short (1 row) but very wide value, which needs to be "exploded" or "unstrung" with list functions before the individual pieces can be manipulated, or even before you can determine if a specific individual value is even in there...

    proper database design replaces this with a one-to-many table which is very narrow (FK plus 1 column) but as tall as necessary, one row per value

    then you can use all kinds of set-based functions which operate on the column of values, rather than list-based functions

    the deal-breaker is the efficiency of searching for a specific individual value

    your method requires a table scan, retrieving all rows, unstringing the values in each row, and only then being able to determine if a value is present

    the one-to-many best practice table design just uses a simple WHERE condition, which can be indexed for optimum performance

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    So in the given table design, I belong to the "Web Admins" group. This group is supposed to have complete control over the ACP. If there are 30 templates in the ACP, and there are 7 employees that are Web Admins as well, that means I would need:

    1 entry per person (7) per template (30) for the group.

    So that's 210 rows just to handle us.

    I would estimate there are 600 employees, 30 templates and probably 100 groups.

    That would make for 1.8 million rows, but this would still be the better way to design a database? Hmm. No wonder I'm not DB designer. As a coder, I felt that many entries was the long-way about doing this, and that there had to be "something" that would make this easier.

    The authorization system we had in place prevents any 1 person from having more than 1 group for a particular template (otherwise there would be over 30 million rows)

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    why do you need to assign 30 templates to each person? why do you need to assign templates to a person at all? aren't templates assigned to groups, and people get to use the templates of the group they're in?

    i think you need to go back to the drawing board and re-understand your data
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You do not have to belong to a group or have user-defined rights when you are at a template, but if that template calls for an authorization that you don't have rights for, you get a "forbidden" error (fails authorization)

    If you are assigned to a group, you can only be assigned to 1 per template. And you can have no more than 1 user-defined rights (although as it currently stood, though this may have to change too, that 1 user-defined right could be multiple rights, since it too was a comma delimited list of the IDs of rights)

    And in the end, user-defined rights had precedence over group defined. So if a group-defined right explicitly said you could not delete records, but a user-defined one said you could, you would end up with rights to delete.

    I've never developed an authorization system before, and aside from the ability to add groups to groups or have 1 person be a member of multiple groups, I tried to model it off of Windows' file system.

    I know I have users, templates, rights and groups. And I know I want to use the following limintations:

    1. For each template, a user can belong UP TO 1 group.
    2. For each template, a user can have as many user-defined rights that override group rights.
    3. Specific Groups are designed for specific templates (ie, the group "News Editor" would probably only be legitimately checked for on our News Editing area of the ACP.

    I fleshed this out on the whiteboard, and structurally it is flawless when you use ColdFusion. But my knowledge of SQL is limited, and with the advice that I shouldn't use lists to indicate individual primary keys of other table fields, it "expands" much of what I've setup.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    any chance you could share the actual design? can you script the tables for us?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,653
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    For your current design, you might be able to shift that field over to being XML, then doing Xpath queries against that. Schema-bind it and you can even index it. But, as Rudy points out using SQL's set-based funk schewa is really the way to go here.

    What it sounds like is you really want an addative rights system, which windows isn't quite as deny is uber alles. Anyhow, if I were to be solving this principally in SQL, I would do something like this:

    a) users table: whatever you need, only field that matters for this exercise is ID
    b) groups table: see above
    c) UsersInGroups: many-many join of a & b
    d) Objects: table of objects principals can have rights to. Only field that matters is ID for this demo.
    e) PrincipalRights: first, if you weren't using GUIDs as the ID (or UNIQUEIDENTIFIER in Sql Server), you might want to start. For the record, that is what Windows uses to track principals. This table relates (in a purely logical, no SQL enforcement) to a & b and contains:
    --1) ID from a OR b. This field is a dual key with . . .
    --2) ObjectId: ID of the object.
    --3) SMALLINT fields for each different user right, you are really just using 1 and 0 but BIT won't cut the mustard (see below).

    Then you can just do something like:

    Code:
    SELECT SUM(%user rights field%) FROM
    PrincipalRights WHERE
    @ObjectId=ObjectId AND 
    (@UserId=PrincipalRights.Id OR 
    PrincipalRightsId IN
    (Select ID FROM Groups WHERE UserId=@UserId))
    If the query returns NULL or 0, the principal doesn't have permissions to the object. If it does return > 0, it does have permissions to the object. With proper indexing something like this will scale much, much better than anything that involves parsing fields in WHERE clauses.

  13. #13
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,192
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by aaron.martone
    Auth_Groups ( Groups users can belong to as well as what rights those groups infer )
    You should break this up into three tables:

    rights
    -- id

    groups
    -- id

    group_rights
    -- group_id
    -- right_id

    1. The first will only store rights.
    2. The second will store groups.
    3. The third will store the rights a group has.


    Quote Originally Posted by aaron.martone
    Auth_Rights ( The individual rights users can have, such as Access, View, View Own, Edit, etc. )
    Now that the rights table is separate it can be reused in a similar manor for users.

    users
    -- id

    This table would connect a template to a user on the basic level.

    user_templates
    -- id
    -- template_id
    -- user_id

    Then add a table for optional rights.

    -- user_template_id (references primary key of user_templates)
    -- right_id

    Quote Originally Posted by aaron.martone
    Auth_Users ( The users as well as what groups they belong to )
    Another lookup table becasue the groups table is separate. This table is responsible for telling you who is in each group.

    user_groups
    -- user_id
    -- group_id

    Now if you would also like to assign templates to a group you could use the below table structure.

    templates
    -- id

    group_templates
    -- template_id
    -- group_id

    Now a group can have shared rights for multiple templates.

    That should get you started down your normalization path.

  14. #14
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,192
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    This is how I would begin:

    templates
    • id (primary key)
    • name


    groups (if only one template may associated with a single group)
    • id (primary key)
    • template_id (references (templates(id))
    • name


    rights
    • id (primary key)
    • name


    group_rights
    • group_id (references (groups(id))
    • right_id (references (rights(id))
    • primary key(group_id,right_id)


    users
    • id (primary key)
    • name


    user_template_rights
    • user_id (references (users(id))
    • template_id (references (templates(id))
    • right_id (references (rights(id))


    group_users
    • user_id (references (users(id))
    • group_id (references (groups(id))
    • primary key(user_id,group_id)


    template_rights (If certain templates require certain rights to be accessed)
    • template_id (references (templates(id))
    • right_id (references (rights(id))
    • primary key(template_id,right_id)

  15. #15
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,192
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    Instead of only allowing one template for a group though I would opt for a many to many relationship to easily create a super user.

    groups
    • id (primary key)
    • template_id (references (templates(id))
    • name


    group_templates (factor group => template relationship into new table)
    • group_id (references (groups(id))
    • template_id (references (templates(id))
    • primary key(group_id,template_id)
    • name


    If you wanted to you could get even fancier and factor out the group rights as well based the primary key for group_templates table. This would allow precise control over rights a group has per a individual template.

    group_templates (factor group => template relationship into new table)
    • id (primary key)
    • group_id (references (groups(id))
    • template_id (references (templates(id))
    • unique key(group_id,template_id)
    • name


    group_template_rights (factor group_template => rights relationship into new table)
    • group_template_id (references (group_templates(id))
    • right_id (references (rights(id))
    • unique key(group_template_id,right_id)
    • name

  16. #16
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You know, out of left field, our coder learned some SQL and built a Scalar Function called ListFind :

    Code:
    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go
    
    
    ALTER FUNCTION [dbo].
    [ListFind](@MyString varchar(8000), @MySearchString varchar(50), @MyDelim as char(1))  
        RETURNS INT 
        AS  
        BEGIN 
            DECLARE @MyPos INT
            DECLARE @MyReturnPos INT
            DECLARE @MyPointer1 INT
            DECLARE @MyPointer2 INT
            DECLARE @MyToken varchar(50)
                 
            SET @MyPos = 0
            SET @MyReturnPos = 0
            SET @MyToken = NULL
            SET @MyPointer1 = 0
            SET @MyPointer2 = -1
                 
            WHILE @MyPointer2 <> 0 AND @MyString IS NOT NULL
                BEGIN
                    SET @MyPointer2 = CharIndex(@MyDelim,@MyString,@MyPointer1+1)
                    
                    IF @MyPointer2 <> 0
                        SET @MyToken = SubString(@MyString,@MyPointer1+1,@MyPointer2-@MyPointer1-1)
                    ELSE
                        SET @MyToken = SubString(@MyString,@MyPointer1+1,LEN(@MyString))
                        SET @MyPos = @MyPos + 1
                    IF @MyToken = @MySearchString
                        SET @MyReturnPos = @MyPos
                        SET @MyPointer1 = @MyPointer2
                END
            RETURN @MyReturnPos
        END
    This allowed us to keep the comma delimited list and use the SQL like:

    Code:
        SELECT
            AG.Auth_Group_ID,
            AG.Auth_Group_Name,
            AG.FK_Auth_Right_IDs
        FROM
            Auth_Groups AS AG
        WHERE
            dbo.ListFind( AG.FK_Template_IDs, '#REQUEST.Queries.qryTemplate.Template_ID#', ',' ) > 0
        AND
            Auth_Group_Is_Enabled = 1 -- Enabled
    And it seems to be working in the manner we're looking for. Is this problematic?

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by aaron.martone View Post
    Is this problematic?
    yup—it won't scale in searches

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,653
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    I'd expand that to "there is no more surefire way to kill your database's scalability, save using pointers." Especially with something like permissions, which can easily get hammered.

    As I said, if you are wedded to the "single DB field for permissions" model, you really should look at XML. That can at least be indexed to some extent. But you really shouldn't work with this sort of model.

    PS: I should add recursive triggers to the first sentence.
    Last edited by wwb_99; May 13, 2009 at 08:59. Reason: PS . . .

  19. #19
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Dangit. This is killing me. I'm on an impossibly tight deadline, and what I'm going to have to do is use the Scalar function that was written, and if/when I get the time, I'll have to redesign the database to accommodate this at a later time, if it is possible at all.

    The ACP won't get as many hits as the public site it's controlling ( 1/2 a million hits a month ) so maybe I'll get time to re-do this.

  20. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    reminder for next time: getting the database design right is a task that comes at the beginning of the development cycle (before any code is written), not at the end

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  21. #21
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    These tables are merely 4 of currently 15 that the database has. They were added down the line and their development was rushed only because the higher ups want progress in X time and really don't want quality control.

    But the perfectionist in me can't stand this type of rushed development. 12 days to make the ACP with no framework in place, and we've spent 3 days just fending off emails and input from the public on the site... ugh.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •