Re: Mapping one to many

On Jun 13, 2007, at 10:05 , Nabil wrote:

> Ok this is a very simple problem but for some reason I'm suffering
> from brain freeze. I have two tables Users and Groups. A user can
> be a member of many different groups.

> What I was thinking of doing is creating a column called groups in
> users of type int[] that contains the ids of the groups the user is
> a member of. I want to make sure the group exists. The problem is I
> cant have Users.groups reference Groups.id.

Only use arrays for data types that are naturally arrays, i.e.,
you're treating the array as a value rather than accessing individual
elements of the array. As you've seen, relational databases are not
at their best when operating on array elements: relational databases
operate on tables, columns, and rows.

> Is there some kind of check I can do?

Not easily.

> If so what would happen if I delete a group that has members in it?

Good question :)

> One other way I though about was having a user_group_mapping table
> so that would have something like user_id that references Users.id
> and group_id that references Groups.id and when I want to figure
> out what groups a user is a member of I would do "SELECT group_id
> FROM user_group_mapping WHERE user_id=(the id I need)" but that
> seems kind of messy.

That's exactly how you *should* do it. It's a lot less messy than
what you'll go through trying to do it using arrays. :)

Michael Glaesemann
grzm seespotcode net