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

 

[Jamie Collins]

Possibly a DOB field in the people table

The OP indicated that age is being captured. It could be that age
coupled with the date captured (or even date entered into the DB) could
be adequate (i.e. the timestamp + interval approach to modelling time
in SQL) for determining whether the person is considered a child at any
point in time during a dog's life. For simplicity, let's assume DOB is
available...

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.

I would not recommend the table name 'tblJoin'. Prefixes aside, I'd
avoid an entity/relationship name of 'join'. How about 'Roles'?

I think the OP omitted some necessary start and end date columns from
this table if they are to fulfil the primary role "help link the
various people who come through these dogs' lives until they are placed
with a client".

The implication is that a child can be a trainer but none of the other
roles mentioned. Therefore, I agree the status of adult/child should be
checked when the person is assigned a role.

It is often said in these groups that a calculation should not be
stored in a column. If DOB was available (or could be approximated
based on timestamp + age), your adult/child state would seem to be an
example of storing a calculation.

An alternative is to store the DOB in the 'roles' table with
identifier, of course, and a foreign key back to the 'people' table (or
a multi-table CHECK constraint but DRI is generally preferred). This
way CHECK constraints or Validation Rules could compare DOB with the
start and end date columns e.g. to ensure a child isn't assigned the
role 'breeder', to ensure someone isn't assigned a role before they
were born, etc.

I note your intention was to keep things simple and the lack DOB and
start and end dates for roles suggests the OP may be merely interested
in the roles, rather that when or in what order those roles were
assigned (i.e. no temporal elements). In this case, having distinct
roles for adults and children (e.g. 'trainer' and 'student trainer'
respectively) could suffice.

Jamie.

--

 

[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

 

[Jamie Collins]

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?

With a relationship table to model families, perhaps. This article may
help:

'Hollywood Couples' by Joe Celko:
http://www.intelligententerprise.com/010101/celko.jhtml

Jamie.

--