Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
2  
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. Why did you come up with that model in the first place? –  a_horse_with_no_name Jun 27 '12 at 6:52
    
@a_horse_with_no_name : I could not find any other way to store the member of the group. What else do you suggest? –  Ashwin Jun 27 '12 at 6:54
    
A normalized model with a n:m relation. –  a_horse_with_no_name Jun 27 '12 at 6:55
    
@a_horse_with_no_name : Can you be more detailed? I would really like to change the model if there is a better one. If you can give an answer in the answer section, it would be really helpful. –  Ashwin Jun 27 '12 at 7:00
add comment

1 Answer

up vote 5 down vote accepted

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
;
share|improve this answer
    
Will that not make the table_members table really big. The number rows would become number of groups*number of users. Will this not be a problem when searching? –  Ashwin Jun 27 '12 at 7:05
1  
@Ashwin: no that won't be a problem. PostgreSQL can easily handle hundreds of million of rows in a single table. Anything smaller is not considered really big these days. And if that really gets too big someday, you can partition that table. How many users and groups are you expecting. –  a_horse_with_no_name Jun 27 '12 at 7:08
1  
The reason you have to ask how to define a FK with that system is one indication that it's not good. Think of queries like: how many members do my groups have? Move a member from group1 to group2. Get me the average number of members in a group. Find groups that that have the same three members. And so on. Anything that involves dealing with group members individually will be very complicated and error prone. Do read up on what normalization means. –  a_horse_with_no_name Jun 27 '12 at 7:48
2  
Well if you insist on keeping a bad model, just do it. But be warned. –  a_horse_with_no_name Jun 27 '12 at 7:55
1  
FWIW, we have tables with hundreds of millions of rows, and that database handles about 150 web hits per second, running about ten queries each, while being the replication target of about 80 databases with 3000 directly connected users doing OLTP. Normalize (en.wikipedia.org/wiki/Database_normalization), then deal with any actual performance issues -- they almost certainly won't be where you expect them. –  kgrittn Jun 29 '12 at 12:18
show 4 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.