How To Build and Share Databases On the Web
- — 19 May, 2009 05:54
If you are part of a business, sooner or later you want to be able to collaborate on a database with a colleague or customer. In the past, the easiest way to share a small database was to create a spreadsheet and email it to your collaborators. While this isn't the best method, it has withstood more sophisticated competition for several reasons:
First, databases are still tricky for some people to understand, and spreadsheets are more obvious. A spreadsheet is usually contained on a single screen, so it is easy for someone to figure out its logic, and the grid of rows and columns is also easier than the typical relational database with a series of tables. Tables are more than many people -- especially those who don't have a lot of formal IT training -- want to deal with. Second, the database collaboration tools are tough to learn and use. Look at how many people still use Lotus Notes for email and not much else. And since most of us are comfortable with email, using it as the transportation system isn't all that taxing. Until the day comes when three people are working on the same spreadsheet or database and make conflicting changes.
Third, building the right kinds of collaborative applications requires some skill and understanding of how and what kinds of data are shared. How many people are going to be adding/changing records to your database? How many just want to do queries and reports? And how do you prevent conflicting concurrent updates?
So what are your choices, if you don't want to email spreadsheets around? You have two basic methods. First is to use one of the many Internet-compatible database programs. Alpha Five has some readily available Web tools and its standard edition is US$200, or you can purchase the Filemaker Server version for $1000. There are plenty of others, but these two are the easiest to setup and use across the Internet.
For both products, you will need to make use of a hosted Windows or Mac machine at your Internet provider or else have some other means of connecting the machine that you run your database on to the Internet. (PointInSpace is one provider that offers a Filemaker Pro hosting account for $40 a month, and ZebraHost offers Alpha Five hosting starting at $30 a month. Here is a link to a series of video tutorials on how to do this with Alpha Five.) You will also have to learn their programming language and be comfortable with how they produce reports. This method has the advantage that you are running a real database program and has the most flexibility. But it has a high entry cost in terms of skills and may be more than you want to deal with.
If the skills required on either of these are daunting, you'll probably end up with the second method, which is to use one of the Web services companies that are geared towards sharing spreadsheets and tabular data. While Google Docs does allow people to collaborate on a file-level, what we really want is something a bit more sophisticated and that can recognize the individual records. There are at least four services that I have found that do this: o TrackVia, $10 a month per usero DabbleDB, $8 a month per user o Intuit's QuickBase $250 a month for 10 userso Blist.com, $10 per month per user plus $30 per month for one admin account
With all of these, you can create an account and upload your spreadsheet in about five minutes. If your first line in the spreadsheet contains your field names, you are just about done. You can easily sort any column quickly by clicking on the arrow icons. You can quickly locate duplicate records, create a mail merge template and forms for your Web site, all with just a couple of clicks of the mouse.
Custom reports are simple, and what's more, they can be distributed via email to your collaborators on a set schedule. Adding different collaborators with various discrete permissions is very straightforward, and in about 30 minutes you can have a project setup and working with your team. The good news is that you don't need to be a programmer, or even act like one. You also don't need to pay for hosting fees (that is included as part of the service), and all you need is your Web browser to access your data.
To get a feel for what is involved, all four services allow you can start off with free accounts. In TrackVia's case, you have 14 days before you have to give them your credit card, and in dabbleDB's case, you can use the free account forever, provided you don't mind that your data will be in public view. With blist, you can use their consumer-grade account for free forever, but it limits you to databases smaller than 100 MB. QuickBase gives you a 30-day free trial and the ability to share with up to 10 people, so if you are looking for something to support just a few collaborators that will be too pricey.
Each of the four services differs in terms of how they can import data into your database, what kinds of reports are available, and how many different databases and storage is allowed per account. That is why it is best to try each out and see what makes the most sense for your work style and must-have features, and how far you can really get without knowing any real HTML.
Let's take a look at how you import your data in TrackVia as an example. When you create a new database, you are presented with three choices.
You can upload a spreadsheet, and if you do you click on the "see tips" link and you will see a list of caveats and instructions on how to do this. If you have your data in a CSV file, you first have to import it into Excel before getting the data to TrackVia. Some of the other services allow you to import CSVs directly, or cut and paste data from your desktop or a Web site. You also have the option of starting with a sample database template, and TrackVia offers more than a dozen different ones. Let's say you pick the Contacts template. Then you can either add each contact record one at a time using a standard Web form, or use the Import tool and bring in that spreadsheet.
TrackVia also has an interesting option to monitor a special email inbox that will collect new database records and post the information automatically. If you go to the Email Collecting tool, you will see a series of screens, including the one below that you can use to set this feature up. You can have your e-mails create new records, or update existing records.
Good luck with sharing your databases and spreadsheets!