[thelist] Database wise, how to create groups..

[rudy]

>  select m.membershipType, p.name, p.birthday
>    from person p, member m
>  where p.personID = m.personID
>      and m.groupID = 13
> order by m.membershipType, p.name
>
> That's Oracle syntax: other DBs will be similar but not identical.

actually, that'll run fine in all of them (no oracle-isms included)

nice explanation of the intersection table, too, jane

the additional data fields (your examples of membership type and date
joined were excellent) are really hard to handle in any structure other
than an intersection table

damien, it is possible to go with the comma-separated list, even though it
breaks some rules

the caveats are:

1) all your queries are belong to one direction, from person to group, and
you never try to go in the other direction, from group to person

   select p.name, p.birthday, g.desc
     from person p, group g
   where p.something = foo
      and g.groupid in p.grouplist

when this query selects a person (p.something=foo), that row is joined to
the groups table by matching the group ids to each of the values in the
person's grouplist

i haven't actually tested this syntax anywhere, but i'm confident it should
work (except perhaps in databases that balk at an IN-list check involving a
field rather than literal values)

selecting a group first, and then trying to find all the people that have
that group included in the grouplist, well, that would work, except it
would be horribly inefficient

2) you're sure the intersection will never carry additional data besides
the keys

because the day you need to add another data field, and realize you do need
an intersection table, all your queries are broken


<tip type="designing a many-to-many intersection table">

a many-to-many intersection table will have a foreign key to table A and
another foreign key to table B, and perhaps some additional data fields,
but you usually (see note below) do not need to declare a separate primary
key for the intersection table

    create table AintersectB
       ( tableAfk integer
       , tableBfk integer
       , dateadded datetime
       , otherstuff  char(20)
       , foreign key tableAfk
             references tableA (tableApk)
                 on delete restrict
       , foreign key tableBfk
             references tableB (tableBpk)
                 on delete restrict
       , primary key (tableAfk, tableBfk)   )

do not be misled by the table name AintersectB, this table works in both
directions no matter which order the keys are physically declared

since the purpose of a primary key is to ensure uniqueness, you can declare
the two columns in either order

some databases automatically create an index on the primary key, so this
index would be a compound index, giving great performance in only one
direction (the first key declared) -- for processing efficiency, you may
want to declare an additional simple index on the other key

NOTE: if you want more than one entry in the table for a specific fk pair
(e.g. to track membership history -- the hint will be a column called
"active" or something), then perforce you must declare a separate primary
key for the intersection table

do not declare the separate pk unless you have to allow for this multiple
membership

</tip>


rudy
http://rudy.ca/