Postgresql – Tightly coupling tables in postgresql

postgresql

Our web-application is something like – users can create groups(request for membership of a group also), ask questions, answer etc. It is still in the development stage. What I want to do is tightly couple some of the tables.
For example – Whenever a new member joins a group(i.e when that table is being updated) it should be checked whether he/she is a valid user or Whenever someone asks a question in a group, before inserting the question in the table, it should be checked whether that user is a a member of that group etc.
I cannot do these checks using foreign keys because, the group mmbers are store in this way

group_id    members

mygroup     member1,member2,member3
group001    member2,member10,member2,..<br>

As you can see they are stored by seperating commas. So I cannot put the foreign key constraint.

I do not want to do these checks in each and every servlet. I would like to do them in the database itself on insertion. Please tell me how to proceed with this?
Thank you.

Best Answer

The only sensible solution is to normalize your model and get rid of those dreaded comma separated values. Then you can use a proper foreign key.

Something like:

create table users
(
    user_id   serial not null primary key,
    user_name text not null
);

create table groups
(
    group_id   serial not null primary key,
    group_name text not null
);

create table members  
(
    user_id  integer not null,
    group_id integer not null,
    primary key (user_id, group_id),
    foreign key (user_id) references users (user_id),
    foreign key (group_id) references groups (group_id)
);

If for some reason you would like to have the comma separated lists (for display convenience) you can always create a view that returns this:

create view v_members
as
select g.group_id, 
       g.group_name, 
       string_agg(u.user_name) as group_members
from members m
  join users u on u.user_id = m.user_id
  join groups g on g.group_id = m.group_id
group by g.group_id, g.group_name
;
Related Question