Central Candidate Database Kick-off E-mail
On Tue, Jun 5, 2012 at 10:28 AM, Jim McIntosh <jim.mcintosh@sympatico.ca> wrote:
Hi Christin;
The attached file includes a description of the spread sheet (MEMLIST.xlsx) that I use to maintain the mailing list for members, inquiries and referrals. It should form the starting point for the requirements for a DB.
Assuming we are going to use an SQL-type DB, I anticipate we would likely have the following tables;
Contact info (Name, phone(s), email, source, Membership Status, In Date, Join Date, Sign Date)
Address (including Riding? We have several ‘couples’ with the same address, but this could be part of the Contact table)
Donations (Date and amount of each donation)
Officers ( Leader, Deputy Leader, Executives, Ethics Committee, Auditor, (etc?) - a 2-way, 1-1 link to the Contact info)
Candidates (Election Date, Riding, Status, Endorsed, votes, %, Rank) (Rank would be like 4th of 7 candidates)
Comments (free form text, possibly with tags/keywords to aid in searches)
Regards; Jim
Table: People
intOPLPIDnum int(11)
A unique value identifying every single mind connected to the party.
txtFirstName text
Given name, no prefixes, no suffixes. TODO: add fields for stuff like "Dr. Mr. Ms. Mrs. etc."
txtLastName text
Family name. TODO: add fields for stuff like "Dr. Mr. Ms. Mrs. etc."
vcAddress text
Entire mailing address. Lines to be delimited by colons.
vcEmail text
Primary E-mail. TODO: Add miscallaneous, and official party, e-mail address.
enumMemStatus enum('ActiveVoting', 'Inactive', 'ActiveNonVoting' )
ActiveVoting, ActiveNonVoting or Inactive
dateInDate date
A field Jim suggested we needed. To be defined.
dateJoinDate date
Date the person joined the party.
dateSignDate date
A field Jim suggested we needed. To be defined.
intOldLiveInRiding int(11)
Riding number where the person dwells based on old 107.
intNewLiveInRiding int(11)
Riding number where the person dwells based on new 122.
boolIsRegionalCoordinator tinyint(1)
True = Yes - intRegionalCoordinator Field becomes the field they coordinate. False = No - intRegionalCoordinator Field becomes the field coordinator they would work with to volunteer.
intRegionalCoordinator int(11)
As defined by boolIsRegionalCoordinator
txtComments text
Any notes. Should be regional coordinator level appropriate.
intCurExecutivePosition int(11)
Currently held executive position number.
txtPastExecutivePositions text
Past held executive position number. Colon deliminated.
txtWhenPastExecutivePositions text
Past held executive positions by year. Colon deliminated.
intPastCandidate int
Most recent 107 based number. After 122 implemented, 107 nums will either be converted, prefixed, or archived.
txtWhenCandidate text
Text identifier for most recent election the individual participated in. "2011" = 2011 provincial election Identify other elections by any simple string.
(Relevant Material) from Original Attachement (Treasurer & CFO Duties)
MEMBERSHIP LIST (MEMLIST.xls) Information about members, people who have requested information about the Party, and people who have been referred to the Party by members is kept on a spread sheet named ‘Ont’ in an Excel file named MEMLIST.xlsx. There are several other spread sheets in this file.
Contact info
Name (including Prefix such as Mr. Ms., Dr., Prof. and Suffix such as CA, P.Eng), Salutation (if other than first name), address, telephone numbers, email ID, web site, Company name and Riding are maintained.
Note: If a contribution is received from a corporation (or a union), the name of the Corporation, Company or union should go in the LASTNAME column.
Status
The following Status codes are used;
I Inquiry – an individual asked for information about the party.
R Referral – Someone (usually a member) has asked us to send information to the individual
D Donor – has made a contribution to the Party but has not joined.
M Associate Member
V Voting Member
X Exchange – Someone we keep on the list indefinitely, either to exchange newsletters or as a friend of the Party
Expiry Date – The date the individual’s membership in the Party expires, or the date the Inquiry or Referral will be removed from the mailing list.
Contributions
There are four columns used to record Contributions; Update The date the donation was deposited in the Chequing Account Deposit The amount of the donation
Donation Amount donated year-to-date not including the amount in the deposit column
LastDon The date of the previous donation
Other Columns
Comment Used for a variety of purposes, such as identifying potential candidates
Position Used to identify members of the Executive and Ethics Committees and others active in the Party.
Area Identifies a riding as being in a geographic area such as Toronto East or Northern Ontario. Used to produce lists for regional coordinators to contact members and others on our mailing list.
In Date The date the individual asked to be added to the file. This may be the date on an Inquiry email sent from our web site or the date on a cheque or PayPal notification, or the date the person visited our booth at some event.
Source How the Party got this person’s name (web site, booth, another member)
Join The date the person became a member of the Party
Sign The date the member last signed the membership form, usually the same date as Join, but could differ if the person joined as an Associate Member first and later signed as a Voting Member. Members may also be asked to sign a new membership application if the criteria for membership are changed at a Convention.
Update The date of the last change to this person’s record. Depending on the Reason column, it could be the date: the information was added to the spreadsheet for a new inquiry or referral; the Donation was deposited; or the address was changed.
# Used to provide a count of the number of names on the list, or when the list is filtered. This column contains a “1” except for corporations.