Thread: Mapping one to many

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. Is there some kind of check I can do? If so what  would
happen if I delete a group that has members in it? 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. I'm open
to any suggestions. Thanks in advance to every and any one who help.

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

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
>

--- Nabil <Nabil@kramer-smilko.com> wrote:

> Ahh but groups dont know about there members. It it was implemented
> the way that it has been suggested to me (a table for each users,
> groups and mapping user to groups) you would end up with a many to
> many relationship. The way I wanted to do it was just map user to
> groups without mapping groups to users. In other words map one user
> to many groups, the groups know nothing about the users.

What will you do when you have that you have to add more groups and delete other groups?  Are you
going to redefine the dimementions of your array and the assignment of your array indexes as
records are added and deleted to your groups table?

A "many to many" table is really a relationship between two or more entity tables.  The groups
table would know nothing about members and members would know nothing about groups.  They way to
determine the relationship between members and groups is to query across the relationship table.

This is a time test any univerally accepted model for (Relational) database systems.

Regards,
Richard Broersma Jr.

I am not saying that this solution doesn't work or even that is a
"bad" one but what I'm saying is that there is obviously a better
solution. Also there are better forms, other than row/column, in
which to module data but there any Databases that support them. If
you'll notice from my second message I was asking why that is so is
so and to please enlighten me to the magic of RMDBs. I feel as if
there is something about RMDBs that will explain alot but is just
beyond my reach and I what to know what it is.


On Jun 13, 2007, at 2:05 PM, Richard Broersma Jr wrote:

>
> --- Nabil <Nabil@kramer-smilko.com> wrote:
>
>> Ahh but groups dont know about there members. It it was implemented
>> the way that it has been suggested to me (a table for each users,
>> groups and mapping user to groups) you would end up with a many to
>> many relationship. The way I wanted to do it was just map user to
>> groups without mapping groups to users. In other words map one user
>> to many groups, the groups know nothing about the users.
>
> What will you do when you have that you have to add more groups and
> delete other groups?  Are you
> going to redefine the dimementions of your array and the assignment
> of your array indexes as
> records are added and deleted to your groups table?
>
> A "many to many" table is really a relationship between two or more
> entity tables.  The groups
> table would know nothing about members and members would know
> nothing about groups.  They way to
> determine the relationship between members and groups is to query
> across the relationship table.
>
> This is a time test any univerally accepted model for (Relational)
> database systems.
>
> Regards,
> Richard Broersma Jr.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>

--- Nabil <Nabil@kramer-smilko.com> wrote:
> Also there are better forms, other than row/column, in
> which to module data but there any Databases that support them.

I am not sure what you have in mind when you say that there are forms that are better than
row/colum.  Really a table can be thought of as a container that holds specific entities(rows)
each entity is defined by its attributes(colums).  However, I would argue that idea of
"row/column" is closer to a spreadsheet rather than a table.  Tables are distinctly different from
a spreadsheet.

> If
> you'll notice from my second message I was asking why that is so is
> so and to please enlighten me to the magic of RMDBs. I feel as if
> there is something about RMDBs that will explain alot but is just
> beyond my reach and I what to know what it is.

http://en.wikipedia.org/wiki/Entity-relationship_model
http://en.wikipedia.org/wiki/Database_normalization

I hope this helps.

Regards,
Richard Broersma Jr.

>
>I am not saying that this solution doesn't work or even that is a  "bad"
>one but what I'm saying is that there is obviously a better  solution. Also
>there are better forms, other than row/column, in  which to module data but
>there any Databases that support them. If  you'll notice from my second
>message I was asking why that is so is  so and to please enlighten me to
>the magic of RMDBs. I feel as if  there is something about RMDBs that will
>explain alot but is just  beyond my reach and I what to know what it is.
>
>

Sometimes it's really good to know, why things are how the are. The base for
RDBMs is pure mathematic.

This would be a first step into it

http://en.wikipedia.org/wiki/Codd

Besides, there is no problem to store tree structures in an SQL System. As
far as i know there even exists an LDAP implementation in DB2. Here is a
description (hard to read for beginners - but it works and is "simple")

Regards Sepp

_________________________________________________________________
Sensationelle Konzerte auf allen Kontinenten - Musik für unsere Mutter Erde.
http://liveearthsos.msn.com/Hub.aspx?mkt=de-at