Membership DB

From US Go Wiki
Jump to: navigation, search

Contents

First Phase Requirements

These are the requirements which must be met before we can call the new system the official one. Details for these can be found in subsequent sections.

  • Answer all Open Questions
  • Ensure that the implementation of all membership types, descriptions, benefits, and fees is correct
  • Have a working join/renewal/edit-info interface for members/chapters.
    • This includes PayPal as the first-phase method of payment. Generic CC payments should be added after that.
  • Implement a flag to track who has a printed card
  • Ability to edit expire date arbitrarily
  • Share data with the ratings DB.
    • Jon and I are working this out
  • TD list export
  • Emails are not unique per member record. Provide a way for the user to choose which member record they want to reset the password for.
  • Review final version with Sam
  • Replace/update anything on the website that depends on the old DB or exports from it. Items I'm aware of:

Open Questions

  • Are complimentary members lifetime or not?
  • What member info is ok to share with chapter reps? Email, address? What about with other chapter members?

Answered Questions

  • The new DB will allow members to log in, renew membership, update their info, etc. Members with an email address can request (or be assigned) a password -- that's no problem. What about users with an out-of-date email or no email at all? Should we require they contact us, or let them log in with something like AGA ID + last name / zip code?
    • Andrew sez: members w/o email should contact membership@usgo.org (or registrar? whatever the right address is)
  • What is the most effective way to pull in membership data (new members, renewals, etc.) from tournaments? Is there a standard digital format for this yet? Are TDs required to use it?
    • Sam has provided me with sample tournament-report data. We (Justin, Jon, Sam) will work out the best way to share this with the ratings DB.
  • Is the At-Large voting region relevant for the DB? How to track it?
    • Sam has explained that At-Large stuff isn't relevant to the DB. The Glossary could include a short description of regions & reps, perhaps.
  • How is continuous membership currently determined?
    • Sam figures it out through various, mysterious means. The new DB stores a history of changes so will be able to calculate this once there is actually sufficient history stored (the new system will need to be live for a year before that will happen).
  • How will members be allowed to pay online? PayPal, credit card? Steve provided some basic PayPal info. For CC, I'll need merchant account details.
    • We'll start with PayPal and roll out any others as needed/possible
  • Youth membership discounts appear to use 23 as the cut-off date, but the TDList uses 18. Is that intentional?
    • Sam says it's not intentional
  • How should promotional and hardship sign-ups be handled online? (See http://www.usgo.org/chapters-supp.html and https://usgo.org/chapters.html). Can someone signing up just say they're a member of a chapter and get the discount, or should there be some sort of verification? Can promo members renew for a second year at the discounted rate?
    • I've gone ahead and created an email-voucher feature: chapter reps can send anyone an email with a special link that lets them receive the promo discount.
  • Should promotional members be set as Full, with a flag to denote that they received a promotional discount?
    • Made an executive decision and said yes.
  • Should complimentary members be included in the TDList? The current TDList excludes them but according to my notes, they have full benefits.
    • Sam says yes.
  • Are institutional sponsors tracked in Sam's DB? Should they be tracked in the new one?
    • Sort of. There's a type for them now in the new DB.
  • What do the PP, P, and WP values mean in the Credit Card field of Sam's DB? What do the codes in Sam's "Source Referral" field mean?
    • Not using these in the new DB
  • Is Sam's contact_html field more up to date than the chapter contact/email/phone fields? Can they be merged somehow?
    • There's no clear way to merge them automatically. I've made the field non-html and will see about consolidating in the future.
  • Should complimentary types be set as lifetime, with a flag to denote that it was complimentary? Can the foreign type be merged with one-time or promotional types?
    • Sam says this is up to me to decide, so I'll go with what I think works best.
  • What do the codes in Sam's Returned field mean?
    • Sam says the data's not reliable and will clean it out. We'll resync with printed records somehow.

To Do / In Progress / Complete

Interface for Admins

http://usgo.org/mdb/

Data

  • import latest from Sam (members + chapters/clubs)
    • "AGA Membership tables.mdb", table Members (exported as CSV to Members.txt)
    • "chapter data.mdb", tables Chapter & Clubs
  • save "special" field as legacy_id (lifetime members' previous ID)
  • add country field
  • change citizen_of to checkbox for US citizenship; make old field legacy

Member Type Descriptions & Fees (store these somewhere? editable?)

  • Fees: Update per Sam's instructions - Full: $30 for first year, $20 thereafter
  • Youth - not a real type. Used only for TD List export, some emails. Generated from DOB (< 24 years old).
    • Youth time $10/yr unless filling up, then $8/yr with $100 cap
  • Full (rename from Regular)
    • receives members' e-journal weekly
    • yearbook annually (if member during December)
  • Type E - E-Journal Members, $20/yr, e-journal only, no other benefits
  • Promotional - 1/2 price, $15/yr up to 2 years; can renew later; full benefits
    • Full w/ flag or own type that gets auto-changed after use?
    • Ability to specify arbitrary promotions?
  • Sponsor - $120/yr (4x), full benefits, name in yearbook
  • Sustainer - $60/yr (2x), full benefits, name in yearbook
  • Foreign - rating only, no benefits, no fees
  • Complimentary - lifetime, full benefits - usually org or pros
  • Provisional - rating only, no benefits, no fees (should have paid to play in tourney)
  • Non-member, same as provisional but paid proper $10 fee
    • Rename to "one-time"
  • Lifetime - $1000, full benefits
    • can change their ID to <100 (must request via email) - interface to allow it
  • Library - $30/yr, full benefits
  • Type I - add as sponsor, $ donated already noted in Fee field
  • On-hold => Pending
    • status for people paying via check, or bounced payment
    • can log in to pay, no other benefits
  • Chapter $35/yr
    • no e-journals or yearbooks
    • contact person not required
    • must have email
  • Flag for whether received printed, plastic card ("returned" field in Sam's DB)
    • Member given temporary paper card in the meantime
    • UI for exporting + setting flag for new members all at once
      • There's a query for it; bulk edit can be used to update until a better UI is in place

Chapters & Clubs

  • merge old status (Stat) field with members status field
  • remove size field, maybe add a note to one of the freeform textareas
  • chapter = paid, can run tourneys; club = not paid, not in MDB
  • chapter-specific fields in separate table; rest only in MDB (link by member ID)
  • coordinate with Alf Mikula from promotego.org about mutual data exchange
  • contact person AGA ID (contact person address separate from club address?)
    • past ones stored in address1? [what does this mean?]
    • there's a field for Rep ID now; still needs to be filled in for everyone, probably by hand
  • integrate voting rights algorithm (see email from Steve)
  • remove manual input of chapter code, link members to chapters via IDs
  • view members per chapter
  • track chapter members that belong to AGA and not
  • login allowed for contact person
  • clean up "html" fields

Payments

  • payment history
  • record payments and expire date extensions via special forms (no direct expire date editing)
  • track extension length
  • button to extend X number of months
  • perform CC payment on behalf of member
  • Special (admin-only) edit button for expire date

Automatic Renewal Reminders

  • 90, 60, 30 days, immediately before, after expires

Email

  • list emails per query, for copy & paste
  • coordination with whoever manages the email list
    • MailChip API?

Exports

  • for postal mailings like yearbook -- "journal labels members.bas"
  • CSV?
  • checkboxes per field

Queries

  • honor roll (lifetime, sponsor, sustainer)

Voting Regions

  • Eastern (states that touch Atlantic + NH, WV)
  • Western (HI, AK, WA, OR, CA)
  • Central (all the rest)
  • At-Large

Reports

  • printable (PDF?)
  • for envelopes; per record
  • voting chapters
    • list of chapters w/ current members & link to expired members
    • can be made public
    • grouped by region
  • members per state/region
  • tournament eligibility
    • continuous membership
    • X games in the last year
    • strong players (or sorted by strength)
  • Payments/revenue
    • per year, month, etc
    • source (web, in-person, etc)
  • Concise, copy-pastable view of individual members

Ratings

  • read-only to users
  • inserted directly into underlying DB by external ratings software
  • may or may not be the official location for ratings data (up to Phil and the ratings folks)

Tournaments

  • link to rating history
  • result reporting
    • import result file (web interface?)
    • match names (family name + beginning of given) and IDs
    • for unmatched, allow loose searching, seeing within list context
    • checkbox for new (unmatched) provisional members
      • assoc this member with the tourney source (used to be address1?)

TD List

  • provide realtime data in various formats

List view

  • built-in field display toggle in browse view ("show/hide columns")
  • don't search ratings fields in general search
  • extend width
  • click anywhere on row
  • remove delete from list

ACL

  • read-only users
  • per model/field?

Other

  • UI: continuous scrolling table
  • fix minor IE styling issues
  • changelog: revert/undo & undelete
  • editable tags
    • check to ensure not removing used tags
  • editable type, w/ descriptions & fees
  • editable regions
  • cron hookup for scheduled actions
  • USPS API stuff
  • search builder UI
    • zip code should search by prefix
  • edit form: hilite expired date in red
  • better rating format in browse view (don't round, just truncate)
  • save queries

Interface for Members

  • Username (email) & password for members area (password already exists for some members)
  • allow members to update own details
  • extend membership
  • member directory?
  • look up existing member records to avoid duplicates
  • force approval for all new member records

Wish-list

  • pwaldron: It would be nice to have integrated delivery of files or links to files attached to each member's login.

[Feel free to add anything]

Personal tools