• Created by Unknown User (smithal), last modified on Sept 11, 2013

 

This document contains the current tables and fields in the rpms database, and lists the proposed tables and fields for rpms 2.0.

 

Current tables in rpms database


1. users
1. [id auto-increment]
2. fname
3. lname
4. email
5. phone
6. organization
7. method_of_contact


2. admin_infrastructure_status
1. [id auto-increment]
2. projectid
3. infrastructure_status
4. infrastructure_modified_by
5. infrastructure_modified_date
6. infrastructure_comments


3. admin_project_status
1. [id auto-increment]
2. projectid
3. project_status
4. project_modified_by
5. project_modified_date
6. project_comments


4. connectivity
1. [id auto-increment]
2. projectid
3. outside_access
4. custom_network_config
5. outside_agency
6. inside_agency
7. additional_info


5. project
1. [id auto-increment]
2. userid
3. title
4. date_of_request
5. start_date
6. end_date
7. num_personnel
8. project_summary
9. summary_status
10. project_type
11. project_use


6. project_output
1. [id auto-increment]
2. projectid
3. outcome_summary
4. publications


7. project_resources
1. [id auto-increment]
2. projectid
3. consultation
4. developer_resources
5. lab
6. lab_start_date
7. length
8. workstations


8. project_summary
1. [id auto-increment]
2. projectid
3. motivation
4. vision
5. stakeholder1
6. role1
7. stakeholder2
8. role2
9. stakeholder3
10. role3
11. stakeholder4
12. role4
13. stakeholder5
14. role5
15. stakeholder6
16. role6
17. datasources
18. steps
19. metrics
20. project_additional_info


9. tr_desktop
1. [id auto-increment]
2. projectid
3. dt_qty
4. dt_operating_system
5. dt_memory
6. dt_disk
7. dt_vm
8. dt_software
9. dt_notes


10. tr_server
1. [id auto-increment]
2. projectid
3. server_qty
4. server_operating_system
5. server_memory
6. server_disk
7. server_vm
8. server_software
9. server_notes

 





New tables in rpms 2.0 


1. project_main

  1. id [primary auto-increment: int 5] 
  2. project_created [Unix timestamp]
  3. group_name [char 200]
  4. project_title [char 200] 
  5. start_date [Unix timestamp]
  6. end_date [Unix timestamp]
  7. approved_start [Unix timestamp]
  8. approved_end [Unix timestamp]
  9. project_type [char 50]
  10. project_use [char 50]
  11. summary [char 1000] 
  12. goals [char 1000]
  13. success [char 1000] 
  14. outside_resources [boolean]
  15. outside_notes [char 1000]
  16. test_data [boolean]
  17. test_data_notes [char 1000]
  18. third_party [boolean]
  19. third_party_notes [char 1000]
  20. additional_information [char 1000]
  21. project_outcome [char 1000]
  22. admin_change_date [Unix timestamp]
  23. admin_selection [char 50]
  24. admin_name [char 50]
  25. admin_notes [char 200]
  26. infra_change_date [Unix timestamp]
  27. infrastructure_selection [char 50]
  28. infrastructure_name [char 50]
  29. infrastructure_notes [char 200]
  30. total_VM [char 2]
  31. total_physical [char 2]
  32. total_online [char 2]
  33. total_other [char 2]

 

2. personnel 

  1. id (primary auto-increment record id: int 5)
  2. projectid (foreign key int 5)
  3. first_name [char 100]
  4. last_name [char 100]
  5. title (char 100)
  6. organization [char 200]
  7. phone [char 50]
  8. email [char 100]
  9. primary_contact [boolean]
  10. need_login [boolean]
  11. notify_user [boolean]
  12. user_name [char 50]

 

3. stakeholders

  1. id (primary auto-increment record id: int 5)
  2. projectid (foreign key int 5)
  3. name [char 100]
  4. role (char 100)

 

4. resources

  1. id (primary auto-increment record id: int 5)
  2. projectid (foreign key int 5)
  3. request_date [Unix timestamp]
  4. resource_type (char 20) 
  5. device_config [char 100]
  6. notes [char 1000]
  7. external_access [boolean]
  8. subdomain [char 100]
  9. server_port [char 50]
  10. external_port [char 50]
  11. device_name [char 100]
  12. inventory [char 100]
  13. internal_notes [char 1000]
  14. proxy_from (char 50)
  15. proxy_to (char 50)
  16. start_name [char 50]
  17. end_name [char 50]
  18. start_date  [Unix timestamp]
  19. end_date  [Unix timestamp]

 

