A Common Sense Approach to Normalization of Database Tables

Solution Preview

To create a table to hold records with these columns in them is a simple task. Making it into a set of tables that adheres to the Third Normal Form requires some thought, but fortunately it's really more about common sense than anything else.

The CREATE TABLE command is used in SQL to create tables. (The INSERT command is how you put records into that table.) The syntax of the CREATE command looks like this:

CREATE TABLE customer_order (
Order_id INT,
Order_date DATE,
Customer_id INT,
Customer_name VARCHAR(60),
Customer_address VARCHAR(60),
Customer_city VARCHAR(60),
Customer_state VARCHAR(60),
Item_id VARCHAR(20),
Item_description VARCHAR(120),
Item_qty INT,
Item_price DOUBLE,
Item_total_price DOUBLE,
Order_total_price DOUBLE
);

The name of each column in the table is listed, and a data type for each column is specified. Note that the specifics of creating the table will vary depending on which implementation of SQL or MySql you are using.

One of the things you will need to ensure at this point, however, is that you can actually use the table for something. You want to be able to put records in there. But you also want to be able to find them to look at them, too, right? And presumably you'll want to be able to answer questions that come up, like: What customer ordered what items; How many times a specific customer placed an order; When did a customer place a specific order? That's where the design of the database table(s) can become more important.

Before we begin massaging the data into a better form, let's look at one more thing: The Order_id. How does an order get an id? Id is an abbreviation for identification, of course, so it implies that you will be able to identify an order, given its id. And since each order is different, you'd probably want a different id for each order. SQL provides a simple mechanism to do that. It's an attribute called "AUTO_INCREMENT". When you have this feature turned on for a given table's key, each time you add a new record to the table, a unique key is automatically given to the field. In this case, the Order_id, which is an INTeger field, could be given the AUTO_INCREMENT attribute. Then, whenever a record is added to what I've called the "customer_order" table, the Order_id will be the next highest integer number. So, if there are five records, the sixth record will have an Order_id = 6.

Now suppose you want to find all the orders that a given customer has placed. He calls up, tells you his name is Quincy Jones, and begins telling you about his order that's gotten screwed up. You need to find Quincy Jones' order in a hurry. You can query the database table, asking it to: SELECT * from customer_order WHERE name ="Quincy Jones"; Maybe it pulls up the right order, maybe it pulls up a bunch of orders, and maybe it doesn't pull up any orders. That always depends on the quality of your data, which, in turn, depends on the quality of your database design and the manner in which the data is entered. That's one of the things Normalization is supposed to help with.

It's entirely possible that each time Quincy Jones called his order in, he used a slightly different name (or address). But because all of your data is stored in one table, it's harder to determine if that's the case. And it could have simply been entered incorrectly, too. One order may have him listed as "Mr. Quincy Jones", another might have him listed as "Mr. Q. Jones", and so on. The same is true with his address. One of the ways you can try to prevent that from happening is to separate the customer data from the order data. If the customer data is stored separately, you ...