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

---------------------------(end of broadcast)---------------------------

TIP 2: Don't 'kill -9' the postmaster