Questions

After the meeting on Monday, I've been re-writing the specs, and I have a few questions I need answered. Anyone who has an answer to any of these is requested to leave their answers in the comments section of this post. Be sure to include the number of the question to which you are responding. The questions are broken up as they pertain to user roles.

Note, to see an enlarged version of any image, right-click on it and select the option to open it in a new tab or window.

Approvers/Functional Leads

1.) What circumstances would cause you, acting as an Approver/Functional Lead, to edit a request?

2.) Given question 1, what sort of edits do you make?

3.) Given question 1, what is the current process for making such edits? For example, do you email the Coordinator to tell them you made the change? Do you just forward it on to the Banner Id Management people?



4.) What information do you need to see in the summary table of requests pending your approval?

5.) What information do you need to see in the summary table of past handled requests?



6.) What information do you need to see in a detailed listing of a request? Currently, I'm planning on showing the User's information, the Coordinator's information, a listing of all past requests, a listing of all current permissions, the status of other Functional Leads' actions on this request, a history of all actions of this request, and the form through which you Approve/Deny the individual parts of this request. Too much information? Not enough? what else?

Coordinators

7.) A request was made for giving the ability to copy another user's permissions. Does this ever happen in the current process? For example, do you ever dig up an old email to copy the permission requested? Or do you ever tell the BIM staff "Make the permissions like so-and-so?" How often would this feature likely be used?



8.) What would be the most useful information to include in a summary table of pending requests?

9.) What information do you need to see regarding the details of a request? Right now I have: basic user information (name, contact info), date the request was made, approval status, ferpa status, comments, and the list of forms/classes requested. Anything else?

Banner Id Management (BIM)

10.) BIM staff get termination reports which cause them to immediately cancel an account. When this occurs, are there any other actions taken? Such as notifying a Coordinator, or sending email to the affected User?

11.) Given the previous question, are there any other "back-door" actions that the BIM staff make?



12.) What information do you want to see in the summary table of pending requests?

13.) What information do you want to see in the summary table of past requests?

14.) Would you ever need to edit a completed request? I don't mean editing an account, I mean a specific request. Under what circumstances would you edit it?

Meeting notes

Met with the entire Banner team today to give an overview of the system. Many changes need to be made.

1.) Add a means to copy another person's access permissions to a new user. Will save entering all the classes/forms when you know of a user who you can just copy.
2.) Add a means to set the campus for the request.
3.) What happens when there are multiple campuses, each requiring different access? Separate requests?
4.) Need a means to consolidate all approver/bacu reminders into a single daily email. Must provide means to indicate a request is urgent and should be handled individually rather than batched.
5.) Add means for BACU staff to enter back door modifications. They get termination reports and immediately go in and close off those person's access. Need means for them to indicate that in the system.
6.) Approvers need a mechanism to modify a request. Apparently they do this regularly.
7.) Need to re-think how the process works regarding an Approver changing their approval/denial.

Presentation

Here are the slides for the presentation to the Banner staff on Monday, 1/14/08 at 2 pm.

BSAR Database Tables and Fields V.2

USER:

General user information, name, uh_username, etc.

Initial size: TODO

Anticipated growth: TODO

References: N/A

Referenced by: bsar_user_role, approver_domain, request, request_comments, requestor, request_object_action, banner_permission

Fields:

id: PK auto-generated

first_name: varchar(50), not null

last_name: varchar(50), not null

uh_username: varchar(8), not null

BSAR_USER_TYPE:

Tells what type of users exist in the bsar system, excluding requestors. Is essentially a three record table with the values of the description fields being Coordinator, Approver, and BACU.

Initial size: 3

Anticipated growth: none

References: N/A

Referenced by: bsar_user_role, request_comments

Fields:

id: PK auto-generated

description: varchar(11), not-null

BSAR_USER_ROLE:

Associates users with roles in the BSAR system, this tells if the user is a Coordinator, Approver, and/or BACU. Handles the case where a user has multiple roles. Requestors are not in this table.

Initial size: TODO

Anticipated growth: TODO

References: user, bsar_user_type

Referenced by: N/A

Fields:

user_id FK user.id, not null

bsar_user_type_id FK bsar_user_type.id, not null

CAMPUS:

Place to store campus short and long names so they aren’t replicated all over the db.

Initial size: 10

Anticipated growth: none

References: N/A

Referenced by: request_campus

Fields:

id: PK auto-generated

code: char(3), not null, the three letter code for a campus, like WCC, MAN, etc

description: char(20), not null, the full name of the campus

REQUEST:

The general information of a request: who wants access, who filed the request, if the request is marked completed, etc.

Initial size: TODO

Anticipated growth: TODO

References: user

Referenced by: request_object, request_comments, request_campus

Fields:

id: PK auto-generated

coordinator_id: FK user.id, not null

requestor_id FK user.id, not null

completed: boolean, not null

Index on coordinator_id

Index on requestor_id

REQUEST_CAMPUS:

Groups together a request with all campuses to which it applies.

Initial size: 0

Anticipated growth: TODO

References: request, campus

Referenced by: N/A

Fields:

request_id: FK request.id, not null

campus_id: FK campus.id, not null

constraint PK on request_id, campus_id

index on request_id

REQUEST_COMMENT:

May be overkill, but this allows multiple user types to make comments. Since approvers may want to make a comment about the entire part of the request they have control over, the comment section in the request_object_action table isn’t enough.

Initial size: 0

Anticipated growth: TODO

References: request, user, bsar_user_type

