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.
No comments:
Post a Comment