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 IDLast NameFirst NamePrefixEmailSalutationSuffixUser TitleWork PhoneMobile PhonePrimary OrganizationPrimary TitlePersonal Street AddressPersonal Street Address2Personal CityStateCountryPostal CodeDate CreatedDate UpdatedHighest Degree YearUW DepartmentPrimary AffiliationHighest DegreePrefix DynamicTrackCo-Investigator / Collaborator / Academic Partner AffiliationDepartmentRole NameCitizenship StatusSMPH DivisionPrimary Affiliation Otheroffice_telephoneoffice_faxoffice_extensionemployee_iddepartment_divisiondegreeacademic_partner_affiliation_otherJob TitleIndividual Vendor NumberMiddle NameBulk Update Test FieldAlias (or AKA)Biographical Statement
3055UserTesting
exampleemail@wisc.edu


(608) 123-9874
One Test OrganizationChief Executive Officer





9/20/20212/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)NameAcronymLegal NameStreet AddressStreet Address 2CityStateCountryPortal CodePhoneEmailWebsiteDate CreatedDate UpdatedTax ClassTax IDtax_registration_dateOrganization Total Budgetnumber_of_paid_staffnumber_of_full_time_equivalentOrganization TypeDBA Testorganization_category_other
1665One Test Organization

1 Test RoadApt 123MadisonWisconsinUnited States53711(608) 123-4567testing@wisc.edu
9/20/20212/4/2022
XX-XXXXXXX







Requests spreadsheet:

