Re: Mapping one to many
От | Nabil |
---|---|
Тема | Re: Mapping one to many |
Дата | |
Msg-id | 09429E17-2362-4346-A88D-67636D3C65EF@kramer-smilko.com обсуждение исходный текст |
Ответ на | Re: Mapping one to many (Michael Glaesemann <grzm@seespotcode.net>) |
Список | 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 >
В списке pgsql-novice по дате отправления: