many to many problem

T

Tim Ferguson

A contact can be a single entity on its own or "belong" to any one or
combination of the following:

a single company -or-

a single division of a single company -or- multiple divisions of a
single company -or- a single division of multiple companies -or-
Multiple divisions of mutilple companies
Click to expand...

PMFJI

I have tried to read as much of this thread as I could in one sitting:
can I propose a sub-typing kind of solution like this:-


ThingsToContact
===============
*Thing
OtherCommonStuff

Companies
=========
*Thing (FK references ThingsToContact)
FullName
TypeOfBusiness

Divisions
=========
*Thing (FK references ThingsToContact)
BelongsTo (FK references Companies)
AreaCovered
DollarsPerYear
MaxCreditAllowance

Addresses
=========
*AddressID
FirstLine
SecondLine
PostCode

PhoneNumbers
============
*PhoneID
AreaCode
LocalNumber
TypeOfPhone

LocatedAt
=========
*Thing (FK references ThingsToContact)
*Address (FK references Addresses)
*TypeOfLocation (eg mailing, billing, home, etc etc)

RingingAt
=========
*Thing (FK references ThingsToContact)
*Phone (FK references PhoneNumbers
*ReasonForPhoning (appointments, creditquery, etc)

People
======
*PersonID
FirstName
LastName
Birthday
WifesBirthday

Represents
==========
*Person (FK references People)
*Thing (FK references
*TypeOfContact (manager, salesman, etc etc)


I have a feeling that some of the PKs could be overly complex. For
example, I assumed that Eric can represent ACME division in different
roles at a time: but if this is not true then the PK for Represents needs
only to have the two fields. Etc further up the list.

Something like this can model most of your rules. Some of them need to
be implemented via CHECK constraints: for example ensuring that a
Company.Thing value does not appear in as a Division.Thing and so on. You
probably need controlling tables for TypeOfLocation, ReasonForPhoning etc
etc.

This also assumes that People don't have addresses or phone numbers
themselves, but are only addressable via the actual roles they assume: if
Eric is the salesman for Acme Division and regional rep for Southern
Belles, then you'd contact him using the appropriate details for either.
When it comes to UI design, it's simple enough to create a single list of
all the contacts he is associated with.

Hope that helps...


Tim F
 
F

Frank Cutre

Hi Lynn,
If your memory works like mine, we are both in trouble <g>. I remember
Paradox requiring the Primary Key to be the first field in the table but
didn't remember an autoincrement having to be first, but maybe it did. As
for Access AutoNumber fields, you will find them to be a mixed bag of
tricks. I really don't like them but use them in tables for which it is
extremely difficult to come up with a workable natural key. They do provide
a quick and easy way to provide relationships between tables, but they can
very easily mask the existence of redundant data in a given table.
Click to expand...

"but they can very easily mask the existence of redundant data in a given
table"... hmmm... recalling our earlier conversation...
================
This is not really what I would describe as a many-to-many relationship but
as multiple one-to-many relationships. We will still need a "resolver"
table, but this will require a little more finesse than a typical
many-to-many relationship. Here's one approach you could take, although this
is probably not the only way to solve the problem. Others reading this
thread may have some better suggestions.

tblTelephoneConnections
TelephoneConnectionsID (PK)
CompanyID (FK)
DivisionID (FK)
ContactID (FK)
TelephoneID (FK)
Click to expand...

Oooooh... re-solv-er table... magic. Haven't read about that one yet.
Looks similar to a ManyToMany linking table.

The fact that it starts with a TelephoneConnectionID(PK) field, though,
leads me to believe the possibility exists for redundant record items (ties)
to occur amongst the [CompanyID (FK), DivisionID (FK), ContactID (FK),
TelephoneID (FK)] fields subset, hence the TelephoneConnectionID(PK)
"tiebreaker"... is that correct?
Click to expand...

You could put it that way.
Click to expand...
================
Although I used the "TelephoneConnectionID(PK)" field when I pressed you ref
"the possibility exists for redundant record items"... I was really
eyeballing that TelephoneID field in the tblTelephoneConnections table and
saying to myself... Lynn's suggesting that I PK the tblPhones table which
(in my mind) opens the door for redundancy.

Now that I know we're both on the same wavelength regarding PKs and how they
can (all together now) "very easily mask the existence of redundant data in
a given table"... I gotta press you harder ref "although this is probably
not the only way to solve the problem. Others reading this thread may have
some better suggestions"... Just so you know I haven't been in the reclining
position (of my lazy boy as I write you) here's what I have to offer
(oversimplified for brevity):
---------------------------------
given

userForm (form collects and/or displays phone info)

and

tblPhones
AreaCode (PK)
Number (PK)
TelephoneID (autoNumber) <-- now that I know it doesn't have to be 1st

where

userForm VBAcode queries user input against tblPhones BEFORE commitiing post
to tblPhones
if userForm(user input) exists in tblPhones
.....then RETURN TelephoneID to userForm to trigger the appropriate display
fields QUIT
POST user input to tblPhones and RETURN TelephoneID to userForm to trigger
the appropriate display fields
---------------------------------
I haven't a clue on performance and/or maintenance for this approach,
couldn't tell ya if it's overkill either. Normally I try to limit writing
code to security, display hardware config, and report generating issues
(what the heck did you buy that new fangled program for in the first
place?). One downside (of this approach) for sure is... now I gotta wait
til I learn some VBA before I get to play with the tables, forms and reports
(whimper).

OK "Lynn-man"... you got me on experience, you got me on connections... so
give it up... what are my options (to best use relationships to ensure a
nice tight tblPhones)... pretty please? :)))

No worries. It happens to me all the time. At least my parents didn't do me
like they did my older brother. His initials are R.A.T. <g>
Click to expand...
If your middle name was Irving that'd be cool :))

Thanks again Lynn.

THANX for your time... (^_^)
 
F

Frank Cutre

Hiya Tina,
looks like Lynn has gotten you to a place where you feel confident in moving
on, Frank. and thank heaven he stepped in to help! because i've yet to come
up with anything that looks workable and supports those complex real-world
relationships of yours. <wry grin>
good luck as you continue developing! :)
Click to expand...
BOTH of you did. You're a "PK" in my microsoft.public.access.tablesdbdesign
table! :)))

Thanks again for holding my hand.

THANX for your time... (^_^)
 
T

tina

you're welcome Frank, but HEY....*did you note Tim Ferguson's post of
today??*

it's further up the thread. may find some help there too - i haven't built
it (i usually have to see something in "brick and mortar" before i can
understand it well <g>), but it looks a very interesting approach.
 
T

Tim Ferguson

Your approach is similar to what I'm honing in on. Thank you for your
added perspectives.
Click to expand...

Best of luck... make sure you let us know how it works out.

All the best


Tim F
 
L

Lynn Trapp

you're welcome Frank, but HEY....*did you note Tim Ferguson's post of
today??*

it's further up the thread. may find some help there too - i haven't built
it (i usually have to see something in "brick and mortar" before i can
understand it well <g>), but it looks a very interesting approach.
Click to expand...

Tim always has some great ideas to add. I think his approach is very
workable.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html
 
J

John Vinson

<prrrr prrrr>
Click to expand...

.... and I'm VERY grateful that the two of you jumped in to help. This
was Not looking like fun... and you two and Tina saved me a lot of
skull sweat!

John W. Vinson[MVP]
 
T

tina

... and I'm VERY grateful that the two of you jumped in to help. This
was Not looking like fun...
Click to expand...

ditto that, in spades! - i'd got in over my head and was going down for the
third time. <g> Lynn and Tim get medals for saving Frank and me both!
<bows low, coughing out water>
 
L

Lynn Trapp

I haven't a clue on performance and/or maintenance for this approach,
couldn't tell ya if it's overkill either. Normally I try to limit writing
code to security, display hardware config, and report generating issues
(what the heck did you buy that new fangled program for in the first
place?). One downside (of this approach) for sure is... now I gotta wait
til I learn some VBA before I get to play with the tables, forms and
reports
(whimper).
Click to expand...

No, No, NO. Get your tables and relationships right first before you even
begin to think about how to code anything in VBA.
OK "Lynn-man"... you got me on experience, you got me on connections... so
give it up... what are my options (to best use relationships to ensure a
nice tight tblPhones)... pretty please? :)))
Click to expand...

I'm afraid you lost me here, my friend.


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html
 
L

Lynn Trapp

L

Lynn Trapp

ditto that, in spades! - i'd got in over my head and was going down for
the
third time. <g> Lynn and Tim get medals for saving Frank and me both!
<bows low, coughing out water>
Click to expand...

There you go being hard on yourself again. Am I gonna have to give you "the
lecture" again? <g,d,&r>

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html
 
T

Tim Ferguson

so
give it up... what are my options (to best use relationships to ensure a
nice tight tblPhones)...
Click to expand...

Sorry to interrupt again -- did you understand how the subtyping suggesting
I made upthread worked? I do appreciate that the description was a little
on the terse side (not everyone has broadband <g>) so if you need it spread
out a bit let me know.

All the best


Tim F
 
T

tina

hey, i'm never too proud to admit to needing help. knowing when to ask for
help - and asking - is a strength, not a weakness. i just wish i'd had
access to such a great resource (and wonderful folks) when i was a newbie. i
learn plenty now, on an ongoing basis, but i *really* could've cleaned up
back then! :)
 
L

Lynn Trapp

hey, i'm never too proud to admit to needing help. knowing when to ask for
help - and asking - is a strength, not a weakness. i just wish i'd had
access to such a great resource (and wonderful folks) when i was a newbie.
i
learn plenty now, on an ongoing basis, but i *really* could've cleaned up
back then! :)
Click to expand...

The person who is too proud to ask for help from time to time is doomed to
fail. I think you have "cleaned up" quite nicely :)

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html
 
T

Tim Ferguson

The person who is too proud to ask for help from time to time is
doomed to fail. I think you have "cleaned up" quite nicely :)
Click to expand...

Sign in one of our seminar rooms:

You can ask a question and look foolish for a minute;
or you can stay silent and remain a fool forever.

B Wishes


Tim F
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Stumped by "one to many" in query, need some help 4
Assistance with Many-to-Many Relationships 6
A many-to-many challenge 1
Change specific records' values 1
Designing a patient management 4
Need count of divisions and number of employees within division 2
combo box issue 22
Multiple Many-to-Many relationships 10

Top