Designer
for Microsoft Access
Create complex MS Access databases without being an expert in relational
database design! Designer for Microsoft Access asks you plain-language
questions about what you want to manage with your database, and creates
the tables and relationships automatically. Free trial available
As mentioned earlier (See article on Identifying Table Relationships), RDBMSs do not support many-to-many relationships between tables. This is the example we had of this type of 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
The usual solution is to break this relationship down into two one-to-many relationships by creating an intersection or junction table. This table would hold the primary key field from each of the tables in the many-to-many relationship. In the new table, those fields together would be a multi-field primary key resulting in the following relationships and diagram.
An intersection table and two one-to-many relationships
This forms two one-to-many relationships; each employee can work on many projects and many employees can work on a single project. Just as you might have to make a connection in Chicago to fly from Boston to Seattle, you sometimes need an intermediate or junction table to get where you need to be.
Take a look at the following article that includes an example of Handling Many-To-Many Relationships in Microsoft Access