Re: Mapping one to many

Yeah, what Michael said.

Though if you're really insistent on having foreign key constraint-
like behavior with arrays, you could try either creating a stored
procedure (e.g. delete_group()) that you call to delete groups from
the table and perform cleanup/checking of stuff in the users table,
or you could create a row-level triggers for the groups table to
emulate the same sort of trigger mechanism that foreign key
constraints use internally. Use this speculative advice at your own
risk.

--Ilan

On Jun 13, 2007, at 11:49 AM, Michael Glaesemann wrote:

>
> 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
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

Ilan Volow
"Implicit code is inherently evil, and here's the reason why:"