What kind of database software am I looking for?

December 5, 2012 8:31 AM

Database question: what front-end tool is best for high-volume manual editing of content?

Background: I'm building a web/mobile flashcard app, or rather I've hired others to build it for me. Each "card" has a lot of content, let's say 15-20 fields, most of which are free text and the others are labels/categories/flags/etc.

The content is written mostly by others, in batches, then imported and extensively edited by me.

Currently I'm managing it all with a big Excel file, and my developer has made a web panel for me where I upload the latest version of this Excel file and he's got scripts that pull it into the SQL table that the app runs off of.

So far so good. But as you have probably guessed by now, it's getting to be too much to handle in Excel, even on a high end machine -- I'm at well over 3,000 records now and I'd really like to add another 5-10 internal fields per card, e.g. to keep a record of content that I've rejected and notes on why. It's not just the speed of search/sort operations, it's also the amount of content that can fit on a screen.

Obviously I need to switch to a better tool. But I have no idea exactly what I'm looking for or even what it's called. When I google "database frontend" I get stuff like PHPmySQL, which I've tried, and it seems much more like a GUI for managing the database structure than a practical way to edit content.

What I really want is something where I can add and edit records, scroll through, sort, filter and search them, just as easily as in Excel, or hopefully even more easily. Ideally I'd be able to either click on any particular field in the table and just edit it in place, like Excel, or alternatively open the whole record in a pop-up window with form elements (text boxes, check boxes, drop down menus) for each field that I can rapidly tab through and edit, then save and drop quickly back to the list view. And then export from this local database (or better yet, some defined subset of it) to a file I can upload to be pulled into the live database.

Any suggestions?

posted by pete_22 to Computers & Internet (14 answers total)

I have developed some applications to do stuff like this, were I intending to talk to you about it I would have some initial questions I'd ask by email.

What does web/mobile mean? 2 different apps and 2 different databases or just a website that can be accessed by mobile phones?

When you say high volume editing do you mean anything other than editing lots of records one at a time (though with the ability to choose subsets of records to work on).

What is the live database? MySQL? something else? What is your local database?

What are you actually looking to edit, the externally sourced content or the database entries?

Do you have Microsoft Access? It is fairly hideous (IMO) but it does lend itself to this kind of ad-hoc application development.
posted by epo at 9:08 AM on December 5, 2012

Response by poster: Thanks for the response, but unless I've badly misunderstood the situation, I don't think most of these questions are relevant. Maybe I was over-explaining in the question.

I think we can set aside the app or where the content comes from, and just look at it this way: I have a big table in Excel with several thousand records and 15-20 fields. I frequently need to make hundreds of edits to it, across a wide range of records and fields, and deliver the updated version to others as a CSV or equivalent. These edits usually include adding new records, so the table is growing over time. And let's say I'm entering these new records by hand, so I don't need any special way to import them.

Other than Excel, what are my other options for doing this? I realize I could set up a local database server and get someone to write me a custom application for editing it, which sounds like what you're referring to... but it's hard to believe there aren't other off-the-shelf products for this purpose.

I do have Access but I've heard a lot of bad things about it (and it sounds like you somewhat agree). What else is out there?
posted by pete_22 at 9:42 AM on December 5, 2012

Response by poster: When you say high volume editing do you mean anything other than editing lots of records one at a time (though with the ability to choose subsets of records to work on)

I don't think I mean anything other than that, but you're right that the ability to choose subsets of records is important. For example, I frequently use multi-level sorts of the whole table to isolate 50-100 records with some particular characteristic that means I might need to edit them, and then scroll through those one at a time.

I rarely do large-scale Find and Replace operations but the ability to do that occasionally is also a helpful feature of Excel. I could live without it though.
posted by pete_22 at 9:50 AM on December 5, 2012

Why on earth are you using Excel like a database when you already have a database involved (for the app)?

I think you need to talk to your web person and have him or her create a web-based GUI for you to search for cards and then edit them in a browser. Store all cards in the same database that the web app uses, and add a flag for whether or not the card is live so the app only sees the cards you want it to see. The interface can look like whatever you want it to look like, since the web person will be coding the forms according to your specifications.

You will want to be very clear with the developer about what kinds of information and metadata you want to store and search on, as that information will guide the changes s/he makes to the database. You should expect to have to refactor it at some point in the future anyway if your app takes off, but poor choices now might make that moment come sooner than you'd like.

Does that make sense?
posted by jsturgill at 10:11 AM on December 5, 2012

Best answer: From your description it sounds like you use quite a few of the features of Excel for what you're doing. I don't think you're going to find a generalized application that gives you all of the features from Excel that you want and leaves off the ones you don't want.

