Problem solve Get help with specific problems with your technologies, process and projects.

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
UserGroupFolder Table  lkpGroupFolder Table
  UserGroupFolderID      GroupID
  FolderID               FolderID

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

Dig Deeper on Oracle and SQL

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.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.