Database schema

[dbolson]

Based on the initial proposed schema and data from New Jersey, here is a proposed updated schema. I left off a few details from the first one but only for the sake of clarity here. This would be version 1 of a few before we would have a final schema for the mvp.

candidates
first_name	varchar(255)
last_name	varchar(255)

candidates_elections
candidate_id	integer
election_id	integer
party_id	integer

districts
name	varchar(255)
state	varchar(2)

district_election_results
candidate_id	integer
district_id	integer
election_id	integer
votes	integer

election_types
name	varchar(100)

elections
election_type_id	integer
year	smallint default date_part('year', now())
state	varchar(2)

municipalities
district_id	integer
name	varchar(255)

municipality_election_results
candidate_id	integer
election_id	integer
municipality_id	integer
votes	integer

parties
name	varchar(255)

[dbolson]

• Should candidates have a middle name?
• candidates_elections has the party for a candidate at the time of the election.
• My assumption right now is that a state election will have results by district and possibly results by municipality (NJ has the latter), so we would keep the vote counts in both district_election_results and municipality_election_results.
• It's probably not necessary to have a states table because we can use a uniform two digit lowercase state code. We could add a database constraint to normalize these in different tables (eg., always a two digit lowercase string).
• Again, this schema does not yet have all the fields from the original schema. Once we agree on this basic one, we can add more fields to the tables.

[tarheel]

Thanks for iterating on this. One other wrinkle: we want to be able to store data about how the people within a district voted in other elections, e.g. the 2012 presidential election or a 2014 U.S. Senate race.

[dbolson]

The district_election_results should take care of this. It records

• the total votes ("10,001") of
• a single district ("District 1, NJ") for
• a single candidate ("Hillary Clinton") in
• a single election ("2016 Presidential")

This is the same for municipality_election_results.

Does this get us the data you're talking about?

[tarheel]

Oh, I see. I had misunderstood your design.

[dbolson]

Would it be helpful to show example data for this design?

[tarheel]

Nah, it's clear now.

[tarheel]

Some suggestions on the schema:

(previously discussed)

• remove unique index on state from elections and districts

(new)

• add unique on (first_name, last_name) to candidates (this may not work well once we add more states and we get name conflicts, but I'm finding it helpful at the moment, at least)
• add unique on (state, district_type_id, name) to districts

[tarheel]

Also add the following columns to the *_election_results tables:

• percentage (numeric)
• withdrew (boolean)
• won (boolean)