I also don't see how to get what you're talking about without custom coding or a substantial amount of customization of an existing application: when you talk about how it would "open the whole record in a pop-up window with form elements (text boxes, check boxes, drop down menus) for each field" how would the application know whether you wanted to edit each particular field with a text box, a set of check boxes, or a drop down menu, and what the choices in the drop-down menu should be?

So, I would agree with jsturgill that this is probably something you or your developer will have to build as a custom application. There are things like Microsoft Infopath that attempt to be a general platform for this sort of application but the requirements you describe - you being the only user and the inputs and outputs already being handled via manual processes - make me think that sort of thing would be overkill and it would be best written in whatever programming language the developer is familiar with.

Alternatively, maybe try a lighter-weight open-source spreadsheet application, or maybe a very old version of Excel like Excel 97 or something, that would run faster on a modern computer? A caveat might be that an older spreadsheet application might not be able to use all of the RAM on a modern computer.
posted by XMLicious at 10:23 AM on December 5, 2012

Sound like you want a tool like Access or Filemaker where you can define and store data and create frontends to help you with entering/editing/etc.
posted by wongcorgi at 11:22 AM on December 5, 2012

Just throwing this out there -- you can import the excel spreadsheet into google docs and create a form for it pretty trivially. Might be something to play around with and see if it's better for you than excel/access...
posted by empath at 11:43 AM on December 5, 2012

Excel is for number crunching and is really not the right tool for the job. It wouldn't take too much time to generate a front-end editor in Access for the data, though you'd then have the fun of exporting it back out again. As empath mentioned, Google Docs might be a pretty good intermediate.

Though this is really sounding more and more like replicating a content management system, of which there are hundreds. And most of them look (and act) like a database exploded onto the screen, since that's inherently what they are.

Time spent fighting your tools is time wasted, investments now will save you over the life of the project.
posted by fifteen schnitzengruben is my limit at 2:54 PM on December 5, 2012

I have to disagree with your first statement - the spreadsheet interface at this point in history is a pretty sophisticated and vetted general tool for editing any array of data and even though I, as an experienced software developer, can easily write regexes to reformat data or whip up an application that uses a datagrid-type control or other form controls to edit it, it often still makes more sense to paste stuff into a spreadsheet and swap a few columns and rows around or use the fill tool or some other standardized, well-tested functionality.

I'm not saying it's necessarily the best tool for the job in the OP's case—I don't think we have enough details to say, at this point—but it well might be in this particular case. This sounds like the sort of thing where he might start developing an application and keep finding features from the spreadsheet interface he needs anyways and repeatedly having to pay someone to re-implement and QA those features in the app, all just for him as the sole user, and still in the end not be satisfied with the result.
posted by XMLicious at 3:29 PM on December 5, 2012

Response by poster: Why on earth are you using Excel like a database when you already have a database involved (for the app)?

I think you need to talk to your web person and have him or her create a web-based GUI for you to search for cards and then edit them in a browser.

Yes, we've already done that, and I still use Excel instead because it's much, much faster. For example: suppose I need to add a "difficulty rating" from 1-5 to each of 500 cards. In Excel, I sort down to those 500, type a number in the first cell, hit return and I'm already on the next row, type another number and hit return, etc. It takes literally 2-3 seconds per word, if that. I can do all 500 in an hour with time to spare, never even touching the mouse.

With the web panel, there aren't nearly as many keyboard shortcuts so I'm clicking all the time -- open a word, tab to the field or click on it, type a number, save and back to the list -- and the slower response time adds up, so now you're talking 20 seconds per word and it's impossible to get into the same "rhythm," so it's literally an all day project.

From your description it sounds like you use quite a few of the features of Excel for what you're doing. I don't think you're going to find a generalized application that gives you all of the features from Excel that you want and leaves off the ones you don't want.

Yes, that may be true. I guess in a way I'm asking for a narrow, specialized kind of spreadsheet, optimized for fast data entry and without all the other features (graphing etc) that slow down Excel.

But if that doesn't exist, I'm surprised, because I think I'm hardly the first person to have this kind of problem, right?
posted by pete_22 at 5:07 AM on December 6, 2012

Best answer: No, you aren't the first person to have this kind of problem, which is why there are programming components called "datagrids" that are designed to be a bit like a pre-built embedded spreadsheet starting from the most minimal set of features that a developer can build up an application around. But entirely because the need is usually for a specialized spreadsheet, this sort of application is usually custom-built.

