More assistance with Many to Many Relationships

[Lynn]

Thanks for your assistance Tina. I don't think I
explained myself enough. With what I have read about
many to many relationships, it stated that you had to set
up a combined primary key with the primary keys from the
two tables.

My problem is that each job may not have a Quote and each
Quote may not lead to a job. I initially was going to
set it up this way but it will not work for what we
need. I need to be able to track all Quotes and there
may be many different Quotes pertaining to one job or a
Job may not happern.

I have Customers that may have Quotes and Jobs and I may
have many quotes for one customer and the same with the
jobs. I have tried to think of a way to link the quotes
and jobs but it does not work.

Then to complicate the database even more I have Extra
Work information that needs to tie into the Jobs. This
should only be a one to many relationship.

Thank you for your assistance and I hope this information
better describes my problem.

-----Original Message-----
tblCustomers
CustID (primary key)
other fields about the customer

tblCustQuotes (one-to-many relationship with tblCustomers)
QuoteID (primary key)
CustID (foreign key from tblCustomers)
other fields about the quote as a whole

tblQuoteDetails (one-to-many relationship with tblQuotes)
QDetailID (primary key)
QuoteID (foreign key from tblCustQuotes)
other fields about the line item of the quote

tblJobs (one-to-one relationship with tblQuotes)
JobID (foreign key from tblQuotes is the primary key here)
other fields about the job as a whole

tblJobDetails (one-to-many relationship with tblJobs)
JDetailID (primary key)
JobID (foreign key from tblJobs)
other fields about the line item of the job

the above assumes that each quote belongs to one customer, and that each job
stems from one specific quote that the customer accepted, which is why
tblJobs is linked to tblQuotes and not directly to tblCustomers. in a
one-to-one relationship, typically both tables use the same primary key.

from your description, i don't see a many-to-many relationship at all. but
maybe i am misunderstanding your concept.

also, i get the impression you don't have a firm grasp on the meanings of
one-to-many and many-to-many. here's how they work:

one-to-many
one record in tblA may link to many records in tblB, *but* each record in
tblB only links to one record in tblA.
Orders and OrderDetails is a classic example of one-to- many.

many-to-many
one record in tblA may link to many records in tblB, *and* one record in
tblB may link to many records in tblA.
Orders and Products is a classic example of many-to- many. to express this
relationship, you need a linking table -

tblProductOrders (or OrderDetails).

also, here's one-to-one:
one record in tblA links to only one record in tblB, *and* one record in
tblB links to only one record in tblA.

remember that to determine the type of relationship, you have to consider
the link from A to B, *and also* consider the link from B to A. always look
at "both sides" of the link.

hth





.

..

 

[Rebecca Riordan]

Lynn,

Can a quote lead to multiple jobs? You say there may be multiple quotes for
a job, but can it lead the other way? If not, all you need is to have an
(optional) job number in the quote file.

HTH

--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...

 

[Lynn]

Rebecca:

The quote could definitely lead to multiple jobs. I need
to link the Customers to both Quotes and Jobs. I am not
sure I clearly understand how that will work for both
Quotes and Jobs. How do you define Parent and Child
relationships in my situation or should I not go there.

Thank you in advance for your assistance.

 

[Rebecca Riordan]

Okay, take a deep breath, this is going to get ugly <g>

Okay, so you have a 1-to-many between Customers and Quotes, a 1-to-many
between Customers and Jobs, and a many-to-many between Quotes and Jobs, is
that correct? If so, then you need to include the PK of the Customer table
(typically CustomerID) in both the Orders and Jobs tables, that will allow
you to track that link either way. Then you need what's called a "junction"
or "resolver" table between the Orders and Jobs tables to resolve the
many-to-many between them. That table only needs the PKs of the Orders and
Jobs tables.

Now, the way this works: If Mr. Smith has two quotes, Q1 and Q2, and Q1
doesn't lead to a Job, but Q2 leads to three jobs, J1, J2 and J3, then your
tables would look like:

Customers
- Mr. Smith, ID: 01

Quotes
- Q1, CustomerID: 01
- Q2, CustomerID: 01

JobQuotes
- Q2, J1
- Q2, J2
- Q2, J3

Quotes
- J1, CustomerID: 01
- J2, CustomerID: 01
- J3, CustomerID: 01

Once that's set up, you can find all the quotes for a customer, all the jobs
for a customer, all the quotes for a job, and all the jobs for a quote using
straightforward queries.

HTH

--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...