Data Model ERD Notes

  1. Validity vs start/end: A hire date might not be the same as when email should be granted for a person. Perhaps SOR records should only have a validity period that matches start/end dates, while things like early email or grace period email/library access are covered via BRP.
  2. Identifiers (id) all start >= 1. 0 is reserved to indicate "none". change_id is issued sequentially, so most recent change always has highest change_id.
  3. Example ct_registry_permission_types (Various types would come with common values pre-populated.):
    1. Admin/all access
    2. Support/read all
    3. User/self update
  4. pr_persons is populated by initial SOR data for a person, and updated subsequently according to br_attribute_rules and br_attribute_overrides, if BR component is in use.
  5. pr_sor_records:sor_record_id must be unique across all sor_t, since it is used as a key into pr_sor_%_records.
  6. pr_roles might be calculated on input processing of SOR data.
  7. The registry isn't responsible for generating email addresses per se, though a Rule could do so based on identifier assignment.
  8. Example pr_address_types:
    1. Home
    2. Office
    3. Mailing
    4. Parent
    5. Campus
  9. Example pr_phone_types:
    1. Landline
    2. Fax
    3. Pager
    4. Mobile/ATT
    5. Mobile/Sprint
    6. Mobile/TMobile
    7. Mobile/Verizon
    8. Mobile
  10. The term "Identifier" is ambiguous. In most cases, it refers to the unique key for a table. However, in the pr_identifiers table, it also refers to an individual's identifiers, eg NetID. Perhaps the former usage should become "Key".
  11. The "algorithm" reference in pr_identifier_types could be a plug-in, which in turn might add its own database tables. eg:
    pr_plugin_identifierassignment_nextavailable
    --------------------------------------------
    identifier_t int
    next         int 
  12. Example pr_identifier_types:
    identifer_t name   format        min max algorithm  private changeable
    ----------- ------ ------------- --- --- ---------  ------- ----------
    1           NetID  $$$?$?##?#?#? 3   8   selfselect n       y
    2           RCPID  #*            1   8   nextavail  y       n
    3           OpenID http://.*     15  80  external   n       y 
  13. SOR IDs are mapped via the identifier tables, as for all other types of identifiers.
  14. How to manage history/archiving? Should entries in tables never go away (just add a 'deleted' flag to each row), or should they be copied to archive_pr_tablename?
  15. (pr_sor_records:sor_record_id,pr_roles:role_id) may not be unique. For example, a student could leave, then return. Or, an employee could resign and then come back.
  16. There are implied "known" types of people: Employee, Student, Alumni. Perhaps make this explicit, with more via plugins.
  17. Example pr_department_types:
    1. School
    2. Program
    3. Division
    4. Department
  18. br_preferred_attribute_rules describes, generically, which SORs and Roles take precedence when a single attribute must be selected, or an ordering must be constructed (eg: display hierarchy). br_preferred_attribute_overrides defines exceptions to the rules. br_derived_attribute_rules describes, generically, how to create new attributes based on existing ones. br_derived_attribute_overrides defines exceptions to the rules.
  19. The contact tables (eg: prs_emails, prs_addresses) don't have a column for sorid because they can be referenced by non-SORID based tables, eg prs_department_contact_records.
  20. "Local" data could be stored in autogenerated tables. For example, prs_sordata_hr might hold (sor_id, sor_t, salary_grade, health_plan, ...).
  21. Group owners are listed in ctx_registry_permission and permission_types with target of "gro_groups:group_id" (managing group data is just another registry permission)
  22. Tables with generic type columns have link to likely tables in ER lists but connectors are dashed
  23. Emergency contact info is calculated based on defined rules, eg
    1. Where SOR asserts sponsored relationship and emergency contact flag = true
    2. Where phone type = mobile