(Or perhaps created by customizing a generalized desktop spreadsheet application, in some cases; Microsoft has heavily invested in Visual Basic for Applications as well as other programming interfaces over the years to allow programmers to add custom functionality around the Office products. It's one possible way your developer is processing the Excel file when you upload it.)

How about this: since your developer obviously already has some code that is rigged up to handle your spreadsheet, why not ask him or her to write a little application that lets you do a search of the full file and returns the rows of search results as another, smaller Excel file or .csv file that can be re-imported into Excel? That way you can work on smaller files that don't drag your computer down as much and only need to worry about merging your work back into the full file (or some sort of intermediate-stage database... another small tool could handle that.)

Which gives me another thought: especially because of its integration with other Office products, it's possible to accidentally paste in things that look like normal words or numbers but are actually a complicated blob of code that Excel decided to "help you" by including, which can massively slow down Excel. Just in case that's part of your problem I would try saving a copy as .csv (which should strip out any crap like that) and completely closing Excel and re-opening the file in it, to see if it performs any faster.
posted by XMLicious at 5:43 AM on December 6, 2012

Best answer: With the web panel, there aren't nearly as many keyboard shortcuts so I'm clicking all the time -- open a word, tab to the field or click on it, type a number, save and back to the list -- and the slower response time adds up, so now you're talking 20 seconds per word and it's impossible to get into the same "rhythm," so it's literally an all day project.

You can add keyboard shortcuts to a web app. JavaScript is totally a thing! And since you're making the interface from scratch, the interface is as flexible as your imagination. You are not locked into any particular workflow.

For example, you could pair selection criteria (all cards without a difficulty rating, say, or cards 200-700, or all cards added since... whenever) with an explicit list of the fields on those cards that will be edited in the next step (the difficulty rating field, in your example).

The actual entry forms would be dynamically generated, so that only the defined fields are editable, with shortcuts in place for navigating to the next and previous entries and other common operations. Assuming a low-latency connection to the database, you could have a very similar workflow that is just as fast with much more complicated things happening behind the scenes (tracking changes, for example, and sanity checks on the information before commiting it to the database).

This sort of app could also be an asset going forward in a way that the excel spreadsheets are not: at some point if the card app takes off, you're likely going to want to pay someone to do the data entry for you. This seems like a much more flexible approach for that, as it would easily allow for restrictions on the fields that certain users can edit, easily allow for your approval before making the changes live, does not require any special software for the data entry, and so on.

Simple data entry like this can be outsourced very cheaply, so you may already be at a point where it no longer makes sense for you to do it yourself. Even in America or other first-world countries there are plenty of people who wouldn't mind $8 or $10 an hour for this sort of work and who could match your speed or possibly do it faster.

A web app is probably the most portable, flexible thing there is, and for simple CRUD stuff like this there are a lot of tools that exist to make development fast and secure.

Just something for you to consider.
posted by jsturgill at 9:35 AM on December 6, 2012

And since you're making the interface from scratch, the interface is as flexible as your imagination.

I would totally agree but add the caveat "as flexible as your imagination and your budget."

Since the OP isn't the one writing the code and has to pay someone else to do it, I would make sure that there's at least potential revenue in your business plan that would justify it.

Although it's much easier to build a sophisticated, cross-browser web application today than it was in the past, doing it well still requires some experience and planning and it is entirely possible to make an application that's a morass of spaghetti code which becomes a headache to support, maintain, and expand.

So I would say if you're going to hire someone to do a custom application, go all in: don't dribble money into it one small modification at a time, which can just become a black hole. Work with your developer to come up with detailed requirements and expectations for how the application will be tested, how bugs will be handled, and how frequently the app's code will be revisited for "tune up" maintenance, and discuss how it might be enhanced in the future if people other than yourself end up using it so those considerations can be included when designing the software's architecture.

On the other hand, if you've been considering trying to acquire more coding expertise yourself and have the time to invest, this could be a great learning opportunity. As jsturgill points out, this sort of CRUD database-backed application is relatively simple and is probably the most common type of software application. As both the developer and user you would have a good sense of which features you really need to implement and what you can "live with" and figure out how to build in code later, and take a more gradual approach than might be prudent if you're paying someone else by the hour to write it and deal with things that go wrong.
posted by XMLicious at 12:56 PM on December 6, 2012

Access has an unfairly tarnished reputation because it's not great at handling large numbers of concurrent users and because a lot of amateurs use it to create frankly horrible solutions. That said I know a few people who do this kind of data manipulation for a living and you can take their MS Access away when you pry it from their cold, dead hands.

To take your example of adding a "difficulty rating", in access you would switch to table design view, add a new column, add a default value, press save and you are done.

It is a great tool, just don't expect to master it in a weekend.
posted by Lanark at 4:57 PM on December 6, 2012