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

Jane Williams jane at williams.nildram.co.uk
Thu Mar 21 06:09:01 CST 2002


Alliax <damiencola at wanadoo.fr> wrote :

> Hello,
> I'd like to take advices from the db gurus out there..
> I have a table for people:
> person { personID, name, sex, birthday }
>
> I'd like to create groups of persons, a person can be in none, one OR
> several groups.

> Here's what I thought:
> create a table for groups:
> group { groupID, listofpersons }
> where listofpersons is of type varchar(255) and a typical entry would look
> like:
> "2,4,7,11"
>
> where each number relate to a personID

Well, maybe you have some good reason for doing it that way that I haven't spotted. But the standard relational approach would be to have a Groups table:
group (groupID, groupDesc .....)

and an "intersection entity" that says who's a member of what:
member (groupID, personID)
where groupID must be in group.groupID and personID must be in person.personID. ("Foreign Key" is the DB buzz-word here).

You might have extra fields in the member table like "membership type" (pleb, normal, God), or "date joined", depending on what you're trying to do.

Then when you want to display all members of group 13, say:

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.

What DB are you using? and what method of getting data from it to the web page?






More information about the thelist mailing list