X-Political%20Org%20Database%20Design

1
X-Political Org Database Design

• By Lisa de Larios-Heiman
• Jon Snydal
• Margaret Spring
• Annie Yeh

2
Organization Website was here, but I removed it
for anonymity.
3
Needs of the client

• Managing Information About
• Committees
• Campaigns
• Members
• Sponsors
• Partners
• Communication
• Events

4
Needs of the Client - Problems

• Current problems
• Information either undocumented or documented in
  Excel spreadsheet not accessible by everyone
• Inability to do complex queries
• No defined processes for system and data
  maintenance
• Information not integrated into the X-Political
  Org website

5
EER
6
EER to Relational Design
Members and Committees
1 Person (P_ID, Title, FName, MName, LName,
Affiliation) 1c Member (Mem_ID1, MemStatus,
MemExpiration, MemSince, DuesType, PaymentMethod)
7 Committee (Comm_ID, CommitteeName) 8
CommitteeJoinMember(Comm_ID7, Mem_ID1c, StDate,
EdDate)
7
EER to Relational Design
Campaigns and Managers
1 Person (P_ID, FName, MName, LName,
Affiliation, Title) 1a Candidate (Can_ID1,
PartyAffiliation) 1d CampaignManager
(CM_ID1) 6 Campaign (Camp_ID, electionCycle,
Region, CM_ID1d, LabelPurchase,
CommEndorse, MemEndorse, EarlyEndorse) 6a
Can_Camp (CanCamp_ID6, Can_ID1a,
PoliticalIDNum, Position, Incumbent,
EndorseRank) 6b Measure_Camp (MeasCamp_ID6,
GovtMeasIDNum, Meas_ID17)
8
Query 1 Voting Eligibility
Problem
Which members have attended at least 3 events
List members first name, middle name, last name,
number of events attended, and membership status.
Relevant Entities
Relevant Tables
9
Query 1 Voting Eligibility
Query in Design View
Query Results
10
Query 2 Member Managed Campaign
Problem
What campaigns did members manage? (members not
always campaign managers)
Relevant Entities
Relevant Tables
11
Query 2 Member Managed Campaign
Query in Design View
Query Results
12
(No Transcript)