Normalize ballot ready and create county commissioner data mart

deployment-gap-model-education-fund / deployment-gap-model

ETL code for the Deployment Gap Model Education Fund

https://www.deploymentgap.fund/

MIT License

6 stars 2 forks source link

Normalize ballot ready and create county commissioner data mart #294

Closed bendnorman closed 9 months ago

bendnorman commented 10 months ago

This PR:

Normalizes the raw Ballot Ready data into three entity tables: races, elections, and positions.
Reconstructs the denormalized br_election_data data mart from the normalized tables.
Changes county_commission_election_info to have a row for each county and columns for the next general, special and primary elections.

TrentonBush commented 10 months ago

I think the current normalization drops the m:m county:race relationship down to 1:m, and also assigns it as county:race when it is actually a simpler county:position relationship. So basically I think there should be an additional table: election level, position level, race level (aka election:position m:m table) and the new county:position m:m table. Could you take a look at the notebook 36 I added and see if that makes sense to you?

bendnorman commented 10 months ago

Ah good catch thank you! I moved the county information from the races table to a new county:position m:m table called br_positions_counties. I don't think this modeling change actually changes the shape of data_mart.br_election_data.