[NCUC E-team] Member database maintenance

[Tapani Tarvainen]

Here's some basic documentation of the member database.

Maintaining the database here means simply keeping
member data up to date: adding new members,
correcting and updating data for old,
deleting resigned members.

(Unfortunately there's no interface for members
themselves to update their own data directly.
I was going to write one but... perhaps ICANN labs
will come up with something.)

The database is quite simple, with PostgreSQL as db engine.
As noted earlier there's no custom interface, but
phppgadmin is easy enough even if not exactly pretty.

Access details including URL, login &c can be found
in the ncuc1 server in /root/member-database-access.txt
(presently accessible to me, Wilson and Brenden;
just let one of us know who should be added).

Phppgadmin's main drawback is the lack of any content-specific
help or safety features like undo, and its error messages
are sometimes rather cryptic, but I've peppered the database
with a heavy dose of checks, constraints and triggers that make
it somewhat hard to do much irreversible damage.

There is only one database, 'ncuc', and only one table,
'ncucers', that normally need to be touched, with the
following columns:

id           internal index (autogenerated, never change)

name         name (no separation of first and last name,
	     by convention "firstname lastname" but
	     that's not checked in any way).

role         'OffRep' for an organization's official representative,
	     'AddRep' for additional (non-voting) represetatives,
	     'Ind' for individual members
	     (must be spelled exactly like that).

organization name of the organization.
	     Note, organizations are not listed separately,
	     only implied by their official representatives;
	     there should be exactly one official representative
	     per organization.
	     Also, organization probably should be null for
	     individual members but isn't in all cases
	     (these should be checked).

country      obvious (blank for several members, unfortunately)

domain       copied from NCSG data, not presently used anywhere
website      ditto

email        primary email address (used for ballots);
	     must be unique (but can be null if unknown),
	     forced to lower case

email2       alternate email (to be used in case primary fails)

orgsize      'SMALL' or 'LARGE', null for individuals

state        membership status, possible values:
	     0 = new member, no ballot (not included in voter roll)
	     1 = old member, contact info not confirmed, no ballot
	     2 = member in good standing, included in voter roll
	     3 = removed (ex-member due to be deleted but kept in db pending
	       	 verification; no ballot, not shown in member list in the web)

xkey         random identifier used in data confirmation emails
	     (should be regenerated if needed again, no need to edit)


Adding new members, updating member data and deleting
old ones, as well as simply browsing member data can be
done with phppgadmin GUI, which has buttons like Browse,
Search, Edit, Insert, Delete &c.

Various web pages showing member data use the database
and are automatically updated when it changes.

Mailing lists, however, are not linked to the db in any
way, in particular ncuc-discuss subscriptions must be
handled separately.

That's basically all there is to it - easy as pie.
If you managed to read this far and understood most of it,
you are qualified to take over. :-)


For completeness, here are other tables that may be of interest
(generally not editable):

ncucer_history	  history of changes made to ncucers (starting now):
		  usable for undoing mistakes,
		  reviewing changes, making statistics
		  (in the future this could even be used to plot
		  membership development graphs or something)

ncucer_states	  valid values for state

orgsizes	  valid values for orgsize
		  (no need to change unless bylaws are changed
		  adding medium size organizations or something)

roles		  valid values for role

There're also a couple of tables that have been used
for temporary purposes like comparing data with NCSG,
but they're not being used anymore (should really be
removed, I may do it yet).

-- 
Tapani Tarvainen