Good methods for human-readable & human-maintained databases

Question

So this is the scenario:

• You have a bunch of data that needs to end up in SQL.
• It needs to entered by hand.
• It is not an "enter once and you're done" scenario: it will need to be modified and expanded by humans in an ongoing iterative way. Comments will be associated with entries. It is also useful for data entry people to be able to see related entries near each other.
• Different parts of data will need to be worked on simultaneously by different people.
• Some error checking also needs to happen. (Let the data entry people correct their mistakes before SQL picks them up)

I have one answer, which is how my project currently operates, but it occurred to me that maybe there are other awesome ways of doing this which don't have the problems of my current method.

Answer 1

If the constraints you're referring to can be enforced at the database level, free software like Quest Toad could allow them enter data directly into the db. It feels very much like using a spreadsheet when in grid view and displays an error when constraints are violated.

Alternatively, depending on what existing stack you have available, .Net grid views make it easy to slap together crud screens in little time.

Answer 2

Look at YAML as a way to represent the data as plain, human-readable, and human-fixable text.

A very simple program can parse the YAML, locate errors and (if there are no errors) update the database.

Answer 3

My answer is basically

• Have the data entry work in Prolog files (Prolog facts)
• Have multiple files, split up in a way that is sane for the data.
• Have a script that converts the Prolog facts to SQL.
• Have some tests in Prolog that validate the Prolog facts.

CONS of this approach:

• a little bit annoying to have to check across multiple files to see if an entry already exists, or has been moved etc.
• Writing Prolog, as simple as this is, is pretty scary for non-programmers (compared to say, filling out an Excel spreadsheet, or some guided process)
• maybe: Merging is tricky, or maybe my VCS is just not very smart (see Which SCM/VCS cope well with moving text between files?)

So this works pretty well, but maybe there is something better that I've never thought of!

Answer 4

These are some really basic requirements, and you probably have more issues than those stated. Nonetheless, you need a simple admin utility to enter data into your database.

A straight SQL query/update utility doesn't cut it because your team needs validation and such. You need multi-user access to the same data with transactional support. You also want to annotate your data entries and allow "related entries" to be viewed by your other users.

You need a database-maintenance application.

Consider using something like Django and it's built admin utilities. It might be more than you're expecting, but I imagine you have more needs in your future than what you've stated here.