Results 1 to 5 of 5
  1. #1
    Join Date
    May 2004
    Posts
    14

    DB design question

    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.

  2. #2
    Join Date
    Oct 2011
    Location
    Hamburg, Germany
    Posts
    18
    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?

  3. #3
    Join Date
    May 2004
    Posts
    14

    can't modify the USER table

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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2004
    Posts
    14

    Advantage

    So it seems that same query
    Code:
    SELECT u.user_name, g.group_name
    FROM group g, users u, user_group ug
    WHERE g.aid='123' AND g.gid=ug.gid AND u.uid=ug.uid
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •