• Choose a category
• All
• Classic-Fiction

Identifying Relationships in Your Data

After you normalize the data (see the preceding section), you’ve created the entities (tables). Now, you need to investigate the relationships among these entities.

Three main types of data relationships exist (and of these, only two are common):

♦ One-to-one relationship: Each element of table A is related to exactly one element of table B. This type of relationship isn’t common because if a one-to-one relationship exists between two tables, the information can be combined safely into one table.

♦ One-to-many relationship: For each element of table A, there could be many possible elements in table B. The relationship between mission and hero is a one-to-many relationship, as each mission can have many heroes, but each hero has only one mission. (My heroes have attention issues and can’t multitask very well.) Note that hero and mission are not a one-to-many relationship, but a many-to-one. The order matters.

♦ Many-to-many relationship: This type of relationship happens when an element of A may have many values from B, and B may also have many values of A. Usually, listed fields turn out to be many-to-many relationships. In the hero data, the relationship between hero and power is a many-to-many relationship because each hero can have many powers, and each power can belong to multiple heroes.

You can use an ER tool to diagram the various relationship types. Figure 3-7 shows this addition to the hero design.

Figure 3-7: Now I’ve added relationships.

Note that MySQL Workbench doesn’t actually allow you to draw many-to-many joins. I drew that into Figure 3-7 to illustrate the point. In the next chapter, I show how to emulate many-to-many relationships with a special trick called a link table.ER diagrams use special symbols to represent different kinds of relationships. The line between tables indicates a join, or relationship, but the type of join is indicated by the markings on the ends of the lines. In general, the crow’s feet or filled-in circle indicate many, and the double lines indicate one.

ER diagrams get much more complex than the simple ones I show here, but for this introduction, the one and many symbols are enough to get you started.

Chapter 4: Putting Data Together with Joins

In This Chapter

Using SQL functions

Creating calculated fields

Working with date values

Building views

Creating inner joins and link tables

Single tables aren’t sufficient for most data. If you understand the rules of data normalization (see Chapter 3 of this minibook), you know how to break your data into a series of smaller tables. The question remains, though: How do you recombine all these broken-up tables to make something the user can actually use?

In this chapter, you discover several techniques for combining the data in your tables to create useful results.

I wrote a quick PHP script to help me with most of the figures in this chapter. Each SQL query I intend to look at is stored in a separate SQL file, and I can load up the file and look at it with the PHP code. Feel free to look over the code for showQuery on the CD-ROM. If you want to run this code yourself, be sure to change the username and password to reflect your data settings. Use queryDemo.html to see all the queries in action. I also include a script called buildHero.sql that creates a database with all the tables and views I mention in this chapter. Feel free to load that script into your database so that you can play along at home.

Calculating Virtual Fields

Part of data normalization means that you eliminate fields that can be calculated. In the hero database described in Chapter 3 of this minibook, data normalization meant that you don’t store the hero’s age, but his or her birthday instead (see Chapter 3 of this minibook). Of course, if you really want the age, you should be able to find some way to calculate it. SQL includes support for calculating results right in the query.

Begin by looking over the improved hero table