http://qs321.pair.com?node_id=205968


in reply to Choosing a database for a new project

Go with MySQL and DBI. Just get some help from somebody out there, to set up a MySQL user for you, and create an empty database for you. Let him make sure you have enough user rights to create tables and delete all tables you create. Just because you need to learn to use MySQL, there's no need to start with learning how to admin MySQL!

There's no need for you to start up MySQL. If you have it at your disposal, it will already be running. All you have to do, is connect to it, through DBI.

Backing up the database is easy enough using mysqldump, assuming you have command line access:

mysqldump --add-drop-table -u user -p database >database.sql
"user" is your user name, "database" the database name, "database.sql" the text file with the dump that will be created, which is a text file with nothing but SQL commands, enough to recreate the whole contents. This command line will prompt you for the password. You can type the password right after the "-p" (no space), but that way it will stay in your shell's command line history, which is a bit of a security risk. (MySQL takes care to overwrite the password in the command line that appears in "ps".)

Restoring the database can be as simple as:

mysql -u user -p database <database.sql
It's also an easy way to make a possibly modified copy of an existing database. It's also a way to learn SQL, in a hands-on way, because you know that this is valid SQL.

For administering the database, like creating or modifying tables, I would recommend some database client software, like mysqltool in Perl. It works very well as a plain CGI script. All you have to do is make the stuff under "htdocs" visible in your web space, if CGI scripts can run anywhere, and modify index.cgi so that it points to your "mysqltool.conf" file with an absolute path. (Why this file is under "htdocs", I'm not sure, but it sure doesn't belong there. So move it out of your web space first.)

A colleague of mine, who's deeply into PHP, simply loves phpMyAdmin, which is similar and written in PHP. It's just, I can't get it to work. I've seen him use it. It looks impressive. Both are free. Perhaps your site already has one of those installed, which you could use, so ask for it. If you like neither, there's more at MySQL's site.

As an aside, may I point you to the SET and ENUM types of field, for your classification. ENUM allows you to store one of just a few possibilities ("at most" some 65000), for example "CLOUDY" for wheater. They're stored in the table as integers, but allow for easier access. SET is similar, it uses a bitmap for the field with at most 64 flags, which you can combine in any way you like.