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

I'm a little confused...

Is it true that one member can belong to many groups, and that one group can have many members?

On 6/13/07, Nabil < Nabil@kramer-smilko.com> wrote:

Kind of sort of no.... A group doest really need to know it members but users (members) need to know what groups they belong to. In other words your mapping one user to many groups. But it is also true that the two different users can map to the same group.

On Jun 13, 2007, at 12:26 PM, Steve Lefevre wrote:

Nabil --

Are you sure you need a one-many-mapping? If I'm reading between the lines correctly, it sounds like you may need a many-to-many relationship.

Here's a question. Can one class have many students, and one student have many classes? If so, you need a many-to-many relationship.

It would go something like this:

students
-----------
id
first_name
last_name

classes
-----------
id
class_name

enrollment
-------------
student_id
class_id

On 6/13/07, Nabil <Nabil@kramer-smilko.com> wrote:

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
>


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

                http://www.postgresql.org/about/donate

--
"Computers are useless. They can only give you answers"
-- Pablo Picasso

--
"Computers are useless. They can only give you answers"
-- Pablo Picasso