many to many problem

[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

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

 

[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.

"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)

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?

You could put it that way.

================
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>

If your middle name was Irving that'd be cool )

Thanks again Lynn.

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

 

[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!

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... (^_^)

 

[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.

 

[Frank Cutre]

Hi Tim,

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

THANX for your time... Frank

 

[Tim Ferguson]

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

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

All the best


Tim F

 

[Frank Cutre]

Hi Tim,

Best of luck

Thank you

... make sure you let us know how it works out.

Oh don't worry... I gotta feeling I'm not done with newsgroup yet... so
please keep half an eyeball on me. ))

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

 

[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.

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

 

[Tim Ferguson]

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

<prrrr prrrr>

 

[Lynn Trapp]

ROFL

--
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

 

[John Vinson]

<prrrr prrrr>

.... 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]

 

[tina]

... and I'm VERY grateful that the two of you jumped in to help. This

was Not looking like fun...

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>

 

[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).

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? ))

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

 

[Lynn Trapp]

... 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!

Well, what can I say? tina came begging (don't hit me, tina) and who was I
to turn her down? LOL

--
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

 

[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>

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

 

[Tim Ferguson]

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

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

 

[tina]

better run fast!

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

 

[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!

 

[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!

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

 

[Lynn Trapp]

Nah,
I'm a nice lecturer. ;-)

--
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