One-to-many in wizzard

C

Carole Drake

I am very new - I notice that in the wizzard there is no many-to-many; is it
best to break down components until there are no many-to-many? Is
many-to-many possible? I have sales people who sell many products and
products that can be sold by many. Would I set up a table for every
salesperson, every product category and then let the query answer it?
Carole
 
D

Douglas J Steele

I'm actually unaware of any physical implementation of relational databases
that supports many-to-many relationships. What's always done is an
"intersection" or "resolution" entity is inserted between the two entities
that are related many-to-many, so that rather than A being related to B as
many-to-many, you have A related to zAB (the intersection entity) as
one-to-many, and B related to zAB as one-to-many.

Take a look in the Northwind sample database that comes with Access. The
three tables Products, Orders and Order Details is an example of this: in
actual fact, there's a many-to-many relationship between Product and Orders
(one product can appear on many orders, one order can contain many
products), and Order Details is the resolution between those two tables.
 
K

Ken Sheridan

You are most of the way to answering your own question, I think.
Many-to-many relationships exist conceptually between entities, Products and
SalesPeople in your case, but are not created directly between the two
tables. As you've correctly identified, they should be resolved to several
one-to-many relationships.

The many-to-many relationship is modelled by another table which has two
columns, e.g. ProductID and SalesPersonID which are foreign keys referencing
the primary keys of the other two tables. This table might have other
columns too, e.g. Quantity to record the number of each product sold by each
salesperson.

Often the table which models the many-to-many relationship between entities
might itself represent another entity. In your case it might for instance be
an Orders table if its an Order which represents the context in which the
relationship between Products and Salespeople exits. The Orders table would
have other columns representing attributes of the order such as CustomerID,
OrderDate etc.

That, however, would be an over-simple solution in most scenarios as it
would assume each order is for one product only. A more common model would
be to have SalesPersonID, CustomerID, OrderDate etc in an Orders table and to
have an OrderDetails table modelling the many-to-many relationship between
Orders and Products (with columns OrderID and ProductID, plus columns for
UnitPrice, Quantity etc). In this extended model the Salesperson would map
to the produces via the Orders and OrderDetails table, so the database still
records what products are sold by each salesperson.

To see this sort of model have a look at the sample Northwind database which
comes with Access. The Orders table there includes an EmployeeID column
analogous to your Salesperson. It also shows how forms with subforms are
used to input the related data.
 
C

Carole Drake

Thanks.

Ken Sheridan said:
You are most of the way to answering your own question, I think.
Many-to-many relationships exist conceptually between entities, Products and
SalesPeople in your case, but are not created directly between the two
tables. As you've correctly identified, they should be resolved to several
one-to-many relationships.

The many-to-many relationship is modelled by another table which has two
columns, e.g. ProductID and SalesPersonID which are foreign keys referencing
the primary keys of the other two tables. This table might have other
columns too, e.g. Quantity to record the number of each product sold by each
salesperson.

Often the table which models the many-to-many relationship between entities
might itself represent another entity. In your case it might for instance be
an Orders table if its an Order which represents the context in which the
relationship between Products and Salespeople exits. The Orders table would
have other columns representing attributes of the order such as CustomerID,
OrderDate etc.

That, however, would be an over-simple solution in most scenarios as it
would assume each order is for one product only. A more common model would
be to have SalesPersonID, CustomerID, OrderDate etc in an Orders table and to
have an OrderDetails table modelling the many-to-many relationship between
Orders and Products (with columns OrderID and ProductID, plus columns for
UnitPrice, Quantity etc). In this extended model the Salesperson would map
to the produces via the Orders and OrderDetails table, so the database still
records what products are sold by each salesperson.

To see this sort of model have a look at the sample Northwind database which
comes with Access. The Orders table there includes an EmployeeID column
analogous to your Salesperson. It also shows how forms with subforms are
used to input the related data.
Click to expand...
 
Top