Re: Mapping one to many - Mailing list pgsql-novice

First of thanks Michael for clearing things up, I do appreciate the
help. But I must say... wow... umm.... ok... this is not the first
time, and not the last, that I'v had to hammer data that doesn't fit
into a row/column format into a row/column format. I't kinda funny
that there isn't a tree equivalent to a RMDB. I'm sure alot of people
have to map tree like data into a RMDB and end up creating extra
tables to do the one to many mapping. I know this isn't the best
place to post this rant but hasn't any one come up with a better
solution. Idk may be the is some wonderful magic in RMDBs that I seem
to be missing, if so will some one please enlighten me. This problem
isn't limited to just tree like data but to other data organization
formats too. I guess what I'm trying to say is that the storage
medium should fit the data, not hammer the data to fit storage, but
that seems to be what were doing with RMDBs.

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
>