Re: Modeling Group Membership

On May 23, 12:34 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> pietro...@gmail.com wrote:
> > 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?
>
> 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.
> jstuck...@attglobal.net
> ==================

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 wrote:
> On May 23, 12:34 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>> pietro...@gmail.com wrote:
>>> 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?
>> 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.
>> jstuck...@attglobal.net
>> ==================
>
> 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?
>

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 -- hamzee@sbcdeglobalspam.net
To reply to me directly, delete "despam".

pietromas@gmail.com wrote:
> On May 23, 12:34 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>> pietro...@gmail.com wrote:
>>> 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?
>> 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.
>> jstuck...@attglobal.net
>> ==================
>
> 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?
>

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.
jstucklex@attglobal.net
==================