Rename `sponsor_partner` to `affiliate`

[fyliu]

Overview

We should rename the sponsor_partner table to something like affiliate. And the project_sponsor_partner_xref table to affiliation or similar. This would simplify and clarify the ideas behind these tables.

Also more clear would be converting the project_sponsor_partner_xref.is_sponsor field to type. See discussion below.

Action Items

ERD, spreadsheet, code changes for each of these

• [ ] rename sponsor_partner to affiliate
• [x] rename project_sponsor_partner_xref to affiliation. Made part of #65
  • [x] convert the is_sponsor field name to type and make it a choice or enum. Made part of #65

Resources/Instructions

Proposed Changes

• Rename the project_sponsor_partner_xref table to affiliation or a more appropriate descriptive term for the relationship.
• Rename the sponsor_partner table to affiliate.
• Make is_sponsor is a choice like choose between sponsor or partner, and call it type

What the changes give us

Mostly simpler naming for these concepts.

Clearer code when working with the relationship and model.

Example to create a project-affiliate relation

Affiliation.objects.create(project="PeopleDepot", affiliate="Code for America", type="partner")

The current way would look like this

ProjectSponsorPartnerXref.objects.create(project="PeopleDepot", sponsor_partner="Code for America", is_sponsor=False)

_Originally posted by @fyliu in https://github.com/hackforla/peopledepot/pull/270#discussion_r1529113443_

[Neecolaa]

• Definitely not opposed to changing sponsor_partner to affiliate. The way I see it, it depends on the likelihood that we'd add another affiliate type. If there's always going to be only the two, we avoid having to use a foreign key to a separate type table by using the boolean.

  • Can we edit enums after the fact like type tables? Currently we only use it for the days of the week because those aren't expected to change. Same question for a django choice.
  • We technically already have 4 affiliate types (project partner, project sponsor, org partner, org sponsor), but our current schema does take care of the overlap.
  • Our current schema is not taking into account other orgs. The org table is not currently associated with the affiliate table in any way, because we're the only org using the system. Is this something we want to build in now?
  • Part of the reasoning for making sponsor/partner a boolean is because an affiliate cannot be both a sponsor and a partner of the same project. Making sure the project_id/affiliate_id is unique would do the same thing, I believe?

• The xref tables all use the naming convention table_a_table_b_xref and I'd like to keep it that way. The name project_affiliate_xref makes it clear that the table contains the relationships between projects and affiliates in a way that affiliation does not.

[fyliu]

Sorry I thought posted this that day but I didn't click submit.

The consistent table naming convention sounds good. What if we kept the table_a_table_b_xref as the table name so it's consistent within the database, but changed the model name to Affiliation so that the coding side looks more like English? I believe django models allows specifying a different table name.

Seems like a good idea to set up the association with the org table so we don't have to do it later.

I know it was decided that it's going to be an either or for the project sponsor or partner. But I wonder about the likelihood that it will become something like the org where they can be both. Maybe we need to create a decision record or at least a statement so we're confident this is going to be stable.

I think enums or choices are just short strings. Or maybe that's one of several way to do it but it's the one I can remember. If it's set to 1 char long, then there's that many possible values we can later use. Django translates from sponsor and partner to s and p for our case. We set up the mapping ourselves in the code and the db sees the short strings.

You're right. I realized while testing the PR that the affiliate table needs a uniqueness constraint on the (project_id, sponsor_partner_id) pair. I'm making it part of that issue.

[fyliu]

Updates and actions needed

From comment above:

• We will rename the sponsor_partner table to affiliate because a single word is easier to use than 2
  • FK references in other models may need to be renamed as well
  • [ ] create issue to rename the table
• The project_affiliate_xref(Affiliation) table's uniqueness constraint is being done in #65 in PR #270

From the meeting:

• We will rename the django model Affiliation for ease of coding, but need to keep the database table name for consistency with the existing xref naming convention.
  • [ ] do this in a new issue or as part of #65?
• It's not a priority to convert affiliate.is_org_sponsor into a FK relationship with org. It looks to be a good idea but can be discussed more and decided later.
• The affiliate's ability to be both partner and sponsor to org, but is limited to either partner or sponsor to project is a business requirement, so we will implement it this way.

[fyliu]

I forgot to bring up the enum during the meeting. It's part of #65 so I'll comment there.

[fyliu]

Cleaning things up a bit here. I made some of the changes part of #65 since that's being worked on right now. The only change remaining here is what the title says.

I just realized I still haven't confirmed the boolean to enum change but it's already being coded. Will have to do that with @Neecolaa at the next meeting to figure out what's best. We can undo the code change if it's not a good direction.

[fyliu]

Here's a comparison of them.

1. boolean (database boolean)

   • great space usage in database
   • limited to 2 values
   • field has to be called either is_sponsor or is_partner and infer the other based on the boolean value

2. enum (database varchar)

   • uses less space if it's just 1 or 2 chars long or an int
   • supports 2 values for this case
   • can add more values but it has to be done in code, not db
   • field can be called affiliate_type and printed out as string like my_affiliate.affiliate_type.label == "Sponsor"
   • more clear what the value is
     • in db, affiliate_type can be "sponsor"/"partner", or "s"/"p" if 1-char long, or 1/2 if int (but not clear what the numbers represent, which may be okay since the code knows what it is)
     • in code, my_affiliate.affiliate_type == AffiliateType.SPONSOR

[Neecolaa]

After a discussion with Bonnie at the People Depot meeting, we've decided to add the boolean is_partner to project_affiliate_xref.

• Projects can now have an affiliate be both a sponsor and a partner.
• An affiliate can be a sponsor and/or partner of the org without being a sponsor and/or partner of any projects.
• If an affiliate is a sponsor of any project, they are also considered a sponsor of the org.
• If an affiliate is a partner of any project, they are also considered a partner of the org.

A partner is an entity that's doing ongoing work with us on the project. A sponsor is an entity who has provided funds or an in-kind donation.

[fyliu]

Unassigning @freaky4wrld since the coding is done. We just need to change the ERD and spreadsheet for the rest of this issue.