Q

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?


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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close