The information here is mostly metaphorical examples of real life to help explain complex database concepts in regards to Fluxx data model to serve a more general audience.
Tables
Imagine the database being made out of different spreadsheets which we call Tables. Every row on that table is representing one record of a type represented by that table. For example, if we are adding a Testing User (First 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 / Academic 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 uniquely identifying the record in that table/spreadsheet in our database.
In the Fluxx database, the column names are not the same as the labels we 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 readable labels.
Links
Let's start this section with a story:
When we invite a guest to our house, we prepare for them. We make sure that we make space for them to come and we expect them to come. The other way 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 tell 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 don't expect them and we don't even know they exist. So, if anyone asks us about 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 birds. Our house is going to be our house with or without the birds. But the birds need a roof to sit on.
The same idea is happening in our database. As we mentioned in Tables section, we can imagine tables as spreadsheets. So, like spreadsheets, a Request spreadsheet would not know anything about Organization spreadsheets unless we connect them together somehow. In databases, we can Link the spreadsheets that have relationship together. For example, a Request would like to know which Organization it belongs to. So, there is an empty seat in Requests spreadsheets 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 Explanation | PERC - Use of Animal Subjects Protocol Explanation | PERC - Use of Biological Material Protocol Explanation | PERC - Use of Radioactive Material Protocol Explanation | 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 Application | Dec 2021 PERC Strategic Grant | LOI Draft | 12/31/2021 | 12/30/2022 | $ 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 Strategic 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 only 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 retrieve the information when we do a query).
The following chart is showing the main linking that is in place in Fluxx Grantmaking Database:
