Dabbling in Live Databases: MySQL - Page 5

Step 2: Create the structure for each table, in this case, billing and invoices. Consider billing -- we can describe its structure as follows:

billing: table description

Field Name	Field Type	Can be null?
ClientName	text	no
ClientEmail	text	no
Invoice	integer	no
Total	real	no
Paid	boolean	yes

Now, to actually create these structures in MySQL we need to concoct an SQL statement which defines the above:

CREATE TABLE billing
(ClientName CHAR(255) NOT NULL,
ClientEmail CHAR(255) NOT NULL,
Invoice INTEGER NOT NULL,
Total DECIMAL(6,2) NOT NULL,
Paid TINYINT NULL,
INDEX IDX_INVOICE (Invoice)
)

You have quite a bit of discretion in specifying your table's structure. We've made some decisions here, because MySQL offers many different types of fields, where in our table description we were using general terms. For instance, we've decided to use a CHAR field with a width of 255 characters for our text fields; there are several possible field types that would work here, and the differences are subtle. Generally, you want to select a field type that minimizes the amount of memory required to hold the possible data values -- for instance, if we had a field which only contained text less than 10 characters long, it would be wasteful to choose a CHAR(255) field type. The Column Types section in the MySQL reference manual details the differences between the many field types available.

Lastly, we created an index of the "Invoice" field -- indexing a field can speed up future queries, at the expense of a larger database, if those queries involve that field. Creating an index for your most queried fields may be a good idea, but these performance issues are matters that may require some experimentation, and tasty snack foods.

We're now ready to actually send this SQL statement to the MySQL server, thus creating the structure for billing. We'll use the interactive mysql client, which you can launch from mysql/bin/mysql in your MySQL installation directory. This client offers a command line environment:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 3.22.20

Type 'help' for help.

mysql>

Tell the client to use the Clients database:

mysql> \u Clients

And now enter the SQL statement to prepare:

mysql> create table billing (ClientName CHAR(255) NOT NULL,
ClientEmail CHAR(255) NOT NULL,Invoice INTEGER NOT NULL,
Total DECIMAL(6,2) NOT NULL,Paid TINYINT NULL
,INDEX IDX_INVOICE (Invoice))

Note that we typed the above statement in one line and the linewraps above were added for legibility on this web page. If you miss commas or parenthesis in the right places the MySQL server will reject the statement with an error, so pay careful attention to syntax. Also note the use of case -- specifically, it is the case of your table and field names that matters. Our table is called "billing", which is not the same as "Billing". The same applies to the field names. Hit Enter after you type the above and you'll be back at the command-line: now you tell the client to execute the statement:

mysql> \g
Query OK, 0 rows affected (0.06 sec)

If all goes well, MySQL will return a terse report and we can sleep well tonight knowing that the billing table structure is in place. Moving along, we lather, rinse, and repeat for invoices.

mysql> CREATE TABLE invoices 
(Invoice INTEGER NOT NULL,Hours DECIMAL(8,2) NOT NULL,
Rate DECIMAL(6,2) NOT NULL,INDEX IDX_INVOICE (Invoice))

mysql> \g
Query OK, 0 rows affected (0.06 sec)

A hearty pat on the back! That's two table structures in the can, and we can finally get to the meat of this here feast, importing the raw data into these tables.