Data relationships Database schema

A proposed database / Django app design / API endpoint schema

Overview

Goals

The goal is to make a modular data architecture such that meaningful, self-contained apps containing subsets of data can be defined, and the interactions across the apps can be simple, minimal, and meaningful.

Each app should expose data feeds limited to that app's scope. For higher-level feeds, such as disclosure information, information across multiple apps is combined. Such an app need not store any data of it's own.

Schema / apps

Below, there are five apps:

• ballot - generic balloting definition for Y/N and multiple-choice ballots (including elections)
• candidate - metadata about ballot measures that are elections, ballot measure choices that are candidates
• locality - different ways to reference localities (cities, states, jurisdictions)
• contributions - raw data for political contributions from committees. This is where most of the back-end work is.
• disclosure - feeds for querying contribution data by locality, candidate, ballot, etc. This is what the front-end folks will care most about, but will be easy for the back-end folks.

Some key ideas:

• disclosure contains no data; it just JOINs data from other apps and exposes the most important feeds for our app.
• ballot is generic for any multiple-choice ballot measure, including elections for offices. This makes connecting money to candidates and any other ballot measure simple.
• There are many ways to query data based on locality. locality:Jurisdiction contains fips_id, and so is the core linkage system there. Anything else (zip codes, cities, counties, "combined statistical areas" will need to resolve to fips_ids

Looking forward:

• This modular structure may work well with abstracting out components, like:
  • An app (or set of apps) to implement a Django back-end for the Voting Information Project Specification or OpenCivicData / Popolo Project and Google Civic Information - having to do with ballots and elections.
  • An app (or set of apps) that could be shared with the OpenFEC API and existing tools

Additional apps (for completion, not listed below):

• netfile - would be great to pull this out (like calaccess_raw) as a separate project later; if we can make it a clean, independent Django app now, it would be easy to do so in the future!
• zipcode_metro - again, this could be it's own separate project if we like--perhaps abstract out parts of locality above to make a reusable component for CSA/PSA.

Proposed Apps and their feeds/models

ballot app

Generic balloting definition for Y/N and multiple-choice ballots, including elections

Feeds

• /ballot/:ballot_id - raw ballot info, plus all ballot measures (and choices?)
• /ballot/measure/:ballot_measure_id (Mockup 3) - ballot measure info & choices

Data tables

Ballot/Vote/Contest

• ID
• Jurisdiction:ID
• Date

BallotMeasure/Referendum

• ID
• Ballot:ID
• Text

BallotMeasureChoice (if none present, ballot measure is binary)

• ID
• BallotMeasure:ID
• Text

---

candidate app

Metadata about ballot measures representing elections

Feeds

• /candidates/ballot/:ballot_id - all candidate info for a specific ballot
• /candidates/measure/:ballot_measure_id - all candidate info for a specific office
• /candidate/:candidate_id - detailed information on candidate (party, location, history of running for office)
• /election/:ballot_id - all info related to a specific election
• /elections/<type>/:locality_id/ - find all elections in a specific locality (jurisdiction, city, state, PSA, etc.)

Data tables

Person

• ID
• Name
• PartyAffiliation:ID
• Contact info

Candidate

• ID
• Person:ID
• Office:ID
• BallotMeasureChoice:ID (gives jurisdiction)

Office

• ID
• Jurisdiction:ID
• Other data

Election

• ID
• Office:ID
• BallotMeasureChoice:ID

---

locality app

Different ways to reference localities (cities, states, jurisdictions)

Feeds

• /location/<type>/:locality_id - get detailed information about a locality
• /locations/inside/<type>/:locality_id - get all locations (zip, city, etc) found within a given locality
• /search/<type>/?q=? - return all localities of the given type (default: fips)

Data tables

CSA/PSA

• ID
• Name

Jurisdiction - no metadata, just a common linkage system for other tables to use.

• ID - fips_id
• Type (for back-pointing to which table it came from, essentially)
• CSA/PSA:ID (is this unique?)

ZipCode

• ID
• ZipCode
• Jurisdiction:ID (is this unique?)
• City:ID (is this unique?)

City

• ID
• Name
• County:ID (is this unique?) referes to state.
• Jurisdiction:ID (is this unique?)

County

• ID
• Name
• State:ID
• Jurisdiction:ID (is this unique?)

State

• ID
• Name
• Jurisdiction:ID (is this unique?)

---

contributions app

Raw data for political contributions. This is where most of the back-end work is.

Feeds

• /contributions/measure/:ballot_measure_id - all contributions (support&oppose) for all choices in a given ballot measure
• /contributions/choice/:ballot_measure_choice_id - all contributions (support&oppose) for a single choice in a given ballot measure
• /contributions/committee/:committee_id - all contributions ever made by a specific committee.
• /committee/:committee_id - metadata (name, etc) about committee

Data tables

Forms

• ID
• Type (city/county/state)

Committee

• ID
• Name
• Jurisdiction:ID (can be null)
• Contact Info

Contribution

• Form:ID (needed for purging procedure)
• PeriodStart (start date of contribution period during which this was received)
• PeriodEnd
• Amount
• Committee:ID
• BallotMeasure:ID (refers to jurisdiction)
• BallotMeasureChoice:ID (can be null for binary)
• Support/Oppose

ReportPeriod

• Ballot:ID (refers to jursidiction)
• PeriodStart
• PeriodEnd

---

disclosure app

feeds for querying contribution data by locality, candidate, ballot, etc. This is what the front-end folks will care most about, but will be easy for the back-end folks.

Feeds

• /city/:city_id - returns city name, high-level totals of money within the city's election.
• /city/:city_id/ballot - returns the combined contests of all jurisdictions within the city
• /ballot/:ballot_measure_id (Mockup 2, Mockup 3 - contribution totals and contributors, per referendum
• /committee/:committee_id(Mockup 1) - Metadata & high-level contribution information (ballot measures, localities, etc)
• /committee/disclosure/:committee_id (Mockup 8) - Showing contributions to a committee

Data tables

No models.