Normalizing the Database
You decide to create a unique key in the Customers table and name it Customer_ID. A person can only
have one membership, so the key must be unique. You add unique keys to all the tables and name them
after the table they identify—Ad_ID, Order_ID, and Product_ID.
The next thing you normally would do is perform some normalization on the tables. Because these
tables are pretty normalized, you'll leave them alone. The only changes you'll make is to change the
Products and Customer columns in the Orders table to Customer_ID and Product_ID.
Note For the sake of simplicity, these tables are not truly normalized. They could be
broken down further, that is, a Manufacturers table and probably an
OrderMasters and OrderDetails table as well.
After that process is finished, the next step is to define the relationships that exist between the tables.
You can establish a one-to-many relationship between the Customers table and the Ads table right
away. Add a Customer_ID column to the Ads table. There is also a one-to-many relationship between
the Customers and Orders tables and the Products and Orders tables. Because you already have
those columns in the Orders table, you don't need to add them.
Well, it looks like you have ironed out all the wrinkles, so now you can create a model of the database.
To do this, you use a modeling tool. Figure 21.2 shows a Microsoft Access representation of the
database and the relationships.
Figure 21.2 The Meet_A_Geek database model.
If you are working in a team environment, it's always a good idea to have the team look at your model.
Sometimes someone else can spot something you may have missed. Share your design, let other
people critique your work. It can only make your design better. After you are happy with your design, it
is time to create the database.Building the Database
To create your database, you need to have the mysqld daemon up and running, and you also need to have
the CREATE privilege. If these two things are good to go, you can create your database in one of two ways.
You can do this by using the mysqladmin command or the SQL CREATE DATABASE statement. It's your
choice.
To create a database with the mysqladmin command, type the following from the command line:
%> bin/mysqladmin –p CREATE DATABASE Meet_A_Geek
To create the database in the MySQL monitor using an SQL statement, use the following command:
mysql> CREATE DATABASE Meet_A_Geek;
Now that you have created the database, it is time to create the columns. There are several ways you
can do this. The recommended way is to create a text file containing the DDL statements and run them
through the mysql program in batch. That way, you will always have a copy of the schema, just in case
your database ever crashes and you need to start from scratch. If you do not choose to do it this way,
you can type the SQL statements interactively with the MySQL monitor. You learned about this on Day
4, "Creating Your First Database." To refresh your memory, the command to create the Orders table
would look like the following: