Good Relations
03/13/2001Last 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.
|
Also in aboutSQL: |
| 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:
- One-to-one relationships link a single record in one table to 0 or 1 records in a related table.
- One-to-many relationships link a single record in one table to 0, 1, or more records in a related table.
- Many-to-many relationships link multiple records in one table to 0, 1, or more records in another table through an intermediate linking table.
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:
- Any fixed-size field can be used as a key to join fields, but try to use the smallest integer types or a short character string.
- Numbers are always better than text.
- Any single field can be used as a key in n other tables.
- Fields that are used for relationships should typically be indexed for performance.
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.
