How to create the best collaborative ad-hoc database ever

For a crisis-mapping project after the 25 April 2015 earthquake in Nepal, we needed a collaborative online database that is easy to set up and maintain. I found the product on obvibase.com to be the best existing solution. Much much better than the misuse of Google Spreadsheets that people usually engage in.

However, the Obvibase software is not free software, and it is not perfect either. So here's my list of improvements that I just submitted to them as feedback, after a month of frequent and in-depth usage of their database. If somebody wants to do so: a free software clone of obvibase.com plus the suggestions below would get us pretty close to the best collaborative ad-hoc database solution ever

• Alt+Arrow Left should work for "page back", but does not.
• The "Main menu → More actions … → Restore …" action's form should list the person who has done an edit in another column, and also what changes were done (showing the before and after version of the affected cell).
• For better privacy protection and because people are used to it already, sharing should work like in Google Docs (adding Google Accounts who get access, with different access levels per account). Otherwise, sharing the access link accidentally gives people read-write access, which cannot be revoked again.
• The "Page Up" and "Page Down" keys should work in the list view.
• Pressing the space bar on the first selectable column (with the double arrow) should select the record, adding a checkmark to the very first column.
• It should be possible do do simple styling of columns (background color, font color, bold font, italics font). Then, one can mark up one column as more important, and speed up visual navigation.
• Column titles should be formatted in bold and / or with a background color, to speed up visual navigation on the screen.
• There should be a way to create a new database in a new tab. If the menu link is a normal link that can be opened in a new tab, it will be enough.
• There should be a sharing mode where people with the link can add records and edit or delete their own records, but noth others. It will have to require login with a Google Account. Because, the general read-write access mode link can not be shared publicly to avoid access by destructive individuals who might delete every record, or overwrite it with spam.
• In the web-published, read-only version, long text cells should have an on-hover box showing the full text, as done for all other versions.
• It should be possible to mark individual columns as non-public in the column settings, which would exclude them form display in the public version that is read-only accessible to all web visitors. This would allow to collect public and confidential information (like, personally identifiable information) in the same database.
• Full export to CSV. There is currently no simple way how to export and re-import the full database incl. all nested records to one or some CSV files. It is however needed for making local backups. The problem is that exporting the main database table misses out records from any nested table, and exporting a nested table misses out records from any other nested table, and also any record from the main database table that does not have a record in the exported nested table.
• CSV exporting from nested tables should not repeat records from the main table. This happens currently, but redundancy is most always a bad idea. Instead, the nested record should refer to the ID of its parent record ("foreign key relation").
• SQLite3 export. Would be good for offline usage, as an interface to other tools, as a way to run complex SQL queries on the dataset, for comfortable backups (unlike CSV export, which misses out some data) etc..
• SQLite3 import, including reconciliation. To allow people "in the field" to contribute without Internet access, there should be an SQLite3 import feature. Concurrent edits would have to be reviewed before finishing the import.
• ODS export. Not that important, but would be good for having an alternative to the SQLite3 format export. The export would include multiple sheets, one for the main database and one for each nested table. Embedded scripts would be used for filtering when following a link from the main database table to the associated records in the nested table.
• When pressing Ctrl+V in a cell while it is not in edit mode, the current clipboard content should be entered into the cell. Currently, the Paste / Import window is shown, which is confusing because Ctrl+V is a clipboard operation in all applications, so people will use it intuitively (forgetting to go to "Edit mode" before). In the current way it functions, there are also two issues: iun addition to showing the Paste / Import window, currently "v" is inserted into the cell when pressing "Ctrl+V", and the focus in on the cell rather than in the text field in the Paste / Import window (which prevents the Esc key to be usable for closing that window).
• When trying to change the filter value for a text column by clicking on the header, the caret is initially at the beginning of the value. It would be more useful to position it at the end. More importantly, even though the caret is in the filter value text box, the "Pos1" and "End" keys navigate to the first and last menu entry instead. They should however move the cursor.
• To reset a text column to "no filter value", it should also be possible to click the column header, delete the filter value in the text field and then press the "Apply" button or Enter key. Because that is what people intuitively expect. Currently, a message will appear saying "No text to search for".
• Search by transliteration and equivalent Latin characters. To find the records one is looking for fast, there should not be a need to enter special characters. For example, when a record contains the name "Matjaž", it should be possible to find it via "Matjaz" and also by any transliteration of "Matjaž" to basic Latin characters.
• In case of a synchronization error, currently a message will appear that "some records have been rolled back". This means data loss, maybe for max. 30 s of editing. It is not due to concurrent edits, but rather due to intermittent connectivity problems. Instead of causing data loss, another solution should be possible. Like for example, asking if syncing should be tried again, and finally offering the rolled back data in CSV format to copy and add it again quickly.
• The database becomes hard to navigate if there are many columns, exceeding the screen width. For this quite frequent case, the database should allow several lines per record, distinguishing between vertically stacked columns by using different text formats.
• The textbox showing values for multiple-choice columns has too big paddings left and right, causing line breaks where there should not be any. Also, it should be always at least the same width as the column itself, for the same reason.
• When changing selectable values in the settings dialogue for multiple choice columns, it should be possible to delete existing values and create new ones, or to edit existing values (which will edit all records that use the old value accordingly, saving a lot of work).
• For powerusers, the filter field when clicking on column headers should allow regexp searches (and storing them in a "recent regexp searches" sub-menu). This can help with analysing columns that have comma-separated values, hierarchical text values and other constructions in them.
• URLs in all text columns should be automatically made into clickable hyperlinks (and automatically shown abbreviated when not in edit mode). Else, it's additional work to go into edit mode and select and copy the URL manually.
• Subtable records should be reachable with no or very little additional work. This is esp. important when storing contact information there, as it frequently contains hyperlinks. Proposal: When hovering over the "[…] records" entry that links to a subtable, an on-hover window should appear that shows the subtable records, incl. columns, hyperlinks etc..
• Currently, reverting to a prior version (via "Main menu → More actions → Restore …") will lead to a database that uses a new URL, without notifying the team about this. This means starting a new branch from the version one reverted to, without knowing, and without a way to merge changes into the main branch that the rest of the team is working on. This should be considered a bug.
• It would be good to be able to see who's editing the database at the same time. Just like in Google Docs and Google Spreadsheets, where there are user icons showing up in the top right then. It could be the same user icons as used for the Google accounts, since login happens via a Google account anyway.
• In the form to create a comment, it should be possible to press "Ctrl + Enter" instead of clicking "Save" with the mouse. It's faster. And like it's done in the Google Doc comments as well.
• For more comfortable and faster visual navigation in long tables, it should be possible to style each column in the column settings separately. A combination of several options for influencing the style would be available: font size, font weight, font color, overflow / line break behavior etc..