MySQL/PHP Database Applications

John Wiley & Sons

Chapter One

IN THIS CHAPTER

* Identifying the problems that led to the creation of the relational database

* Learning the normalization process

* Examining advanced database concepts

The bulk of this chapter is for those of you who have made it to the early twenty-first century without working with relational databases. If you're a seasoned database pro, having worked with Oracle, Sybase, or even something like Microsoft Access or Paradox, you may want to skip this little lesson on database theory. However, we do suggest that you look at the final section of this chapter, where we discuss some of MySQL's weirder points. MySQL's implementation of SQL is incomplete, so it might not support something you want to use.

Why Use a Relational Database?

If you're still here and are ready to read with rapt attention about database theory and the wonders of normalization, you probably don't know much about the history of the relational database. You may not even care. For that reason, I'll keep this very brief. Dr. E. F. Codd was a research scientist at IBM in the 1960s. A mathematician by training, he was unhappy with the available models of data storage, finding them all prone to error and redundancy. He worked on these problems and then, in 1970, published a paper with the rousing title "A Relational Model of Data for Large Shared Data Banks." In all honesty, nothing has been the same since.

A programmer named Larry Ellison read the paper and started work on software that could put Dr. Codd's theories into practice. If you've been a resident of this planet during the past 20 years, you may know that Ellison's product and company took the name Oracle and that he is now one of the richest individuals in the world. His earliest product was designed for huge mainframe systems. Responding to market demands over the years, Oracle, and many other companies that have sprung up since, have designed systems with a variety of features geared toward a variety of operating systems. Now relational databases are so common that you can get one that runs on a Palm Pilot.

To understand why Dr. Codd's theories have revolutionized the data-storage world, it's best to have an idea of what the troubles are with other means of data storage. Take the example of a simple address book - nothing too complex, just something that stores names, addresses, phone numbers, emails, and the like. If you have no persistent, running program to put this information into, the file system of whatever OS you're running becomes the natural choice for storage.

For a simple address book, a delimited text file can be created to store the information. If the first row serves as a header and commas are used as delimiters, the text file might look something like this:

Name, Addr1, Addr2, City, State, Zip, Phone, Email Jay Greenspan, 211 Some St, Apt 2, San Francisco, CA, 94107, 4155551212, jay@not.real Brad Bulger, 411 Some St, Apt 6, San Francisco, CA, 94109, 4155552222, brad@not.real John Doe, 444 Madison Ave, , New York, NY, 11234, 2125556666, nobody@mysqlphpapps.com

This isn't much to look at, but it is at least machine-readable. Using whatever language you wish, you can write a script that opens this file and then parses the information. You will probably want it in some sort of two-dimensional or associative array so that you'll have some flexibility in addressing each portion of each line of the file. Any way you look at it, there's going to be a fair amount of code to write. If you want this information to be sortable and queryable by a variety of criteria, you're going to have to write scripts that will, for instance, sort the list alphabetically by name or find all people within a certain area code. What a pain.

You might face another major problem if your data needs to be used across a network by a variety of people. Presumably more than one person is going to need to write information to this file. What happens if two people try to make changes at once? For starters, it's quite possible that one person will overwrite another's changes. To prevent this from happening, the programmer has to specify file locking if the file is in use. While this might work, it's kind of a pain in the neck for the person who gets locked out. Obviously, the larger the system gets the more unmanageable this all becomes.

What you need is something more robust than the file system - a program or daemon that stays in memory seems to be a good choice. Furthermore, you'll need a data-storage system that reduces the amount of parsing and scripting that the programmer needs to be concerned with. No need for anything too arcane here. A plain, simple table like Table 1-1 should work just fine.

Now this is pretty convenient. It's easy to look at and if a running program accesses this table it should happen pretty quickly. What else might this program do? First, it should be able to address one row at a time without affecting the others. That way, if two or more people want to insert information into this table they won't be tripping over each other. It would be even spiffier if the program provided a simple and elegant way to extract information from a table such as this. There should be a quick way to find all of the people from California that doesn't involve parsing and sorting the file. Furthermore, this wondrous program should be able to accept statements that describe what you want in a language very similar to English. That way you can just say: "Give me all rows where the contents of the state column equal CA."

Yes, this program is great, but it isn't enough. Major problems still need to be dealt with. These problems, which we'll discuss in the following pages, are the same ones that made Dr. Codd write his famous paper, and the same ones that made Larry Ellison a billionaire.

Blasted Anomalies

Dr. Codd's goal was to have a model of information that was dependable. All of the data-storage methods available to him had inherent problems. He referred to these problems as anomalies. There are three types of anomalies: update, delete, and insert.

The update anomaly

Now that you can assume that a table structure can quickly and easily handle multiple requests, you need to see what happens when the information gets more complex. Adding some more information to the previous table introduces some serious problems (Table 1-2).

Table 1-2 is meant to store information for an entire office, not just a single person. Since this company deals with other large companies, there will be times when more than one contact will be at a single office location. For example, in Table 1-2 two contacts are present at 1121 43rd St. At first this may appear to be okay; you can still get at all the information available relatively easily. The problem comes when the BigCo Company decides to up and move to another address. In that case, you'd have to update the address for BigCo in two different rows. This may not sound like such an onerous task, but consider the trouble if this table has 3,000 rows instead of 3 - or 300,000 for that matter. Someone, or some program, has to make sure the data are changed in every appropriate place.

