Types of Relationships
03/20/2001|
Also in aboutSQL: |
Last column, we took a look at creating relationships between different tables in databases as part of our preparation for learning how to use SQL JOINs. This week, we dig deeper into each of the three types of relationships I introduced last week.
One-to-many
The one-to-many relationship is the workhorse of relational databases as well as being the easiest relationship to understand. Let's say you need to build a shopping cart application for an e-commerce site. Your first draft of the database has columns for Item1, Item2, and Item3 with the corresponding Quantity1, Quantity2, and Quantity3 fields.
| OrderNum | ShippingInfo | Item1 | Quantity1 | Item2 | Quantity2 | Item3 | Quantity3 |
Of course, this immediately starts to break down with more than three orders! Any time you find yourself designing a database and adding similar fields like this to the same table, you need to break the table into two (or more!) related tables using a one-to-many relationship.
A one-to-many relationship allows records in Table 1 to be connected to an arbitrary number of records in Table 2 without the limitations imposed by resorting to redundant or limited numbers of fields in a single table. This reduces the size of the database and greatly increases the flexibility and performance of queries operating on that data. We can take our shopping cart example and break it into an Order table and an Item table quite simply.
Order Table
| OrderID | ShippingInfo |
OrderItem Table
| OrderItemID | OrderID | Item | Quantity |
The two tables are linked together using the OrderID field. The contents of any order in the Order table can easily be found by finding all the items with that value in the OrderID field. There is also the added advantage that the two pieces of data are independent and can easily be modified. If we now want to add an ItemNumber to the OrderItem table, we add a single column; in our original monolithic data table, we'd be adding ItemNumber1, ItemNumber2, etc.
One-to-one
One-to-one table relationships are a little more interesting and more underused than either of the other two types of relationships. The key indicator of a possible need for a one-to-one relationship is a table that contains fields that are only used for a certain subset of the records in that table.
Let's take a look at building a Catalog table for the items that your store sells. Odds are that you need to store some information about the individual items like catalog numbers, weight, and other common data. But if you're selling different kinds of items, books and CDs for example, you may want some item-specific information in the database. For example, you may want a page count, author, publish date, and ISBN for books, while you want playing time, number of tracks, artist, and label for the CDs. You could come up with some way to fit both sets of data into the same structure, but then when management decides you're also selling pet supplies, your system will probably break!
A better solution would be a one-to-one relationship between the Item table and another table of item-specific data for each type of item. The resulting structure is essentially one "master" table (CatalogItems) with one or more "subtables" (CDs and Books in this example). You link the two subtables to the master table through the primary key of the master table.
Catalog Table
| CatalogID | Price | Description | QuantityOnHand |
CDs
| CatalogID | PlayingTime | NumOfTracks | Artist | Label |
Books
| CatalogID | PageCount | Author | PublishDate | ISBN |
It may take a few minutes for this design to sink in. As a comparison, here's what the proposed database table would look like as a single monolithic table.
The one-to-one relationship has saved us from doubling the number of fields in the Catalog table and, more importantly, helped us break the database into more discrete entities. In this scenario, we can get all the general information about an item from the Catalog table and can use the primary key of that table to pull up the appropriate information from the subtable.
Pages: 1, 2 |
