ONLamp.com    
 Published on ONLamp.com (http://www.onlamp.com/)
 See this if you're having trouble printing code examples


aboutSQL

Good Relations

03/13/2001

Also in aboutSQL:

SQL Data Types

Working With Tables

Data Definition Language

Introducing SQL Sets

SQL Subqueries

Last week, we started working with a fundamental aspect of database structure -- database keys. This week, we'll take a look at how keys are used to construct relationships between different tables in a database. This is the core function of relational databases. You may have a rocky relationship with your database, but after this week, your data should relate just fine!

Your first relationship

There is nothing magical, complex, or inherently hard about data relationships. In a nutshell, the most simple database relationship is formed by taking the key field value of a record in one table and inserting it into an identical field in the related record in a second table. We'll work through an example from the beginning.

In this scenario, you're building a database for your music collection. You may have started with a single database table that looks something like this:

CDID Artist Title Year Quality
15 Peter Gabriel So 1984 10

You've got a single table with a primary-key value called CDID that contains information about a single CD in your collection. But if you have an extensive collection of Peter Gabriel albums, you'd end up with a lot of redundancy in your database.

CDID Artist Title Year Quality
15 Peter Gabriel So 1984 10
16 Peter Gabriel Us 1992 10

You can imagine the progression from here, especially for a large database of music like an e-commerce site. This brings us to our first SQL Wisdom in a long time:

SQL Wisdom #5: Data redundancy is generally a result of bad design.

Any time you have to re-enter data, you're likely to enter it incorrectly. In addition, redundant data takes up extra space in the database. There are cases where this is a good idea, typically for specific performance reasons; but in general, you can assume that you need to redesign your database if you see a lot of redundant data. And that's the whole essence of relational databases.

Relationships are all about reducing database redundancy. We can take the record for Peter Gabriel and break it into a separate table.

ArtistID ArtistName
22 Peter Gabriel

Now that we have the information about the artist separated from the album information, we can redesign the table of information for the CDs themselves.

CDID ArtistID Title Year Quality
15 22 So 1984 10
16 22 Us 1992 10

I've highlighted the column where the ArtistName string was replaced with a primary key from the table containing the related data record. In database parlance, this is typically called a foreign key. The two database tables are now related by the ArtistID key field.

Long-term relationships

The database relationship we created in the previous section is simple, but it is an example of the most common type of database relationship. There are three standard types of database relationships:

The example shows a one-to-many relationship -- the most common type of database relationship. The Artist table contains a single "Peter Gabriel" record which can have 0, 1, or more related records in the CDs table.

Before we move on to the details of different types of relationships, I want to point out a few features of all relationships:

There are many other details to discuss -- referential integrity, cascaded DELETEs, how NULLs figure into relationships, and a host of other topics that we'll discuss as we move forward with aboutSQL.

Next steps

Now that we've got the basics of keys and relationships, we'll spend next week discussing one-to-one and many-to-many relationships. After that, we'll move back to SQL proper and cover using the JOIN keyword to create SQL statements that use database relationships. Until then, feel free to send me your comments, questions, and feedback.

John Paul Ashenfelter is president and CTO of TransitionPoint.com, a technology development and analysis group focusing on web database applications.

Read more aboutSQL columns.


Return to ONLamp.com.

Copyright © 2009 O'Reilly Media, Inc.