Professional Web Applications Themes

Modeling Group Membership - MySQL

Suppose I have records modeling users. Each user can be a member of any number of groups (like how a unix user can be a member of any number of groups). Whats the best way to implement this? I think I would prefer if it was possible to have an enum field in the user table which could store multiple values per record (one per group) but the only practical way I can think to do it is to create tables for each group and store the users primary key in the tables corresponding to their group membership. Following me? ...

  1. #1

    Default Modeling Group Membership

    Suppose I have records modeling users. Each user can be a member of
    any number of groups (like how a unix user can be a member of any
    number of groups). Whats the best way to implement this?

    I think I would prefer if it was possible to have an enum field in the
    user table which could store multiple values per record (one per
    group) but the only practical way I can think to do it is to create
    tables for each group and store the users primary key in the tables
    corresponding to their group membership.

    Following me? Any better ideas?

    pietromas@gmail.com Guest

  2. #2

    Default Re: Modeling Group Membership

    com wrote: 

    Google for "database normalization" - it should help.

    You need a table which contains two columns - a group id and a user id.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  3. #3

    Default Re: Modeling Group Membership

    On May 23, 12:34 pm, Jerry Stuckle <net> wrote: 


    >
    > Google for "database normalization" - it should help.
    >
    > You need a table which contains two columns - a group id and a user id.
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > net
    > ==================[/ref]

    They would form a joint primary key then. Does that mean that
    selecting All users in a group and All groups of a user would be
    equally efficient?

    pietromas@gmail.com Guest

  4. #4

    Default Re: Modeling Group Membership

    On May 23, 12:34 pm, Jerry Stuckle <net> wrote: 


    >
    > Google for "database normalization" - it should help.
    >
    > You need a table which contains two columns - a group id and a user id.
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > net
    > ==================[/ref]

    They would form a joint primary key then. Does that mean that
    selecting All users in a group and All groups of a user would be
    equally efficient?

    pietromas@gmail.com Guest

  5. #5

    Default Re: Modeling Group Membership

    On May 23, 12:34 pm, Jerry Stuckle <net> wrote: 


    >
    > Google for "database normalization" - it should help.
    >
    > You need a table which contains two columns - a group id and a user id.
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > net
    > ==================[/ref]

    They would form a joint primary key then. Does that mean that
    selecting All users in a group and All groups of a user would be
    equally efficient?

    pietromas@gmail.com Guest

  6. #6

    Default Re: Modeling Group Membership

    com wrote: 
    >> Google for "database normalization" - it should help.
    >>
    >> You need a table which contains two columns - a group id and a user id.
    >>
    >> --
    >> ==================
    >> Remove the "x" from my email address
    >> Jerry Stuckle
    >> JDS Computer Training Corp.
    >> net
    >> ==================[/ref]
    >
    > They would form a joint primary key then. Does that mean that
    > selecting All users in a group and All groups of a user would be
    > equally efficient?
    >[/ref]

    yes it would be efficient specially if you create right index fields on
    them. keep in mind that you still need a user table and a group table to
    identify those entities. the joint table is just that: a table that
    joins the user and group tables.

    --
    lark -- net
    To reply to me directly, delete "despam".
    lark Guest

  7. #7

    Default Re: Modeling Group Membership

    com wrote: 
    >> Google for "database normalization" - it should help.
    >>
    >> You need a table which contains two columns - a group id and a user id.
    >>
    >> --
    >> ==================
    >> Remove the "x" from my email address
    >> Jerry Stuckle
    >> JDS Computer Training Corp.
    >> net
    >> ==================[/ref]
    >
    > They would form a joint primary key then. Does that mean that
    > selecting All users in a group and All groups of a user would be
    > equally efficient?
    >[/ref]

    Yes, it's common to have multiple columns form the primary key in a link
    table.

    The index will be created on the columns in the order you specify.
    MySQl can use the index starting with the first column.

    For instance, if you specify the primary key as (userid, groupid), MySQL
    can use the index when searching/sorting on userid. But the groupid
    isn't in order by itself in the index, so there's no way for MySQL to
    use it.

    The simple answer if you need both it to just create another index on
    groupid.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

Similar Threads

  1. RoleProvider for AD Group membership
    By Olivier in forum ASP.NET Security
    Replies: 7
    Last Post: November 8th, 03:18 AM
  2. Getting Group Membership
    By Raterus in forum ASP.NET Security
    Replies: 3
    Last Post: September 12th, 09:47 AM
  3. Checking group membership
    By Jan Nielsen in forum ASP.NET Security
    Replies: 0
    Last Post: September 2nd, 08:07 PM
  4. LDAP group membership query
    By Paul in forum PERL Miscellaneous
    Replies: 1
    Last Post: July 29th, 06:03 PM
  5. Membership of group
    By Khris in forum Sun Solaris
    Replies: 1
    Last Post: July 17th, 09:39 AM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139