Ask the Expert

Users, groups, and folders

This is an ongoing debate in my company and we are desperate to know who is right! Here is the problem....

We want to link users to a group then link the group to a folder. Here are the two proposed solutions.

Solution 1             Solution 2
----------             ----------
 

User Table             User Table
  UserID                 UserID
 
Groups Table           Groups Table
  GroupID                GroupID
 
Folder Table           Folder Table
  FolderID               FolderID
 
UserGroup Table        lkpUserGroup Table
  UserGroupID            UserID
  GroupID                GroupID
  UserID          
 
UserGroupFolder Table  lkpGroupFolder Table
  UserGroupFolderID      GroupID
  FolderID               FolderID
  UserGroupID

As you can see, in Solution 1 the Users and Groups tables are linked by the UserGroup table, and the UserGroup and the Folder table are linked by the UserGroupFolder table. This will allow many folders to be linked to many usergroups. And because all tables are linked by ID fields this will make expansion and foreign keys easy.

In Solution 2, the lkpUserGroup table is a lookup table linking UserID's and GroupID's. The lkpGroupFolder is a lookup table linking GroupID's and FolderID's. So you would need to get the users groupID from lkpUserGroup then search for the GroupID and FolderID in the lkpGroupFolder.

To me the two solutions will accomplish the same thing, but which one is correct or better designed?


    Requires Free Membership to View

    Login

That's easy. Solution 2 is better. Solution 1 is massively flawed.

In Solution 1, a single folder in the UserGroupFolder seems to be related to multiple rows in the UserGroup table. That's because the only way to relate multiple users to a single group in the UserGroup table is with multiple rows. If there are multiple rows with the same GroupID and different UserIDs, do they all have the same UserGroupID? If not, then you have to relate each single folder to multiple different UserGroupIDs. On the other hand, if multiple rows with the same GroupID and different UserIDs in the UserGroup table have the same UserGroupID, then UserGroupID repeats. Now, in order for a foreign key to be declared in UserGroupFolder, it must reference the primary key of UserGroup, which would have to be composite and include both GroupID and UserID. Thus UserGroupID in the UserGroup table is useless and therefore the foreign key in the UserGroupFolder is wrong and the UserGroupFolder table doesn't work.

For More Information


This was first published in December 2003

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top