It's for a project the Kenya National Commission for Human Rights in Nairobi is doing to document and analyze the current post-election violence there. But I'll let him tell you about it himself.

Here is his request, and because of the time frame, I'm putting it up as he sends it:

I have an urgent problem which is more-or-less On Topic for Groklaw, and I wonder if there are people out there who can help us.

As you probably know, following the Kenyan presidential elections last December, there has been widespread violence in the country, after the announcement that President Kibaki had won the election, despite numerous reports of election irregularities, primarily related to the tallying of votes.

This violence has resulted in the reported killings of hundreds and displacement of thousands of people, who have apparently been targeted for belonging to ethnic communities perceived to be affiliated with specific political parties, and in the reported shooting to death of unarmed demonstrators by security forces. The violence seems to be characterised in four broad categories: organised militia activity in various locations; disorganised protest and violence; disproportionate use of force by the Kenya security forces; and retributive communal actions.

I have been called in by a NGO called "No Peace Without Justice" who themselves have been called in by the Kenya National Commission for Human Rights here in Nairobi to assist them in analysing the current post-election violence.

The aim is to go into the field and collect witness reports and to analyse these along with open source documents (newspapers, etc.). My task is to select a database and set it up for our requirements.

Unfortunately, we have been unable to find anything that is reasonably user friendly and which is focussed on the data patterns we are interested in.

We have a good idea of what tables, queries and forms we need, but there is no way that we can get a proper working system going in the time available with our current resources. I would be working (reluctantly) in Microsoft Access, but if there are people out there who could use our preferred Open Office Base and MySQL, so much the better.

Time scale? Well, people are going into the field next week, and the Commission has committed to a preliminary report in two months.

The bravery, committment and expertise of the Commission and their staff are humbling. They are a fiercly independent public body, and they find themselves between two fires: both the opposition and the government do not particularly like them, and this work - looking at the political violence of both sides of the divide - is not making them any friends. In addition, as a public body, they are having difficulties raising new funds for their work, as international donors have frozen contribution to state (public) bodies so things are really tight.

Are there any database experts in the Groklaw diaspora would be able to help us?

Yes, he knows it's asking a lot. If you can lend a hand, let me know and I'll forward your email to him. No pressure though. Only if you want to. To evaluate what he needs, here's what he says he envisons it as being like:

********************************

This would be a list of tables and their fields, and a description of user interactions. Anyway, here goes - all written as for MS Access:

Firstly, the data tables:

Table tblReport (for the initial input of the witness interview report)

Field ReportID - AutoNumber
Field Source - Number (identifying whether a witness or an open source document)
Field Reliability - Number (selecting from a table Low/Medium/High etc)
Field InterviewerID - Number (link to interviewer's Person record)
Field CollatorID - Number (link to collator's record)
Field DateReported - Date/Time (when the interview took place)
Field StartDate - Date/Time (start of period covered by the interview)
Field EndDate - Date/Time (end of period covered by the interview)
Field Transcript - Memo (full text of the interview)
Field DocumentRef - Number (link to the scanned document)

Table tblPerson (listing all persons or groups referred to)

Field PersonID - AutoNumber
Field PerTypID - Number (link to table of "person" types - individual, group, etc.)
Field FamilyName - Text
Field OtherNames - Text
Field EthnicityID - Number (link to ethnicity table)
Field SexID - Number (link to gender table)
Field MemberOf - Number (link to a relevant group in tblPerson)
Field Age - Number
Field CurrentLocation - Number (link to a location table)
Field CurrentAddress - Text
Field HomeLocation - Number (link to location table)
Field HomeAddress - Text

Table tblEvent (a record for each event extracted from the reports - multiple reports may refer to one event)

Field EventID - AutoNumber
Field TypeID - Number (link to table of event types)
Field ReliabilityID - Number (link to reliability table)
Field StartDate - Date/Time
Field EndDate - Date/Time
Field Location - Number (link to location table)
Field Comments - Memo

Table tblDocuments (index of all scanned documents)

Field DocumentID - AutoNumber
Field Reliability - Number (link to reliability table)
Field Reference - Text (title or description of document)
Field ScanURL - Text (pointer to the scan file)

Now for the lookup tables:

Table tlkpActs (list of acts a perpetrator may commit)

Field ActID - AutoNumber
Field ActName - Text

Field DistID - AutoNumber
Field ProvID - Number (link to administrative Province)
Field District - Text

Table tlkpDivision (list of administrative Divisions in Kenya)

Field DivID - AutoNumber
Field DistID - Number (link to administrative District)
Field Division - Text

Table tlkpEthnicity (list of ethnic groups)

Field EthID - AutoNumber
Field EthnicGroup - Text

Table tlkpEventType (list of types of events)

Field TypeID - AutoNumber
Field EventType - Text

Table tlkpLocation (list of locations (towns and villages) in Kenya)

Field LocID - AutoNumber
Field DivID - Number (link to administrative Division)
Field Location - Text

Table tlkpPersonType (list of "person" types)

Field PerTypID - AutoNumber
Field PersonType - Text

Table tlkpProvince (list of Provinces in Kenya)

Field ProvID - AutoNumber
Field Province - Text

Table tlkpSource (list of source types)

Field SourceID - AutoNumber
Field SourceType - Text

Table tlkpSubLocation (list of sub-locations) (this may not be necessary)

Field SubLID - AutoNumber
Field LocID - Number (link to location)
Field SubLocation - Text

Table tlkpWeapons (list of weapon type)

Field WeapID - AutoNumber
Field Weapon - Text

Now the link tables:

Table tlnkEventPerp (link between an event and a perpetrator "person")

Field EP-ID - AutoNumber
Field EventID - Number (link to an event record)
Field PersonID - Number (link to a person record)

Table tlnkEventVictim (link between an event and a victim "person")

Field EV-ID - AutoNumber
Field EventID - Number (link to an event record)
Field PersonID - Number (link to a person record)

Table tlnkEventDocs (link between an event and a document)

Field ED-ID - AutoNumber
Field EventID - Number (link to an event record)
Field DocID - Number (link to a document record)

Table tlnkEventReport (link between an event and a report)

Field ER-ID - AutoNumber
Field EventID - Number (link to an event record)
Field ReportID - Number (link to a report record)

Table tlnkEventWeapons (link between an event and a weapon)

Field EW-ID - AutoNumber
Field EventID - Number (link to an event record)
Field WeaponID - Number (link to a weapon type)

Table tlnkReportDocs (link between a report and a document)

Field RD-ID - AutoNumber
Field ReportID - Number (link to an event record)
Field DocumentID - Number (link to a person record)

Table tlnkRepRep (link between a report and another report)

Field RR-ID - AutoNumber
Field ReportID - Number (link to an report record)
Field OtherID - Number (link to a related report record)

Now for a functional description of the system:

The home page should display buttons to access each of the four primary data tables and the KNCHR logo.

The input form for each of the four primary tables should open in separate pages - simultaneously if required. They should allow for viewing or editing existing records, or the entry of new records. I don't believe that more fields are needed - at least initially - as the look-up tables can be loaded manually.

The Report page should have provision to create links between the currently open report and a document. A scrollable window should display existing linked documents, and a drop-down box will display all documents for addition to the linked set. This will create a new record in the tlnkReportDocs table. There needs to be provision for removing a link. There needs to be a button to bring up the document entry form if the required document turns out to be not present.

Similar requirements apply to all the other links: Event-Perpetrator Person, Event-Victim Person, Event-Report, Event-Document, Report-Document, Event-Weapons, Report-Report, and Person-Group (now there's a link table I missed out on).

Table tlnkPersonGroup (link between a person and a group "person")

Field PP-ID - AutoNumber
Field PersonID - Number (link to a person record)
Field GroupID - Number (link to a person record for a group)

All fields using look-up tables should display a drop-down box. The last entry in each look-up table will be "Other" for when the required entry is not present, with the operator noting the required entry on a clip-board and making a note in the text field for the record - unless someone can dream up a tidy way of adding the missing entry to the look-up table and flagging it for review.

I think that covers the data-entry side - the next episode covers the search and analysis phase.

Now for the search and analysis functions:

After discussions about analysis, we find there are some bits missing: An extra field (ActID) is required in tblEvent:

Table tblEvent (a record for each event extracted from the reports - multiple reports may refer to one event)

Field EventID - AutoNumber
Field TypeID - Number (link to table of event types)
Field ActID - Number (link to table of event actions)
Field ReliabilityID - Number (link to reliability table)
Field StartDate - Date/Time
Field EndDate - Date/Time
Field Location - Number (link to location table)
Field Comments - Memo

Two new look-up tables are needed:

Table tlkpComms (list of communications type)

Field CommsID - AutoNumber
Field Communication - Text

Table tlkpVehicles (list of vehicle type)

Field VehID - AutoNumber
Field Vehicle - Text

Three new links are required from the Event table:

Table tlnkIntevenor (link between an event and an intervening "person")

Field EV-ID - AutoNumber
Field EventID - Number (link to an event record)
Field PersonID - Number (link to a person record)

Table tlnkEventVehicle (link between an event and a vehicle)

Field EVh-ID - AutoNumber
Field EventID - Number (link to an event record)
Field VehicleID - Number (link to a vehicle record)

Table tlnkEventComms (link between an event and a document)

Field EC-ID - AutoNumber
Field EventID - Number (link to an event record)
Field CommsID - Number (link to a communications record)