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