One of the huge advantages of a relational database is that, once you have your data held in clearly defined, compact tables, you can connect or relate the data held in different tables. There are three types of relationships between the data you are likely to encounter at this stage in the design: one-to-one, one-to-many, and many-to-many. To be able to identify these relationships, you need to examine the data and have an understanding of what business rules apply to the data and tables. If you're not sure, it can be helpful to meet with someone who does have a thorough knowledge of the data.
When analyzing table relationships, you need to look at the relationship from both sides (sounds a little like marriage counseling, doesn't it?). When creating table relationships you always work with two tables at a time. One table is called the primary or parent table and the other is the related or child table.
A one-to-one (1:1) relationship means that each record in Table A relates to one, and only one, record in Table B, and each record in Table B relates to one, and only one, record in Table A. Look at the following example of tables from a company's Employees database:
EmployeeID | FirstName | LastName | Address | City | State | Zip |
---|---|---|---|---|---|---|
EN1-10 | Carol | Schaaf | 2306 Palisade Ave. | Union City | NJ | 07087 |
EN1-12 | Gayle | Murray | 1855 Broadway | New York | NY | 12390 |
EN1-15 | Steve | Baranco | 742 Forrest St. | Kearny | NJ | 07032 |
EN1-16 | Kristine | Racich | 416 Bloomfield St. | Hoboken | NJ | 07030 |
EN1-19 | Barbara | Zumbo | 24 Central Ave. | Ritchfield Park | NJ | 07660 |
EN1-20 | Daniel | Gordon | 2 Angelique St. | Weehawken | NJ | 07087 |
EN1-22 | Jacqueline | Rivet | 3600 Bergeline Ave. | Union City | NJ | 07087 |
EN1-23 | Betsy | Rosyln | 1800 Boulevard East | Weehawken | NJ | 07086 |
EN1-25 | Will | Strick | 2100 91st St. | North Bergen | NJ | 07047 |
EN1-26 | Susan | Shipe | 240 Fifth Ave. | New York | NY | 10018 |
EmployeeID | PayRate |
---|---|
EN1-10 | $25.00 |
EN1-12 | $27.50 |
EN1-15 | $20.00 |
EN1-16 | $19.00 |
EN1-19 | $22.75 |
EN1-20 | $23.00 |
EN1-22 | $22.50 |
EN1-23 | $19.50 |
EN1-25 | $12.50 |
EN1-26 | $14.00 |
Above, tables with a one-to-one relationship from a database of information about employees
Each record in the Personal table is about one employee. That record relates to one, and only one, record in the Payroll table. Each record in the Payroll table relates to one, and only one, record in the Personal table. (This is what looking at it from both directions means.)
In a one-to-one relationship, either table can be considered to be the primary or parent table.
A one-to-many (1:N) relationship means a record in Table A can relate to zero, one, or many records in Table B. Many records in Table B can relate to one record in Table A. The potential relationship is what's important; for a single record in Table A, there might be no related records in Table B, or there might be only one related record, but there could be many. Look at the following tables about a company's Customers and Orders.
CustomerID | CustomerName | Address | City | State | Zip |
---|---|---|---|---|---|
20151 | Engel's Books | 19 International Dr | Ryebrook | NY | 10273-9764 |
20493 | Jamison Books | 396 Apache Ave | Fountain Valley | CA | 92708-4982 |
20512 | Gardening Galore | 79 Gessner Pk | Houston | TX | 77024-6261 |
20688 | Books Abound | 51 Ulster St | Denver | CO | 80237-3386 |
20784 | Book World | 687 Mountain Rd | Stowe | VT | 08276-3196 |
20926 | The Corner Booksotre | 36 N.Miller Ave | Syracuse | NY | 13206-4976 |
20932 | Allendale Books | 512 Columbia Rd | Someville | NJ | 08876-2987 |
21570 | In Between the Covers | 2008 Delta Ave | Cincinnati | OH | 45208-4468 |
21587 | Books and Beyond | 51 Windsor St | Cambridge | MA | 02139-2123 |
21965 | Cover to Cover | 12 Harbor St | Burlington | VT | 04982-2977 |
OrderNum | CustomerID | OrderDate | ShipDate | Shipper |
---|---|---|---|---|
76654 | 20151 | 2/1/00 | 2/6/00 | USPS |
74432 | 20151 | 6/30/99 | 7/2/99 | Federal Express |
75987 | 20151 | 11/10/99 | 11/12/99 | UPS |
62922 | 20493 | 9/5/99 | 9/6/99 | UPS |
65745 | 20493 | 10/1/99 | 10/3/99 | USPS |
72212 | 20493 | 4/22/00 | 4/25/00 | UPS |
73547 | 20493 | 8/17/99 | 8/20/99 | UPS |
69211 | 21570 | 5/12/99 | 5/12/99 | Federal Express |
70343 | 21587 | 10/2/00 | 10/4/00 | UPS |
72833 | 21587 | 12/14/99 | 12/17/99 | UPS |
Above, tables with data about customers and orders that have a one-to-many relationship
The Customers table holds a unique record for each customer. Each customer can (and, we hope, does) place many orders. Many records in the Orders table can relate to only one record in the Customers table. This is a one-to-many relationship (1:N) between the Customers table and the Orders table.
In a one-to-many relationship, the table on the one side of the relationship is the primary table and the table on the many side is the related table.
A one-to-many relationship is the most common relationship found between tables in a relational database. The following is an example of a form used in a database to display the data from two tables with a one-to-many relationship.
A form displaying data from tables with a
one-to-many relationship
Examine the sample data below. These tables hold data about employees and the projects to which they are assigned. Each project can involve more than one employee and each employee can be working on more than one project (the "do more with less" thing). This constitutes a many-to-many (N:N) relationship.
EmployeeID | Last Name | First Name | ProjectNum |
---|---|---|---|
EN1-26 | O'Brien | Sean | 30-452-T3 |
EN1-26 | O'Brien | Sean | 30-457-T3 |
EN1-26 | O'Brien | Sean | 31-124-T3 |
EN1-33 | Guya | Amy | 30-452-T3 |
EN1-33 | Guya | Amy | 30-482-TC |
EN1-33 | Guya | Amy | 31-124-T3 |
EN1-35 | Baranco | Steven | 30-452-T3 |
EN1-35 | Baranco | Steven | 31-238-TC |
EN1-36 | Roslyn | Elizabeth | 35-152-TC |
EN1-38 | Schaaf | Carol | 36-272-TC |
EN1-40 | Wing | Alexandra | 31-238-TC |
EN1-40 | Wing | Alexandra | 31-241-TC |
ProjectNum | ProjectTitle | EmployeeID |
---|---|---|
30-452-T3 | Woodworking Around The House | EN1-26 |
30-452-T3 | Woodworking Around The House | EN1-33 |
30-452-T3 | Woodworking Around The House | EN1-35 |
30-457-T3 | Basic Home Electronics | EN1-26 |
30-482-TC | The Complete American Auto Repair Guide | EN1-33 |
31-124-T3 | The Sport Of Hang Gliding | EN1-26 |
31-124-T3 | The Sport Of Hang Gliding | EN1-33 |
31-238-TC | The Complete Baseball Reference | EN1-35 |
31-238-TC | The Complete Baseball Reference | EN1-35 |
31-241-TC | Improving Your Tennis Game | EN1-40 |
35-152-TC | Managing Your Personal Finances | EN1-36 |
36-272-TC | Using Electronic Mail Effectively | EN1-38 |
Above, tables with a many-to-many relationship
Most RDBMSs do not support many-to-many relationships.
You'll see how to manage this type of database relationship in the Many-To-Many Relationships article.