Excel to mysql

Getting to First Database

You tell your buddy that you need to track information and that you think you need a database, and suddenly you're greeted with terms like SQL, ODBC, and ADO. Your head starts to spin, but you go on and mention that you need to let people enter or view the data through a browser, and he adds Oracle and JDBC. Barely coherent, you stammer something about inexpensive and you hear open-source, CGI, and MySQL. Stop!

For many PC Magazine readers, these terms are common parlance in their everyday life. But for others, the concept of a database is still intimidating. Databases don't need to be scary or expensive. Nor do they require a lot of hardware and support. Current versions of the old standbys FileMaker Pro, Microsoft Access, and Paradox are easier to use than you may expect. And there's a new category of user-friendly databases that are Web-based. User-friendly? They're almost user-obsequious.

We've been enamored with Web-based databases since we came across early entrants Flashbase and Bitlocker nearly two years ago. Both offered impressive sets of features right out of the gate. But both are now gone, and most of the surviving services are still small start-ups. We are, however, encouraged that the personal finance giant Intuit threw its weight behind the category last year when it introduced QuickBase. Still, if you go the Web-based route, make sure you download copies of your data frequently in case the company disappears.

Anatomy of a Database

There are basically two metaphors that can help users understand exactly what a database is. A database can be thought of as a stack of index cards in which each card, or screenful of fill-in-the-blank information, is a data record. The screen, or card, contains all the information about some person, place, thing, or event. Each fill-in is a field or item of information about something in that particular record. The second metaphor is a table in which each row is a record and each column a field. Consider a table where you have column headings such as a last name and first name followed by street address and so forth. Below those column heads, you have a set of names and addresses.

Database-Dependent

Once people start creating databases, they usually find they can't live without them. But the uses are likely to be much more creative than those envisioned by vendors of desktop database packages just a few years ago. Microsoft Access 97 came equipped with templates for your wine inventory, recipes, and book collection. Anyone needing to computerize such matters clearly doesn't get out much. And those uses can be satisfied by spreadsheets like Excel and Lotus 1-2-3, which are cheaper than the typical database package.

Vendors are thinking along different lines now and are being shown the way by creative customers. Connectivity is important. Companies use databases to keep track of customers and their own salespeople. Houses of worship sign up new members on the Web and hold online bake sales where the cookies aren't baked until they've been sold. (Sounds a bit like Dell, doesn't it?) And the board of a statewide environmental group shares its membership and financial data online.

Applications such as these are being incorporated into the programs' and services' templates. QuickBase has prefabricated examples ranging from a data manager that organizes an event's sleeping arrangements to an online discussion forum. Another service, eCriteria, has an entire accounting database system online, including bill payment, payroll, and purchasing.

The Pros and Cons

The Web-based services have their limitations. Currently, they handle the type of database that contains only one table. Access and Paradox, on the other hand, can store separate but linked tables within the same database, such as customer contact data and customer orders.

To use a Web-based service, you simply go to the Web site and register. The services aren't really meant for huge amounts of data, though databases of 5MB to 10MB are routine and can store thousands of address records or catalog items.

Using desktop packages requires a purchase. But check. The "professional" version of Microsoft Office includes Microsoft Access, and Corel WordPerfect Suite Professional comes with Paradox. If you have a lot of data, a desktop package might cost less than a year's worth of monthly Web fees. But if you want users to get to the database using a browser, you'll have to set up a Web server and make it securely accessible to others.

The Continuum

If you must share your database with users outside your local area network, and the information is basic and can be contained within single tables, Web-based services make a lot of sense. They keep prying eyes out of the rest of your system and network, and you don't have to worry about maintaining a server.

Web-based database services also make sense if you are capturing data from a Web page in the first place. For instance, you can use a service to let people register for a newsletter through your site. The sites use passwords and SSL protection to guard your data. But remember, as with desktop databases, security isn't worth much if a member of your sales staff downloads the entire online customer file before leaving to take a job with a competitor. So set up your rights and permissions carefully.

You might also look at Web solutions built onto desktop products. FileMaker Pro's Web Companion, for instance, is a plug-in that uses its own common gateway interface (CGI) to request data from a FileMaker database in XML or to add to one using Sun Microsystems' JDBC (Java Data Base Connectivity). Paradox, too, comes with a Web form designer and JDBC, and Access can expose databases to browsers using Data Access Pages.

If you are doing complicated analyses, you might need to base your system on a desktop package. Desktop packages can do analyses that Web services can't and can pass data to other programsstatistical workhorses like SAS, SPSS, and Statistica.

Once you have chosen a path, take a close look at specific features and try all the interfaces. You and your colleagues will get more out of a database if you and they are truly comfortable with the metaphor of its data-entry and query tools.