Another concern is the potential for error. It's very possible that one of these rows could be altered while the other one remained the same. Or, if changes are keyed in one row at a time, it's likely that somebody will introduce a typo. Then you'd be left wondering if the correct address is 1121 or 1211.

The better way to handle this data is to take the company name and address and put that information in its own table. This process of separating a table out into multiple new tables is usually called decomposition. The two resulting tables will resemble Table 1-3 and Table 1-4.

Now the information pertinent to BigCo is in its own table, Companies. If you look at the next table (Table 1-4), Contacts, you'll see that we've inserted another column, company_id. This column references the company_id column of the Companies table. In Brad's row, you see that the company_id (the second column) equals 1. You can then go to the Companies table, look at the information for company_id 1, and see all the relevant address information. What's happened here is that you've created a relationship between these two tables - hence the name relational database.

You still have all the information you had in the previous setup, you've just segmented it. In this setup you can change the address for both Jay and Brad by altering only a single row. That's the kind of convenience you want to be after.

Perhaps this leaves you wondering how you get this information un-segmented. Relational databases give you the ability to merge, or join, tables. Consider the following statement, which is intended to give all the available information for Brad: "Give me all the columns from the contacts table where contact_id is equal to 1, and while you're at it throw in all the columns from the Companies table where the company_id field equals the value shown in Brad's company_id column."

In other words, in this statement, you are asking to join these two tables where the company_id fields are the same. The result of this request, or query, looks something like Table 1-5.

In the course of a couple of pages, you've learned how to solve a data-integrity problem by segmenting information and creating additional tables. But we have yet to give this problem a name.

When we learned the vocabulary associated with relational databases from a very thick and expensive book, this sort of problem was called an update anomaly. There may or may not be people using this term in the real world; if there are, we haven't met them (people in the real world call it "breach of contract" when addressing their consultants). However, we think this term is pretty apt. In Tables 1-1 and 1-2, if you were to update one row in the table, other rows containing the same information would not be affected.

The delete anomaly

Now take a look at Table 1-6, focusing on row 3.

Consider what happens if Mr. Doe is deleted from the database. This may seem like a simple change but suppose someone accessing the database wants a list of all the companies contacted over the previous year. In the current setup, when you remove row 3, you take out not only the information about John Doe, you remove information about the company as well. This problem is called a delete anomaly.

If the company information is moved to its own table, as you saw in the previous section, this delete anomaly won't be a problem. You can remove Mr. Doe and then decide independently if you want to remove the company he's associated with.

The insert anomaly

Our final area of concern is problems that will be introduced during an insert. Looking again at the Table 1-6, you can see that the purpose of this table is to store information on contacts, not companies. This becomes a drag if you want to add a company but not an individual. For the most part, you'll have to wait to have a specific contact to add to the database before you can add company information. This is a ridiculous restriction. The solution is to store contact information in one table and company information in another. By storing company information in its own table, you can add a new company there even if you (as yet) have no contacts to go with it. Ditto for contacts with no matching companies.

Normalization

Now that we've shown you some of the problems you might encounter, you need to learn the ways to find and eliminate these anomalies. This process is known as normalization. Understanding normalization is vital to working with relational databases. But to anyone who has database experience normalization is not the be-all and end-all of data design. Experience and instinct also play a part in the creation of a good database. The examples in this book will usually be normalized. However, in some cases, a denormalized structure is preferable, for performance reasons, code simplification, or so on.

One other quick caveat. The normalization process consists of several normal forms. Normal forms are standards of database regulation that promote efficiency, predictability of results, and unambiguousness.

In this chapter we cover first, second, and third normal forms. In addition to these, the normalization process can involve four other (progressively more rigorous) normal forms. (For the curious, these are called Boyce-Codd normal form, fourth normal form, fifth normal form, and Domain/Key normal form.) We know about these because we read about them in a book. In the real world, where real people actually develop database applications, these normal forms aren't discussed. If you get your data into third normal form that's about good enough - mainly because data in the third normal form meets the requirements of the first and second normal forms, by definition. Yes, a possibility exists that anomalies will exist in third normal form, but if you get this far you should be OK.

First normal form

Getting data into first normal form is fairly easy. Data need to be in a table structure and to meet the following criteria:

* Each column must have a unique name and define a single attribute of the table as a whole.

* Each row in the table must have a set of values that uniquely identifies the row (this is known as the primary key of the table).

* No two rows can be identical.

* Each cell must contain an atomic value, meaning that each cell contains only one value. No arrays or any other manner of representing more than one value can exist in any cell.

* No repeating groups of data are allowed.

The final item here is the only one that may require some explanation. Take a look at Table 1-7.

As you've already seen with these data, row 1 and row 2 contain two columns that contain identical information. This is a repeating group of data. Only when you remove these columns and place them in their own table will these data be in first normal form. The separation of tables that we did in Tables 1-3 and 1-4 will move this data into first normal form.

Before we move on to chat about second and third normal form, you're going to need a couple of quick definitions. The first is of the term primary key. The primary key is a column or set of columns by which each row can be uniquely identified.

Primary keys, while very important, are difficult to understand both in theory and in practice. The theory is straightforward: Each row in the column designated as the primary key must have a unique value.

Continues...

---

Excerpted from MySQL/PHP Database Applications by Brad Bulger Jay Greenspan David Wall Excerpted by permission.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.

---