Wallace
Here is my kick at the can (written before server crashed, posted after)...
You have several many-to-many (M:M) relationships which requrie joiner or intermediary tables. I like to call these tables "profiles" for several situations. For example, you are setting up an end user's application profile.
Design
From the perspective of the user, they can belong to...
- one department (one-to-many, 1:M)
- many groups (m:m)
- many application groups (m:m)
tblUser
UserID - primary key (pk)
UserLN - Last name
UserFN - First name
LoginName
etc...
Discussion:
Fairly straight
tblDepartment
DeptCode (or DeptID) - pk
DeptName - name
Discussion:
You have indicated DeptID as the primary key which is fine, but there tend to be few enough departments in an organization that allow the use of text code for the key. One advantage of using the DeptCode is that you can perform an alpha sort from a table that uses the DeptCode as a foreign key instead of having to join the Department table in the SELECT statement.
tblApplication
AppCode or AppID - pk
AppName
tblGroup
GroupCode or GroupID - pk
GroupName
+ other fields related to a Group.
Discussion:
I am not sure what a "group" is - security perhaps?
Okay, now for the 1:M
Revisit the User table.
tblUser
UserID - primary key (pk)
UserLN - Last name
UserFN - First name
LoginName
DeptCode (or DeptID) - foreign key (fk) to Department
etc...
Discussion:
Since this is one department can have many users, the primary key DeptCode or DeptID exists on the Department table, and the foreign key will reside on the user table (in blue).
This is how you should handle just about all your 1:M relationshipts - the foreign key should reside on the "many" side. This also means you need a department before you create a user.
tblGroupProfile
GroupCode (or GroupID) - foreign key (fk) to Group table
UserID - fk to User table
ExpireDate
ProfileDate
Primary key = GroupCode + UserID (or GroupID + UserID)
Discussion:
A typical joiner or intermediary table. I added a profile date to capture when the profile was last updated. This way, you can have a mechanism for reviewing old profiles.
In case this is an security group setup, I included an expiration date to capture any temp security assignments.
tblAppGroup
AppCode or AppID - fk to application table
UserID - fk to user application table
ProfileDate
Primary key = AppCode + UserID (or AppID + UserID)
Discussion:
Again, a typical joiner table. Profile date to capture date of last change.
Hopefully, you can see that there is a minimal amount of redundency in this design. Although I tend to use character codes for primary keys where appropriate, you can use the ID or CODE as per your requirement and needs.
Presentation:
Many to many joiner tables used in a subform can seem a little awkward sometimes. However, once you are used to them, they can be very effective.
Example: tblGroupProfile
GroupCode - Combo box
ControlSource - Groupcode
RecordSource - Select GroupCode, GroupName from tblGroups Order by GroupCode
UserID - Combo box
ControlSource - UserID
RecordSource - Select UserID, UserLN, UserFN from tblUser Order by UserLN, UserFN
ExpireDate
ProfileDate - default value =Date(), update anytime a change is made
Then, if this table is included in the User form as a tabular or contineous subform, hide the UserID, link the User form to the GroupProfile using UserID. this way, you can see all groups a user belongs to.
If the GroupProfile contineous or tabular subform is to be added to the Groups main form, hide the GroupCode field, and link on the Groupcode. For the User combo box, have the combo box display the last name (hide the UserID), and then add an unbound text box that uses Me.YourUserComboBox.Column(2) to display the first name of the user. Now you have a subform that will display all the user belonging to the specific group.
Repeat a similar approach for the application group table.
Richard
