Re: Modeling and Normalizing
Posted by:
Rick James
Date: February 18, 2010 02:29PM
Close.
Let me start by asking you what "entities" (people/place/thing/...) you have, and what "relations" there are between them.
Your "Candidate" in an entity. What is all the static information you have about each candidate? Your list is good, but missing "party". (Yeah, a candidate can switch parties, but let's call that an exception.) So, add party_id to the Candidate table.
In a simple case like parties, I would go for something more readable than an AUTO_INCREMENT. Here are two suggestions:
CHAR(1) -- D (Democratic), R, I, G, A, ...
ENUM('Dem', 'Rep', ...)
With that, you don't really need a table for parties.
Another Entity is an "election". I lean toward having an "election_type":
ENUM('General', 'Primary', 'Runoff', 'Runnoff2')
This would turn two tables into one (with an extra column, and more rows).
District "entities": You have already lumped Senate and HOR districts.
Hmmm... An candidate runs for a specific District. So, should it be in the Candidate table? I think so. But... this further ties the table to _this_ election. If you plan on using the schema again for the next election season, we should think about how to deal with that...
Plan A: Different database for each election.
Plan B: Another column in the candidate table to indicate which election. This would lead to the some of the same persons in that table multiple times (but possibly with different party affiliation and/or district).
Vote counts table:
PK (auto_increment)
Candidate
Party
Race (Senate/..., District/...)
Count.
This table is not really proper, since it replicates the Party and Race, but it is handy since you may be doing a lot of queries on it.
Now on the the "relationships". Hmmmm... I don't see any.
Well, ponder my comments, and make another stab at it. This time, please go so far as to write out the CREATE TABLE statements. Then we can discuss specific datatypes and indexes, too.