Design Help

[wallaceoc80]

Can anybody help me with a small design problem that relates to a portion of a database I am designing?

Let me try and explain the situation as quickly as I can:

There can be many DEPARTMENTS (Dept Name and ID) which can have many USERS (User ID, Preferences, Group ID, AppGroup ID). A user can only be a memeber of 1 department.

A user can be a memeber of many GROUPS (Group ID and Name). A group can have many different users.

A user can also be a memeber of many APPGROUPS (AppGroupID, AppGroup Name, Application ID, User ID) and an AppGroup can have many users.

An AppGroup contains many APPLICATIONS (Application ID, App Name).


What is the best way to model this situation. The items I have written in upper case are the objects I currently have in my design. However, I feel there is a lot of duplication in this approach.

Any suggestions?

Regards,

Wallace

 

[johnherman]

Seems to me you have most of it right. Consider this.

APPGROUPS (AppGroupID, AppGroupName)

APPLICATIONS (ApplicationID, AppName, AppGroupID).

Assumes USER in APPGROUP can use any APPLICATION in that APPGROUP.
Assumes APPLICATION can only belong to one APPGROUP.

If the assumptions are wrong, please let us know.


-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[wallaceoc80]

Thanks johnherman,

but forgot to say that APPLICATION can belong to many APPGROUPs.

Any more suggestions in light of this?

Thanks

Wallace

 

[johnherman]

APPGROUPS (AppGroupID, AppGroupName)

APPLICATIONS (ApplicationID, AppName).

APPGROUP_APPLICATION (AppAppID, AppGroupID, ApplicationID)

The APPGROUP_APPLICATION table is called a bridge or relation table (other names are possible). It is used to resolve the many to many relationship between APPGROUPS and APPLICATIONS. The AppAppID column is optional. What is really necessary is the intersection of the two keyfields which link the tables.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[wallaceoc80]

Thanks again,

Am I right in thinking that i don't need the UserID or the Application ID columns in the APPGROUP table?


Thanks,

Wallace

 

[johnherman]

Yes. Because you can join to them.

Looking back, I don't see how DEPARTMENTS fits in. I guess there are USERS assigned to DEPARTMENTS.

Or are GROUPS ASSIGNED TO DEPARTMENTS?

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[wallaceoc80]

The Departments comes in as follows:

USERS are belonging to one and only one department. Also APPLICATIONS belong to one and only one department. My next task is to find a way of demonstrating this in the Database.

I must find a way so that users will only be able to see the applications belonging to the department they are in. AppGroups are customisable by the users of the system so I'm thinking the best way to do this is to have a link from the APPLICATIONS table back to the DEPARTMENTS table?

Your help on this is much appreciated!!

Also referring back to the previous problem. Do I need to have in the USER table to the APPGROUP table or does the bridge table do the linking? (At the moment I have AppGroupID column in the USER table)

Thanks,

Wallace

 

[johnherman]

You will need a bridge/relation table between USERS and APPGROUPS since it is a "many to many" relationship.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[lespaul]

Wallace,

have you read 'The Fundamentals of Relational Database Design'?

Leslie

 

[willir]

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

 

[SQLSister]

Nice article Leslie, you might consider putting the link into an FAQ for easy future reference.

Questions about posting. See faq183-874

 

[willir]

Leslie

Do you want to do the FAQ as suggested by SQLSister? Might be a good idea. Not sure where to post it - here or in the Access area. "We" (menaing yourself and others) could also add some other important links too.

Richard