Standardized Administration Tools (SAT) Accounts Database

This is Daniel Allen's Sep 2019 understanding of the SAT Accounts Database and project to redevelop SAT Accounts management. See web diagram with architecture overview.

Goals for database redesign

  • revise the existing math_computing database schema, which is currently (Sep 2019) a 1:1 image of the sponsors files output, to allow us greater flexibility in accounts management
  • eliminate manually triggered updates for data that is otherwise recorded in OAT/odyssey, such as course enrollments and watiam

Remarks on math_computing schema

Following are some remarks on the current (Sep 2019) schema and data, regarding pieces we might remove from the database entirely.
  • In current data, some tables appear redundant and stale, and can be dropped entirely.
    • .sponsor_netinfo (dates in 2002-2014), .sponsor_network (incomplete old network names and IP ranges eg pravate-scs,, .sponsor_group (descriptions of arg-fe, arg-windows-ts, other groups)
    • .sponsor_userinfo is redundant with odyssey _watiam tables
  • Many tables have blank fields, such as .sponsor_computing.computing_description, .sponsor_class.class_requirements and .sponsor_class.class_instructor
    • Assumption: these can be left blank- or removed entirely- and do not require data entry
      • testing this assumption will require verifying the Math-regional data with Robyn.
  • In current data, I'm not sure of the purpose of some tables
    • .sponsor_charge has 26 rows with no foreign key
  • There are systems for recording class load and enrollment. Assumption: the resource database is not the right place to put these
    • .sponsor_class.class_enrollment has only 9 rows with non-null - representing se112, se212, se382.
    • .sponsor_class.class_load has 471/959 values of low/moderate/heavy/???

Following are remarks on new design that changes the schema for math_computing.

  • We have a number of databases which record "membership in a group" where "membership" grants privileges (such as resource allocation or access) and "group" denotes such broad things as "employment in CS," "grad-student in the Math faculty," or "undergrad in another faculty taking a CS course"
  • These include the following data sources:
    • "odyssey" driven:
      • math_computing schema;
      • _quest schema
      • _watiam schema
      • CS Active Directory records, which are generated from math_computing schema
      • Standardized Administrative Tools (SAT) schema, currently called cs_people /RT#808697
    • other sources on campus:
      • workday employment records (which we theoretically could get API access to)
      • grouper records - including quest data for students, workday data and other manually-updated records
      • campus Active Directory - Nexus LDAP interface

-- DanielAllen - 2019-09-03

Topic revision: r3 - 2019-09-18 - DanielAllen
This site is powered by the TWiki collaboration platform Powered by PerlCopyright © 2008-2019 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback