News Archive
PhpRiot Newsletter
Your Email Address:

More information

Making of UN database

Note: This article was originally published at Planet PHP on 16 July 2010.
Planet PHP

September last year I met up with an old room mate. She told me about a student project calledAMaking Commitments Matter (MCM). They had created a database for UN resolutions. Even though the team was quite large, spanning all across Europe, they didn't have anyone in the group who had a sufficient level of IT know how to bring it all online. Once again it shows that Universities usually are unable to bring together different departments. So I had no choice but to help them out!

Step one defining a database schemaAfor the excel files they currently used to store the information. Contrary to the document systems of the various UN organizations which scatter the various PDF's across different sites, the aim ofAresolutionfinder.orgA(previously called to bring all information to one database. Furthermore would also show the entire history of documents and each of the clauses there in. This again is a huge leap forward, since right now its essentially impossible to determine if a given document or clause is actually the most current version and how things changed over time. Especially this history was a big challenge in the definition of the schema.

Aside from this the two years that MCM spend interviewing key stakeholders like NGO's, government officials and researchers have lead to a fairly complex set of additional attributes that also need to be stored for each document and clause.AThe fact that each clause is stored separately is also a key improvement since many documents contain decisions on many different topics. With this database it will be possible to search for just the clauses for a given area as both documents and individual clauses are tagged.

Using the admin generator of the symfony PHP framework I started building up an interface to the database in order to be able to get feedback from the MCM team. By way of a call for help on my blog Dennis Riedel, living in Barcelona, joined the effort and began working on an import tool to move the data from the excel files into the database schema. At a project meeting in Helsinki in early December 2009, we used this prototype to define the final specifications for the database.

The goal was to have a working frontend ready for theAconferenceAMCM was organizing on the topic ofA"UN information management" end of May 2010. No small undertaking for a small team of two guys in their spare time, neither of whom being real experts in frontend development. So I turned to Liip for help. At Liip we have budgets to support technically or socially relevant efforts. The process is basically pitching the idea to the entire team to get development resources. In the end was allocated 12 man days development as well as 1 man day for server administration and a server to run the application on!

Christoph and Jordi both stepped up on short notice to help me build up a frontend that actually exceeded by far the hopes of the MCM team. The site leverages Apache Solr for the search with features boolean operators and facette filtering. We display the documents, clauses as well as their relation and history. Its also possible to register to store bookmarks to documents and clauses as well as comment them. Most amazingly we still have a few days of development left that we will use to further optimize the UX on the search interface. I should also thank Stefan Sicher who delivered a very sleek and clean design just days before the conference.

Obviously there is still lots left to do. As mentioned before we want to further improve the UX on the search. We are also considering adding alternative search approaches, for example some people might want to search chronologically, others organization etc. We also want to add data mining capabilities so that we can automatically import voting records and maybe eventually even entire documents. Supporting more languages both for the interface and the content also is high on our list. We might also provide the content via an RDF database to enable others to come up with queries of their own. The team is obviously full of ideas and is actively looking for companies or developers willing to donate resources. TheAsource codeAof the entire application is of course open source: patches welcome!

Here is an "/

Truncated by Planet PHP, read more at the original (another 1414 bytes)