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