Request IDGrantee OrganizationProject TitleRequest TypeStatusGrant Start DateGrant End DateAmount RequestedAmount FundedBase Request IDDocument TypesFiscal YearGrantee Org. Street AddressGrantee org Street Address2Grantee Org CityContig IDGrantee Org StateGrantee Org CountryGrantee Org Postal CodeGrantee Org URLGrantee Org Tax IDFiscal OrganizationFiscal Street AddressFiscal Street Address2Fiscal CityFiscal StateFiscal CountryFiscal Postal CodeFiscal URLFiscal Tax IDLead PO/PDSecondary LeadProgramSub ProgramInitiativesSub InitiativesDate Request ReceivedDurationFunding SourceFinal Proposal DateFinal Budget DateDate CreatedDate Last UpdatedPrimary Contact First NamePrimary Contact Last NamePrimary Contact EmailProgram Lead First NameProgram Lead Last NameProgram Lead Contact EmailSignatory First NameSignatory Last NameSignatory EmailFip TitleRequest SummaryDirect Board Authority RequiredThis request is a renewalDigital CheckboxPERC - Application Resubmission Y/Nnce_flagattestation_flagapplication_resubmission_form_flagExtension DateAnticipated Startprogress_report_start_dateprogress_report_end_dateorig_proj_end_datence_start_datence_end_dateform_release_datemeeting_datecomplete_datedeclination_dateOverall Project Amountprogress_report_total_expenditures_amountprogress_report_total_award_amountperc_grant_amountoac_grant_amountnce_amountestimated_other_sources_amountOriginal Grant Amountfiscal org tableNumber of YearsDuration2PERC - Use of Human Subjects ProtocolPERC - Use of Animal Subjects ProtocolPERC - Use of Bio Material ProtocolPERC - Use of Radioactive Material Protocolwisdm_proj_num_secondary_texttrade_secret_and_poprietary_information_otherresponse_to_reviewer_commentsresearch_and_methodologyrequest_area_served_neighborhoodgeographocal_area_served_explorergeneral_additional_informationcurrent_funding_detailcity_town_or_villagecalendar_year_awardedadditional_informtation_detailType of SupportFiscal Organization ExistsFocus of GrantTactics and MethodsConfidentialityOAC Primary Social DeterminantOAC IRB ReviewPERC - Primary TopicPERC - Earliest Timeframe for ImpactPERC - Research/Education ClassificationPERC - Pop Impact - RacialPERC - Pop Impact - UnderservedPERC - Pop Impacts - GenderPERC - Pop Impacts - Age GroupsPERC - Geographic ImpactPERC - County ImpactPERC - Use of Human SubjectsPERC - Use of Animal SubjectsPERC - Use of Biological MaterialPERC - Use of Radioactive MaterialGiving Program, Type, StatusUrban/RuralLength of ProjectDeclination ReasonUse of Radioactive MaterialsUse of Biological MaterialsUse of Animal SubmectsUse of Human SubjectsProgram AreaPERC Primary Categories Research categories Internal ProgramRequest Area Served CountyGeographical Area ServedAge GroupGenderEthnicityPopulation ServedOAC Primary CategoriesFund:SubfundFundGifts DispositionGifts Request StatusGifts Request TypeDigital SignaturePERC - Primary Topic OtherPERC - Use of Human Subjects Protocol ExplanationPERC - Use of Animal Subjects Protocol ExplanationPERC - Use of Biological Material Protocol ExplanationPERC - Use of Radioactive Material Protocol Explanationwisdm_project_numberwisdm_proj_num_primaryuse_of_radioactive_materials_otheruse_of_human_subjects_otheruse_of_biological_materials_otheruse_of_animal_subjects_othertrade_secret_and_proprietary_informationSocial Determinants of Healthrequest_primary_topic_otherrequest_area_served_city_9request_area_served_city_8request_area_served_city_7request_area_served_city_6request_area_served_city_5request_area_served_city_4request_area_served_city_3request_area_served_city_2request_area_served_city_10request_area_served_city_1progress_report_current_grant_titlepo_numberpi_shortpast_fundingnce_durationmcw_applicant_questionleveraged_sources_list_urlkeywordskeyword_5keyword_4keyword_3keyword_2irb_reviewfuture_fundingfunded_project_statusearliest_timeframedemo_valuescurrent_fundingcatalyst_video_uploadcash_match_fundingattestation_signatureadditional_informationGifts Reference NumberGifts ID NumberOrganizational StrengthsStrategyActivitiesChallenges and RisksResultsSustainabilityProject OutcomesOAC IRB ActivitiesFiscal Organization InformationPERC - Application ResubmissionReviewer Comments EditedComments to Applicanttheory_of_changethe_challenge_textsocial_determinants_of_health_addressed_textreview_summaryresults_textrequest_area_served_regionrequest_abstract_impactrequest_abstract_descriptionpull_out_quote_textpse_change_goalsproject_goal_textpast_funding_detailmore_informationmentoring_committeelooking_to_the_future_textlearn_more_textlasting_impact_textkey_partnershipsirb_review_detailgrantee_namegrant_administrationgeographical_area_served_textfuture_funding_detailevaluationcommunity_relevance_and_engagementcash_match_detail_2cash_match_detail_1brief_abstract_and_impact_statementbackground_research_and_evidenceat_a_glance_textapplication_resubmission_form_detailadditional_collaboratorsactive_pending_spreadsheet_urlGifts Declination NotesGifts Project DescriptionFull Application Due DateIRB Approval DropdownInterview DateInterview Start TimeFive-Year Plan Goals AddressedModule TypeApplication Co-PIs
R-5069-2022One Test OrganizationTesting December 2021 Strategic Grant ApplicationDec 2021 PERC Strategic GrantLOI Draft12/31/202112/30/2022 $                         1.00
5069-2022

1 Test RoadApt 123Madison980WisconsinUnited States53711
XX-XXXXXXX








WPP Program Officer Name
Partnership Education and Research CommitteePERC Strategic

12/8/202112


12/8/20211/20/2022TestingUsertesting@wisc.eduProgram Officer First NameProgram Officer Last NameProgramOfficerEmail@wisc.eduTestingUsertesting@wisc.edu
Project Summary Not Needed for Dec 2021 PERC Strategic Application







4/1/2022


















24











































OtherClinical Research







StrategicPartnership 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:

  • No labels