CREATE TABLE Orders (Order_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Customer_ID INT NOT NULL,
Product_ID INT NOT NULL,
Order_Date DATETIME NOT NULL,
Quantity INT, NOT NULL,
Amount_Due FLOAT(10,2) NOT NULL
Payment_Received ENUM(''Y'',''N''));As was explained earlier, you could put all these statements in a file and issue them in batch. This
allows you to create a copy of your schema to use in case of a system failure or if you wanted to
duplicate your work on another machine.
Remember that you can also create indexes at this time. Remember that an index can help boost your
database's performance during SELECT statements. Take a look at the tables you have and choose
some possible candidates to receive an index. MySQL automatically indexes the primary key in each
table. So the table that would probably need the index—the Ads table—already has one. The Ads table
is a good choice because people will be searching this table quite frequently. You could place an index
on the TEXT field to improve performance if you needed to. For now, you'll wait and see how your
database performs.Import Existing Data
Now that the schema is in place, you can begin populating your database. Your client may have data in an
Access or other type of database that he or she may want to port over into the new database. Populating
databases was extensively discussed on Day 8, "Populating the Database." Today, you'll briefly go over
importing data.
If you are currently using Access as your database and are making the move to MySQL, you are in luck.
Because MySQL is ODBC-compliant, you may be able to move your data quite easily if you have the
same exact structure in the MySQL database. You simply use the Export menu choice from the main
toolbar. It will walk you through exporting the data.
Another way to import data from an existing database is to use the Perl DBI. Write a simple script that
simply SELECTs all the data from one database and INSERTs it into the other.
One other way to bring data into your new database is with the LOAD DATA INFILE command. This
command is available through the MySQL monitor and allows you to bring formatted data in a file into
your database. To bring data from a comma-delimited text file, you would issue the following command:
LOAD DATA INFILE ''/home/olddata.txt''
INTO TABLE Customers IGNORE
FIELDS
TERMINATED BY '','';
This command would import the file named olddata.txt that resides in the home directory of the
MySQL server computer. It would load the data into the Customers table, ignoring any duplicate data.
The last part of the command describes how the file is delimited.Sometimes you may need to massage the data to fit into your new schema. This can be done with a
custom script or one of the tools available on the CD-ROM. Remember that MySQL stores dates
differently than most database. Take care when importing date information.Implementing Security
Before you can begin designing scripts that can access the database, you need to take a look at security.
How are you going to handle access to the database? To keep things simple, you will use MySQL's grant
tables to handle all your database access.
The New Customer script will access the database using a user_id that has only INSERT privileges
on the Customers table. After a customer has been added to the database, he or she will get a
username and password that will allow him or her to search the database, create ads, and edit his or
her existing record. The username will be his or her full name separated by an underscore, and his or
her password will be his or her email address. All our customers will have INSERT privileges on the Ads
table, UPDATE privileges for some of the columns in the Customers table, and SELECT privileges on all