Referenced by: N/A

Fields:

id: PK auto-generated

request_id: FK request.id, not null

date: date, not null

comment: varchar(2000), not null

commenter_id: FK user.id, not null

commenter_role: FK bsar_user_type.id, not null, needed because if a user has 2+ roles, which role did the make the comment as, it makes a difference in terms of how it’s displayed.

index on request_id.

REQUESTOR:

Tells if a user has filled out a ferpa notice or not.

Initial size: TODO

Anticipated growth: TODO

References: user

Referenced by: N/A

Fields:

user_id: PK FK user.id, not null

ferpa_sent_date: date, holds last sent date if repeated, null if not sent

ferpa_action_date: date, null if user hasn’t responded

ferpa_approved: boolean, null until user responds

ferpa_resend_count: int, null until sent. number of resends, one week apart

EMAIL:

Holds the different emails that are sent out (ferpa, approver notice, bacu notice, etc) along with resend info (like resend ferpa once a week, for three weeks if not answered). This table will likely disappear in favor of files for the emails, and a properties file for the limit and delay settings, still, I figured I’d put it here so it doesn’t get forgotten.

Initial size: 1

Anticipated growth: none

References: N/A

Referenced by: N/A

Fields:

ferpa_resend_limit: int, not null, number of times to re-nag user

ferpa_resend_delay: int, not null, number of days between resend attempts

ferpa_notice: varchar(?), not null, Text of the first and interim emails to send

ferpa_notice_final: varchar(?), not null, Text of the final email to send

coordinator_ferpa_notice: varchar(?), not null, Email text sent to coordinator when no ferpa obtained.

approver_notice: varchar(?), not null, text of email sent to approvers when action is required.

bacu_notice: varchar(?), not null, Text of email sent to BACU staff when action is required.

BANNER_FORM_ACCESS_TYPE:

Tells the possible access types for a banner form, basically a two-entry table: query and modify.

Initial size: 2

Anticipated growth: 0

References: N/A

Referenced by: request_object

Fields:

id PK auto-generated

code: char(1), not null, shorthand for description, values will be m or q.

description: varchar(6), not null, values will be query or modify.

BANNER_OBJECT_TYPE:

The types of banner_objects, basically a two-entry table: classes and forms

Initial size: 2

Anticipated growth: 0

References: N/A

Referenced by: banner_object

Fields:

id: PK auto-generated

description: varchar(5), not null (values will be class or form)

BANNER_OBJECT:

A listing of all banner classes and forms.

Initial size: TODO

Anticipated growth: TODO

References: banner_object_type

Referenced by: banner_permission, request_object

Fields:

id: PK auto-generated

description: varchar(20?), not null (name of class or form)

type_id FK banner_object_type.id, not null

REQUEST_OBJECT:

Shows what objects were asked for w/ a given request. Groups together all the classes/forms that were asked for in a given request.

Initial size: 0

Anticipated growth: TODO

References: request, banner_object

Referenced by: request_object_action

Fields:

id: PK auto-generated

request_id FK request.id, not null

banner_object_id: FK banner_object.id, not null

access_type: FK banner_form_access_type, only filled in if banner_object is type form, null if is type class

index on request_id

index on banner_object_id

ACTION_TYPE:

The different actions that can happen to a request, or objects in a request, like cancel, approve, deny, create, delete, change to modify/query status, etc.

Initial size: < 10

Anticipated growth: 0

References: N/A

Referenced by: request_object_action

Fields:

id: PK auto-generated

description:varchar(10?), not null

REQUEST_OBJECT_ACTION:

A history of events for each object in a request. Like if permission for an object was initially denied, then approved, then modified, and finally revoked. It’s all here along with who and when for accountability purposes.

Initial size: 0

Anticipated growth: TODO

References: request_object, action_type, user, bsar_user_type

Referenced by: N/A

Fields:

id: PK auto-generated

request_object_id: FK request_object.id, not null

action_type_id: FK action_type.id, not null

action_user_id: FK user.id, not null

action_user_type: FK bsar_user_type.id, not null

action_date: date, not null

action_comment: varchar(2000)

index on request_object_id

BANNER_PERMISSION:

Tells which banner objects to which a user already has access.

Initial size: TODO

Anticipated growth: TODO

References: user, banner_object

Referenced By: N/A

Fields:

user_id FK user.id, not null

object_id FK banner_object.id, not null

form_access_type_id FK banner_form_access_type.id, can be null if object is type class, gets set if object is type form.

constraint PK: user_id, object_id

index on user_id

APPROVER_DOMAIN:

For users with approver roles only, tells which classes and forms they have approval power over.

Initial size: TODO

Anticipated growth: TODO

References: user, banner_object

Referenced by: N/A

Fields:

user_id FK user.id, not null

banner_object_id FK banner_object.id, not null

constraint PK user_id, banner_object_id

index on user_id

BANNER_FORM_ACCESS_TYPE:

Tells the possible access types for a banner form, basically a two-entry table: query and modify.

Initial size: 2

Anticipated growth: 0

References: N/A

Referenced by: request_object

Fields:

id PK auto-generated

code: char(1), not null, shorthand for description, values will be m or q.

description: varchar(6), not null, values will be query or modify.

BSAR Database Diagram V.2

To see the diagram in full-size, right-click on the image and select the option to open in a new window.

The change from V.1 is a fix because a request can apply to multiple campuses.  To fix this, I added the request_campus table which aggregates the request with all pertinent campuses, and removed the campus field from the request table.