I have three tables
1. USERS (UID, USER_NAME)
2. AGENCY (AID, AGENCY_NAME)
3. GROUP (GID, GROUP_NAME)
and I have a case where each agency have groups and each group can have users. So, agency is super set which consist of group and group itself have users.
Now I am wondering what field to put in the new table(s) so that i can make following different queries.
1. List the users in the group in the agency.
2. List the groups in the agency.

Currently I have single table GROUPING(ID, AGENCY_ID, GROUP_ID, USER_ID). Easy with this is I can do query#1. I can do second query also but is this a good design?

Is there any optimum/efficient design for this situation?

Any help will be appreciated.

i would expand a tables with a foregn keys like this

1.USERS (UID, USER_NAME,GID)
2. AGENCY (AID, AGENCY_NAME)
3. GROUP (GID, GROUP_NAME,AID)

or it is not possible?

Sorry forgot to mention. Can't modify USER table.

if you cannot modify the USER table to carry the group foreign key, then you will have to create a one-to-one table that can

agency (aid, agency_name)

group (gid, group_name, aid )

users ( uid, user_name)

user_group ( uid, gid )

KEY: tablename ( primarykey, foreignkey )

So it seems that same query
works for both schema.

The only difference (or advantage??) I see is having user_group table one less column and adding 'aid' in group.

Let me know if any other. I am eager to learn "BASIC" of Database

Thanks