many-to-many setup quandry

[betwalk]

Hi everybody-

Let's see if I can ask this as simply as possible.

Tables in an existing system:

tblContacts
----------------
ContactID=pk
assorted fields like fname, Lname, etc.

tblChildren
---------------
ChildID=pk
ContactID=fk
assorted fields like fname, age etc.

tblDogs (this table is new)
---------------------
DogID=pk
assorted fields like DogName, breed, etc.

tblJoin (this table is also new)
----------
JoinID=pk
DogID=fk
contactID=fk

This is being used by an organization that trains service dogs.
Orginally this database just tracked donors and their family info (yes,
there is a separate table for donations...) They've asked me to help
them expand it to include tracking information about dogs, now, as
well. The purpose of the join table is to help link the various people
who come through these dogs' lives until they are placed with a client.
(ie: breeder, donor, puppy raiser, trainer, etc.) Some people are
adults and sometimes a participant is a student trainer, coming from
the children table.

I got everything set up beautifully and working very well, until I
remembered the kids. I forgot the kids and had only set up
relationship between the dogs and the tblContacts. My heart sank!

I'm confused about how to structure the join table so that I can have a
many-to-many relationship between the dogs and ALL the possible people,
not just the adults. Can I add ChildID as a foreign key to the
tblJoin, allowing a possible many-to-many relationship between dogs and
children? But then relationship rules would constantly be broken, since
either ContactID or ChildID would have to be left empty each time a
record is entered to this table.

Is there some way for me to combine tblContacts and tblChildren and
then set up the many-to-many relationship between tblDogs and this new
dataset?

I'm kinda stumbling around here. I'd be grateful for any guidance on
this-

Thanks- Betsy

 

[Larry Linson]

You may not like the advice, but what you need to do is merge tblContacts
and tblChildren, and use data in the Table to determine (when determination
is needed, which is unlikely to be every time it is accessed) whether the
Contact is a child or an adult.

With enough work, you could probably make it work with the unnormalized
structure, but it'd be extra work over and over again in the future, too.

Larry Linson
Microsoft Access MVP

 

[betwalk]

You may not like the advice, but what you need to do is merge tblContacts
and tblChildren, and use data in the Table to determine (when determination
is needed, which is unlikely to be every time it is accessed) whether the
Contact is a child or an adult.

With enough work, you could probably make it work with the unnormalized
structure, but it'd be extra work over and over again in the future, too.

Larry Linson
Microsoft Access MVP

---------------------------------------
Hi Larry-

Thanks for this quick reply! Are you saying that I can merge this data
via a query for the purpose of the join? Or are you suggesting that
the original structure needs to be changed with parents and their
children all in one table...?

Sorry - but I'm not catching your full meaning. Can you explain a
little more?

Betsy

 

[Larry Linson]

There should be one "people" Table, with both adults and children, and some
way to distinguish between them for those cases where that is needed -- that
will be simpler if you have a date-of-birth Field.

(If, instead, you put an adult/child Field, then you let yourself in for
maintenance chores when a child reaches the age to be considered an
adult -- you have to know when that happens, and run a Query to update the
adult/child field.)

Larry Linson
Microsoft Access MVP

 

[Terry Kreft]

It might be simpler to put the adult/child state in the join table as it may
be necessary to know what the person was when they had contact with the dog,
rather than what they are now.

Possibly a DOB field in the people table and then record the person type
when the relationship is made?

So something like

tblContacts
----------------
ContactID=pk
assorted fields like fname, Lname, etc.
dob

tblDogs (this table is new)
---------------------
DogID=pk
assorted fields like DogName, breed, etc

tblJoin (this table is also new)
----------
DogID=fk
contactID=fk
contactType

 

[betwalk]

Wow, you all have given me a lot to think about!

There are fields that I have not mentioned in all the tables described.
All I outlined here were the pertinent fields for setting up the
relationships.

I need to mull this through my pea brain for a few days...! What this
challenges for me is the idea that the kids are currently set up in a
one-to-many relationship (on the many side, of course) with the
families listed in the contacts table. How do I keep this integrity if
the kids are in the same table as their parents? How would I handle
any family that has more than one kid? I'm missing the larger picture
here, I bet.

Hmmm. I need to think on this!

Thanks for all your input - I value it a LOT!

Betsy