BSAR Database Tables and Fields

TODO: how do I handle the default set of permissions?

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

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, campus

Referenced by: request_object, request_comments

Fields:

id: PK auto-generated

coordinator_id: FK user.id, not null

requestor_id FK user.id, not null

campus_id: FK campus.id, not null

completed: boolean, not null, TODO: can a request be marked re-opened? If so, a boolean for this field isn’t adequate.

REQUEST_COMMENTS:

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, 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.

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: 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.

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:

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

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

Referenced by: N/A

Fields:

request_object_id: FK request_object.id, not null

action: FK action_type.id, not null

action_user_id: FK user.id, not null

action_date: date, not null

action_comment: varchar(2000)

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

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

banner_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.

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

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

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.