Many-to-Many Relationship

[oftenconfused]

I know how to construct a many-to-many relationship, I'm just not sure when
one is required. Sometimes it's obvious -- for example, teachers can have
many students, and students can have many teachers. But, I find that in many
cases it's not so obvious (at least for me!) -- for examples, a company can
operate in many cities, a city can have many companies that do business
there. Do I need a many-to-many relationship in the later example? I'm
hoping someone can give me a common sense rule for determining when a
many-to-many relationship is required (preferably, with several examples).

Thanks!

Andre

 

[Douglas J. Steele]

Many-to-many relations are extremely common.

Sometimes, though, it depends on what you're going to do with the data
whether you model it as a many-to-many. For example, in your
companies/cities example, do you see a reason to have a Cities table? Would
you be storing any information about each City in that table? You could just
as easily model it as Companies and CompanyLocations, a one-to-many
relationship. You could still even use a combo box to provide a list of all
cities that you currently know about, but base that combo box on SELECT
DISTINCT City FROM CompanyLocations, rather than on SELECT City FROM Cities.
(When you think about it, though, the CompanyLocations table would be the
resolution of the the many-to-many relation between companies and cities)

Other common examples of many-to-many relationships are

Invoices/Products: one invoice can contain many products, one product can
appear on many invoices
Suppliers/Products: one supplier can produce many products, one product can
be sources from many suppliers
People/Vehicles: one person can own many vehicles, one vehicle can be owned
by many people (especially if you look at the relationship over time)
LibraryPatrons/Books: one patron can borrow many books, one book can be
borrowed by many patrons (again, especially over time)
Employees/Projects: one employee can work on many projects, one project can
have many employees working on it
Employees/Offices: one employee can have many office locations (don't forget
time!), one office can be used by many employees

That enough to get you going? For more, see some of the resource Jeff Conrad
lists at
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

 

[oftenconfused]

Doug,

I like your suggestion, but I'm not sure it's going to work. Here's what
I'm trying to do. I have a table called BRANCH (data on each corporate
branch) and another table called DIVISION (data on each corporate division).
(The two tables contain sufficiently different data that it's not possible to
create one table with a "toggle" field for identifying whether the record
pertains to a branch or a division...and then there's the problem that
multiple branches roll up into a single division.) Each branch and division
has a physical presence in one or more cities. I'm not sure how to build
this detail into the database. I thought tables were required because of the
one-to-many (one branch can have a presence in multiple cities) and because
BRANCH and DIVISION draw from the same list of cities.

Thanks!

Andrew

 

[Douglas J. Steele]

As I tried to explain, you certainly can have a Cities table, but your other
option is to create a query that lists all of the cities you already know
about, and use that for a combo box. If you're concerned about typos in data
entry, set the LimitToList flag to Yes, and put code in the combo box's
NotInList event to ensure that they really meant to type a city that wasn't
in the list.

The RowSource for the combo box can be a Union query of the two tables:

SELECT City FROM Branch
UNION
SELECT City FROM Division