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.

Options: ReplyQuote


Subject
Written By
Posted
February 17, 2010 05:53PM
Re: Modeling and Normalizing
February 18, 2010 02:29PM
February 19, 2010 10:15AM
February 20, 2010 11:54AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.