Tightly coupling tables in postgresql

Question

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.

Answer 1

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
;