Date: Fri, 29 Mar 2024 03:53:19 -0500 (CDT) Message-ID: <1545578773.917.1711702399324@localhost> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_916_1151544902.1711702399323" ------=_Part_916_1151544902.1711702399323 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html
The information here is mostly metaphorical examples of real life to hel= p explain complex database concepts in regards to Fluxx data model to serve= a more general audience.
Imagine the database being made out of different spreadsheets which we c= all Tables. Every row on that table is representing one record of a type re= presented by that table. For example, if we are adding a Testing User (Firs= t Name: Testing, Last Name: User) as a contact to our database, an example = of what that looks like can be seen below.
Table would be People (Users). It would look like this:
Person ID | Last Name | First Name | Prefix | Salutation | Suffix | User Title | Work Phone | Mobile Phone | Primary Organization | Primary Title | Personal Street Address | Personal Street Address2 | Personal City | State | Country | Postal Code | Date Created | Date Updated | Highest Degree Year | UW Department | Primary Affiliation | Highest Degree | Prefix Dynamic | Track | Co-Investigator / Collaborator / Academi= c Partner Affiliation | Department | Role Name | Citizenship Status | SMPH Division | Primary Affiliation Other | office_telephone | office_fax | office_extension | employee_id | department_division | degree | academic_partner_affiliation_other | Job Title | Individual Vendor Number | Middle Name | Bulk Update Test Field | Alias (or AKA) | Biographical Statement | |
3055 | User | Testing | exampleemail@wisc.edu | (608) 123-9874 | One Test Organization | Chief Executive Officer | 9/20/2021 | 2/11/2022 |
Person ID is a unique number that has no particular meaning other than u= niquely identifying the record in that table/spreadsheet in our database.= p>
In the Fluxx database, the column names are not the same as the labels w= e see in our views; column names are technically the "back-end names" of these fields (e.g. office_telephone and not= "Office Telephone Number"). Everywhere else, we will see the human readabl= e labels.
Let's start this section with a story:
When we invite a guest to our house, we prepare for them. We make sure t= hat we make space for them to come and we expect them to come. The other wa= y around is not the case; they do not expect us to go to their house. When = they show up to our house, we know what their name is and other information= related to them. So, if anyone asks us who is your guest, we are able to t= ell them the name of our guest and their relationship to us and also their = food allergies, because we were expecting them and we were cooking for them= .
Imagine at the same time some birds flying and sitting on our roof. We d= on't expect them and we don't even know they exist. So, if anyone asks us a= bout those birds, we would probably say "what birds?!". The birds however, = know about our house. They were expecting to sit on a roof and that's what = they did. There is only one way relationship between our house and the bird= s. Our house is going to be our house with or without the birds. But the bi= rds need a roof to sit on.
The same idea is happening in our database. As we mentioned in Tables se= ction, we can imagine tables as spreadsheets. So, like spreadsheets, a Requ= est spreadsheet would not know anything about Organization spreadsheet= s unless we connect them together somehow. In databases, we can = Link the spreadsheets that hav= e relationship together. For example, a Request would like to know which Or= ganization it belongs to. So, there is an empty seat in Requests spreadshee= ts that refers to the related Organization:
Organizations spreadsheet:
Contig ID (AKA Org ID) | Name | Acronym | Legal Name | Street Address | Street Address 2 | City | State | Country | Portal Code | Phone | Website | Date Created | Date Updated | Tax Class | Tax ID | tax_registration_date | Organization Total Budget | number_of_paid_staff | number_of_full_time_equivalent | Organization Type | DBA Test | organization_category_other | |
1665 | One= Test Organization | 1 Test Road | Apt 123 | Madison | Wisconsin | United States | 53711 | (608) 123-4567 | testing@wisc.edu | 9/20/2021 | 2/4/2022 | XX-XXXXXXX |
Requests spreadsheet:
Request ID | Grantee Organization | Project Title | Request Type | Status | Grant Start Date | Grant End Date | Amount Requested | Amount Funded | Base Request ID | Document Types | Fiscal Year | Grantee Org. Street Address | Grantee org Street Address2 | Grantee Org City | Contig ID | Grantee Org State | Grantee Org Country | Grantee Org Postal Code | Grantee Org URL | Grantee Org Tax ID | Fiscal Organization | Fiscal Street Address | Fiscal Street Address2 | Fiscal City | Fiscal State | Fiscal Country | Fiscal Postal Code | Fiscal URL | Fiscal Tax ID | Lead PO/PD | Secondary Lead | Program | Sub Program | Initiatives | Sub Initiatives | Date Request Received | Duration | Funding Source | Final Proposal Date | Final Budget Date | Date Created | Date Last Updated | Primary Contact First Name | Primary Contact Last Name | Primary Contact Email | Program Lead First Name | Program Lead Last Name | Program Lead Contact Email | Signatory First Name | Signatory Last Name | Signatory Email | Fip Title | Request Summary | Direct Board Authority Required | This request is a renewal | Digital Checkbox | PERC - Application Resubmission Y/N | nce_flag | attestation_flag | application_resubmission_form_flag | Extension Date | Anticipated Start | progress_report_start_date | progress_report_end_date | orig_proj_end_date | nce_start_date | nce_end_date | form_release_date | meeting_date | complete_date | declination_date | Overall Project Amount | progress_report_total_expenditures_amount | progress_report_total_award_amount | perc_grant_amount | oac_grant_amount | nce_amount | estimated_other_sources_amount | Original Grant Amount | fiscal org table | Number of Years | Duration2 | PERC - Use of Human Subjects Protocol | PERC - Use of Animal Subjects Protocol | PERC - Use of Bio Material Protocol | PERC - Use of Radioactive Material Protocol | wisdm_proj_num_secondary_text | trade_secret_and_poprietary_information_other | response_to_reviewer_comments | research_and_methodology | request_area_served_neighborhood | geographocal_area_served_explorer | general_additional_information | current_funding_detail | city_town_or_village | calendar_year_awarded | additional_informtation_detail | Type of Support | Fiscal Organization Exists | Focus of Grant | Tactics and Methods | Confidentiality | OAC Primary Social Determinant | OAC IRB Review | PERC - Primary Topic | PERC - Earliest Timeframe for Impact | PERC - Research/Education Classification | PERC - Pop Impact - Racial | PERC - Pop Impact - Underserved | PERC - Pop Impacts - Gender | PERC - Pop Impacts - Age Groups | PERC - Geographic Impact | PERC - County Impact | PERC - Use of Human Subjects | PERC - Use of Animal Subjects | PERC - Use of Biological Material | PERC - Use of Radioactive Material | Giving Program, Type, Status | Urban/Rural | Length of Project | Declination Reason | Use of Radioactive Materials | Use of Biological Materials | Use of Animal Submects | Use of Human Subjects | Program Area | PERC Primary Categories | Research categories | Internal Program | Request Area Served County | Geographical Area Served | Age Group | Gender | Ethnicity | Population Served | OAC Primary Categories | Fund:Subfund | Fund | Gifts Disposition | Gifts Request Status | Gifts Request Type | Digital Signature | PERC - Primary Topic Other | PERC - Use of Human Subjects Protocol Explanatio= n | PERC - Use of Animal Subjects Protocol Explanati= on | PERC - Use of Biological Material Protocol Expla= nation | PERC - Use of Radioactive Material Protocol Expl= anation | wisdm_project_number | wisdm_proj_num_primary | use_of_radioactive_materials_other | use_of_human_subjects_other | use_of_biological_materials_other | use_of_animal_subjects_other | trade_secret_and_proprietary_information | Social Determinants of Health | request_primary_topic_other | request_area_served_city_9 | request_area_served_city_8 | request_area_served_city_7 | request_area_served_city_6 | request_area_served_city_5 | request_area_served_city_4 | request_area_served_city_3 | request_area_served_city_2 | request_area_served_city_10 | request_area_served_city_1 | progress_report_current_grant_title | po_number | pi_short | past_funding | nce_duration | mcw_applicant_question | leveraged_sources_list_url | keywords | keyword_5 | keyword_4 | keyword_3 | keyword_2 | irb_review | future_funding | funded_project_status | earliest_timeframe | demo_values | current_funding | catalyst_video_upload | cash_match_funding | attestation_signature | additional_information | Gifts Reference Number | Gifts ID Number | Organizational Strengths | Strategy | Activities | Challenges and Risks | Results | Sustainability | Project Outcomes | OAC IRB Activities | Fiscal Organization Information | PERC - Application Resubmission | Reviewer Comments Edited | Comments to Applicant | theory_of_change | the_challenge_text | social_determinants_of_health_addressed_text | review_summary | results_text | request_area_served_region | request_abstract_impact | request_abstract_description | pull_out_quote_text | pse_change_goals | project_goal_text | past_funding_detail | more_information | mentoring_committee | looking_to_the_future_text | learn_more_text | lasting_impact_text | key_partnerships | irb_review_detail | grantee_name | grant_administration | geographical_area_served_text | future_funding_detail | evaluation | community_relevance_and_engagement | cash_match_detail_2 | cash_match_detail_1 | brief_abstract_and_impact_statement | background_research_and_evidence | at_a_glance_text | application_resubmission_form_detail | additional_collaborators | active_pending_spreadsheet_url | Gifts Declination Notes | Gifts Project Description | Full Application Due Date | IRB Approval Dropdown | Interview Date | Interview Start Time | Five-Year Plan Goals Addressed | Module Type | Application Co-PIs |
R-5069-2022 | One= Test Organization | Testing December 2021 Strategic Grant Applicatio= n | Dec 2021 PERC Strategic Grant | LOI Draft | 12/31/2021 | 12/30/2022 | $  = ; &n= bsp; 1.00 | 5069-2022 | 1 Test Road | Apt 123 | Madison | 980 | Wisconsin | United States | 53711 | XX-XXXXXXX | WPP Program Officer Name | Partnership Education and Research Committee | PERC Strategic | 12/8/2021 | 12 | 12/8/2021 | 1/20/2022 | Testing | User | testing@wisc.edu | Program Officer First Name | Program Officer Last Name | ProgramOfficerEmail@wisc.edu | Testing | User | testing@wisc.edu | Project Summary Not Needed for Dec 2021 PERC Str= ategic Application | 4/1/2022 | 24 | Other | Clinical Research | Strategic | Partnership Education and Research Committee | No | Testing | ICTR |
In other words, there is one = empty space for an organization in a certain request. That is why we can on= ly have one organization for a request.
Organization however, is independent from the request. Requests are like= birds on its roof. It doesn't even know that they are there (we can retrie= ve the information when we do a query).
The following chart is showing the main linking that is in place in Flux= x Grantmaking Database: