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
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in December 2003