5. project_date_history

  1. id (primary auto-increment record id: int 5)
  2. projectid (foreign key int 5)
  3. approved_change [Unix timestamp]
  4. approved_start [Unix timestamp]
  5. approved_end [Unix timestamp]


6. status_history

  1. id (primary auto-increment record id: int 5)
  2. projectid (foreign key int 5)
  3. type_of_status (char 20)
  4. status_changed_date [Unix timestamp]
  5. status_selection [char 50]
  6. status_name [char 50]
  7. status_notes [char 200]

 

7. resources_history

  1. id (primary auto-increment record id: int 5)
  2. changed_date [Unix timestamp]
  3. projectid (foreign key int 5)
  4. request_date [Unix timestamp]
  5. resource_type (char 20) 
  6. device_config [char 100]
  7. notes [char 1000]
  8. external_access [boolean]
  9. subdomain [char 100]
  10. server_port [char 50]
  11. external_port [char 50]
  12. device_name [char 100]
  13. inventory [char 100]
  14. internal_notes [char 1000]
  15. proxy_from (char 50)
  16. proxy_to (char 50)
  17. start_name [char 50]
  18. end_name [char 50]
  19. start_date  [Unix timestamp]
  20. end_date  [Unix timestamp]

 

 

 

 

 

 

Descriptions for tables and fields in rpms 2.0


1. engagement details not in other tables

  1. record key  (foreign key for all others)
  2. date the project application is submitted
  3. internally assigned group name
  4. users project title
  5. users requested project start date
  6. users requested project end date
  7. internal project start date
  8. internal project end date
  9. internal (Prototype, Evaluation, Hybrid)
  10. internal (Internal, External, Collaborative)
  11. users project summary
  12. users project goals
  13. users success criteria
  14. User resources connect to outside the lab?
  15. notes for previous
  16. users primary sources of test data
  17. notes for previous
  18. user needs access to third-party web/apps
  19. notes for previous
  20. users additional information
  21. internal project outcome/summary
  22. date this administration status changed to
  23. internal (On Hold, Submitted, Rejected, etc...)
  24. status changer's name
  25. status changer's notes
  26. date this infrastructure status changed to
  27. internal (Ready, To Set Up, On Hold, etc...)
  28. status changer's name
  29. status changer's notes
  30. total VMs for summary page
  31. total physical equipment (non-VM)
  32. total online resources
  33. total other resources

 

2. user's team members who may need access

  1. record key
  2. foreign key from project table
  3. team member's first name
  4. team member's last name
  5. team member's title
  6. team member's organization name
  7. team member's phone number
  8. team member's email address
  9. is this the primary Team member
  10. does this team member need a login
  11. communication selector, internal use only
  12. internal user name

 

3. (users/actors) in proposed engagement

  1. record key
  2. foreign key from project table
  3. person's full name
  4. person's role in this project

 

4. technology requirements

  1. record key
  2. foreign key from project table
  3. creation of this request
  4. (virtual, physical, online, other)
  5. configuration or OS/software
  6. user's resource notes
  7. VM to be externally accessible
  8. user's requested subdomain
  9. (e.g. 8080)
  10. (e.g, 80)
  11. VM name or physical device model/name
  12. physical device serial number
  13. notes for internal use only
  14. from server name
  15. to server name
  16. device handed to or checked out by name
  17. device received by name (internal receiver)
  18. VM/device use starting date (internal)
  19. VM/device use ending date (internal)

 

5. change history of project approved dates

  1. record key
  2. foreign key from project table
  3. project approved starting date (internal)
  4. project approved ending date (internal)
  5. date changed from this start/end date

 

6. history of all status changes

  1. record key
  2. foreign key from project table
  3. Which status changed? (admin, tech)
  4. date changed to this status 
  5. see project table, #23/27
  6. see project table, #24/28
  7. see project table, #25/29

 

7.  proposed db trigger: before update, copy here

  1. new/this record key
  2. date changed resource from these fields
  3. see resources table for  rest of this

 

 

 

END